World: r4wp

Got an answer from the provider. connection not from the website 
are blocked for safetyreasons. Only reachable by scripts on the website. 
The "admin" user for adjusting tables etc is not the admin user for 
granting access etc to users.

Arnold, thanks for the update. At least you know now what the issue 
is.

This is not possible due to safety reasons
 "That is not the answer I wanted to hear" --no reaction :)

I am now going to experiment with interacting with a script on the 
website that can do what I want remotely. Talking about safety risks 
:)

if you are looking for a fultext/searchengine don't miss elasticsearch. 
scales well, based on lucene, very fast, simple to use.

the interesting part is the compact rest api the storage model and 
the number of different analysers, tokenizer (snowball, ngram, etc). 

you can use elasticsearch also as a standalone nosql database with 
rebol. speaks completly json. 

working currently with 1.2 m documents + datastorage on a single 
machine.  runs very smooth. 

evaluated other components before (sphinx, xapian, tokyo etc.) elasticsearch 
has it all and is much cleaner to handle than solr.

Thanks for the analysis Tomas.

About RebDB:
Is there a way to update multiple fields at the same time ?
I read in the documentation:

db-update/where my-table id 0 [id < 6]

This sets only one filed.

In rebdb.r file, db-update function takes columns parameter as word! 
or block! and says "Columns to set".
So I think it supports updating multiple columns at once.

db-update/where my-table [id colA colB] [0 "a" "b"] [id < 6] ;works

I have also read the 2.0.4 version supports JOINs but there is no 
example how to use it with db-* style commands

Do you know the way ?

I whish to use ODBC to manipulate ACCESS 2007 databases.

Where I could find some documentation about REBOL and ODBC and ODBC 
itself ?

Well, I didn't use it much, but I this works:

;create table tbl [a b c]
>> db-insert tbl [1 2 3]
>> db-select/joins [a b c] tbl [select [a c] from tbl] b
== [1 2 3 1 3]

Thanks Endo

Your are very helpful to me

for ODBC:
http://www.rebol.com/docs/database.html
http://www.connectionstrings.com

Yes, reading about REBOL. I was to quicky.

Well, the whole community was very helpful to me, now I'm trying 
be.

I have read in the REBOL3 world about incompatibility with ODBC in 
the latest icarnations. Do you know anything about this ?

I still use R2, didn't use R3 yet. Just a few tests etc.

As I know, there is no direct ODBC support in R3, but there is an 
ODBC extension which is stable if I'm not mistaken.

I meant "in the latest icarnations of ODBC"

BrianH and Kaj can answer more correctly about R3 && ODBC.

R2 and ODBC

R2 & ODBC is quite stable & useful.

There are just a few small problems which can be solved some work 
arounds.

such as, R2 doesn't support new SQL Server datatypes. You should 
CAST & CONVERT your values in your SQL to be able to get those values 
into R2 app.

I don't know about the datatype support when connecting to Access 
2007 databases; what Endo said was related to SQL Server. However, 
I have Access 2010 here and 2007 at home so I can run some tests 
later today if you like.

R2 is more ambitious about datatype conversion than the R3 extension, 
which only handles SQL datatypes that are directly compatible with 
64bit or smaller R3 datatypes. However, R2 doesn't handle floating-point 
datatypes, and R3 does.

I've used Access 2007 with R2, I didn't see a problem. Not sure about 
the supported datatype. I usually use old/standard/simple SQLs.

Weirdly enough, the datatypes that R2 doesn't support in newer SQL 
Server versions are the ones that were added to make SQL Server *more* 
standard. It supports the old, non-standard ones just fine.

I get tripped up by missing datatype support with the R3 extension 
a lot more than with R2. Converting from numeric to float is one 
thing - iffy, but at least you can do it; converting from varchar(max) 
or text to varchar is often impossible without data loss.

Had to do more than one project with scripts that call themselves 
in R2 to do data extraction from text fields, then call themselves 
in R3 to do processing.

Good thing the R3 extension is open-source. Given time to work on 
it, we can improve its datatype support :)

After some time, I gave a try to the Sqlite DB. It kind of improved 
in some areas, even locking, so it is even more useable in concurrent 
environment. However - it still uses per file lock, and although 
it uses just milliseconds to lock the file, I have a problem with 
one aproach I am trying to take, and hence maybe the Sqlite is not 
right DB for my purpose, or my aproach is not correct. The reason 
why I wanted to use sqlite DB is, that it is easily movable to other 
target, and its performance is fine for even semi heavy solutions. 
I don't want to be dependant upon the some kind of "server", which 
stores its files who knows where, and to which I don't have proper 
access in cgi environment, unless I am a DB admin or server admin 
...

Simply put - millisecond lock time is enough fine grained for my 
purposes, but - let's assume you have several ppl working on some 
customer list, where each customer has some orders. Those ppl do 
select particular customer, and work with orders. What I want is 
- when some user selects particular customer, I need its record being 
locked. The trouble is, that when I use BEGIN transaction for sqlite, 
it locks all the file, and does not allow other process to do write 
to the DB.


I wonder, if I can somehow obey it, not really having per record 
locking. I would have to implement my own lock mechanism (not locking 
in fact), not allowing others select/enter customer record, when 
some other person is working on it?

Or simply I have to use more powerfull SQL solution, but heck, I 
don't like all the heave SQL servers for some simple few tables solution 
....

It's interesting when I try to connect using a port scheme I cannot 
get error msg:

>> con: open [scheme: 'odbc target: "..."] ;try with wrong password 
for example
** Script Error: ODBC error:

>> con: open odbc://sa:[qwe1234-:-mydsn]

** Script Error: ODBC error: [Microsoft][SQL Server Native Client 
10.0][SQL Server]Login failed for user 'sa'.  <<< now I got error 
msg.

Any idea how to get "real" error message?

Shouldn't that rather be HOST instead of TARGET?
>> con: [scheme: 'odbc host: "mydsn"]

Only if the target is a DSN with no other settings specified. Otherwise, 
use target.

con: [scheme: 'odbc target: "dsn=mydsn"]

For instance, this is a common connection I use at work (server name 
changed):

conn: [scheme: 'odbc target: "driver=SQL Server Native Client 10.0;server=SERVER1;database=db1;Trusted_Connection=Yes;"]

I've just updated the R3 ODBC Extension to work with more SQL types 
and, with the support of BrianH, fixed and improved the boot code 
OPEN and COPY actors.


The extension is available over at Github in both source form as 
well as a DLL compiled for windows, see https://github.com/gurzgri/r3-odbc

dll compiled means use from R2?

It's an R3 extension. You could load it in R2, but you'd have to 
program the interface to the extension yourself

Is anyone using Doc's MySql protocol (v4.1 1.3.0) with MySql 5.5.24-cll, 
protocol v10? 


I have a client migrating to a new host. We've been using 1.2.1 against 
mysql 5.0.95, but 5.5 triggers the client auth error. Moving to Doc's 
4.1 version solved that, but a simple test query returns an empty 
block. 


Can't rule out a data migration error yet, as things just moved and 
getting connected was the frist step. Just curious if someone is 
known to have this combo working.

Nevermind. I was able to test another query which works, so all is 
well there. Thanks Doc, once again!

The failing query was a data migration issue, just FYI.

I just upgraded local ubutu and I made the rebol work with ia32-libs 
again, now rebol mysql client is givin me auth error

Gregg: what did you say you did to make it work? what did you move 
to 4.1 the mysql server or something else?

Janko: if you're using a newer MySQL version, you need my 4.1 driver.