World: r3wp

Graham, that features page also says how to use SQLite to manage 
the files that store your application data. It's a cool hack.

Maybe Altme could use it to store local messages?

I'm storing user configuration data on the server (firebird) in a 
text blob field ... which is one serialized rebol object.  Each time 
I change a configuation parameter, the whole object is saved back 
to the database.

It does raise interesting possibilities though for other things.

I have found an interesting PHP project I would like to see ported 
on Rebol: http://adodb.sourceforge.net/. It is a layer to access 
nearly all database available in this world with plugings.

how can I connect to multiple databases at the same time without 
disconnecting?

via the 'attach command?

with the driver, just use connect [db1 db2 db3] etc. IIRC, you can 
attach max 9 databases ...

Or recompile the DLL with MAXDATABASES optinon increased

What I need is connect to a database , than in a second time, connect 
to another database, multiple connections open, no sqlite attach, 
possible?

Why not use ATTACH?

I wanted separate connections, but maybe I understand it wrong as 
it is not same as with mysql, so probably have to go with attach, 
thanks for the suggestions.

I'm on os x 10.5.2, sqlite.r works fine with system preinstalled 
sqlite which is version 3.4.0, but doesn't work with latest version 
3.5.7 (installed thru macports), is this known or os x specific?

I expect that is due to changes in SQLite - it has changed the API 
a bit between 3.4.0 and 3.5.x

macport/sqlite3 version 3.5.4 worked but was 2 times slower than 
with 3.4.0

Thanks BrianH, i'll stick with 3.4.0

btw, are you all considering moving to treatbase?

maybe someone could check if 3.5.7 works with other platforms, if 
not, the sqlite.r maintainer can maybe spot an easy fix looking at 
http://www.sqlite.org/changes.html

I have 3.5.7 running on Windows together with Rebol. Works perfectly.

I got the DLL into 215KB :-)

Tretbase? I think it's pur Rebol based, right? No I won't. I don't 
think Rebol is the right tool for a database engine.

Robert;  I'll fight you on that.  :)  Depends on the scale.  RebDB 
is beautiful for dbs under the 10K ish limit;  TRETBASE is destined 
to be a very handy REBOL scripting database engine, and Paul has 
been hammering on some larger datasets, (not something I'd plan on, 
but it's being developed that way).  So unless you are working in 
the 100K+ record arena, or need multiple user concurrency, I do think 
REBOL is the right tool for a database engine.

;-) Are RebDB and TRETBASE ACID conform?

I didn't expect to win the fight, but I wasn't really expecting to 
get pinned on the first move.  :)  But I'll struggle a little more. 
 In the long run I think the simplicity of the pure REBOL solutions 
(and a little careful manual (or scripted) management) will have 
fewer headaches than a larger full scale  ACID database (again in 
the small dataset arena).


My opinion may vary over time and over projects...but not today. 
  And sorry about clogging the SQLite chat.  rebols could well build 
up a fair amount of expertise with this engine given that  load/library 
is now open to all.

Ok, and please don't get me wrong. SQLite is nice because it's small 
and simple to attach to Rebol, thanks to the work of Ashley. So even 
for 10K records you can use it because the costs are low.

But, it stays a SQL database not directly optimized for Rebol usage.

So, getting a database system that has much better / native Rebol 
support makes a lot of sense. Hopefully such a system will once be 
implemented on the C level for good performance etc.

Will, did you try modifying the following lines in the driver:

	*lib: load/library ... ; to call the correct version

 *prepare: make routine! ... ; to use "sqlite3_prepare_v2" instead 
 of "sqlite3_prepare"

Thank you Ashley, have changed to v2 but get:

connect/log/create d 
** User Error: SQLite out of memory
** Near: make error! reform ["SQLite" error]

Are you able to connect to an existing db file?

rebview cl-sqlite.r


Any idea why I'm getting this (using Ubuntu and Ashley's sqlite.r): 
Script Error: Library error: libsqlite3.so: cannot open shared object 
file: No such file or directory
** Near: *lib: load/library switch/default fourth system/version
>>

Do you perhaps need the sqlite library in your working folder?

This solved the problem: "sudo apt-get install libsqlite3-dev"

Chris, you are right. Thanks!

How can I do an "if not exists? on a sqlite table?

All the tables are stored in the 'sqlite_master' table (including 
the 'Create' command)

select name from sqlite_master where type = 'table' and name = ?

Thanks, Chris. I have this working now.

What is the recommendet way to deal with a lock and to get rid of 
dat-journal?

I do not like to stop all processes and to manually remove the file.
Also to increase the retries has its limitations.
So, how do you handle the problem?

Ok, got it.

I increased the retries and use a shorter wait with a random duration 
If there is still a lock,  I use an sqlite/*step sqlite/sid

I found something odd with an app I'm writing. If I insert or update 
a field (varchar) with a string that has any spaces in it. , it throws 
a rebol error if I try to select it. 

For example, I have a string "OK BOb" stored and when I sql {select 
* from vocab}
** Script Error: OK has no value
** Where: rejoin
** Near: OK BOb 
It doesn't matter what the first word is.

I can't believe I'm the only one so I must be doing something wrong.

how do you connect to DB? There are two modes - normal, and /direct 
....

try to open it with /direct refinement ...

I just use "connect  %dbname.db

OK, I'll try that.

How did you know?

Hi all, is the SQLite driver for REBOL different to the standard 
in that it has locking working? Does this mean it can be used for 
small multi-user apps or is it that people DO but SHOULDN'T like 
with Access?

Thanks

I see.../direct "Do not mold/load REBOL values"

Says: # Lock detection
The driver detects locks and initiates retries on your behalf.

@ http://www.dobeash.com/sqlite.html

Though I have not tried it in multi-user env.