World: r3wp

Versions 3.3.9 (first released 4-Jan-2007) onwards. Mac OS X used 
to ship with a really old version (3.0.8) dating from late 2004.

I'm currently using 3.6.3 and will update to 3.6.4 the newest release.

Ashley, there is still a problem with Click/Button and over.
The colour is irreversibly changing to the default in your demo

sorry, wrong group.

1.0.6 available at: http://www.dobeash.com/download.html


Fixes finalize error as documented at: http://www.mtcnet.net/~henryvm/sqlite/

Ashley, I just remembered that you can't call CONNECT/CREATE several 
times in one application. It gives the error "Database already connected" 
even if you use different file names.


To open more than one database file you have to use the sql ATTACH 
command starting from the 2nd database file.

I think makeing CONNECT handling this case implicit would make a 
lot of sense to make it simpler for users. So the programmer know, 
it's possible to alway call CONNECT/CREATE. What do you think?

BTW: I have this problem on Linux at the moment. The code runs with 
several calls on Windows.

Forget the last one. Doesn't work.

There is already a handler for this case but only if all databases 
are given upfront in a block.

And those files exist.

I now just commented the line that checks if a database is already 
connected. At least it now works but I'm not sure if this has some 
undesireable side-effects.

Ok, some more findings. I think the best way is to make a copy of 
the SQLite object for each database file. Than things are independent. 
The only thing to solve is to find an elegant way to select which 
SQLite object/connection to use without having to pre-fix all calls.

Any ideas for this?

Maybe something like a current database.

I'll look into it ... I like the idea of making this implicit.

If I'm updating multiple fields is this the syntax?

SQL reduce ["update books SET bk=?, chap=?, section=?, up_date=? 
WHERE id=?" book chapter section update id]


I do not get an error but it does not seem to be updating all the 
fields.

Ashley, ok. Let's do it together, I can spend some time to discuss 
things and code some variants.

Gyus, how to encrypt data in SQLite? I can't do it at app level (field 
storage level), as then SELECT would not work. Is the only solution 
to buy some SQLite variant, which encrypts at low level?

Yes.

I did buy the extension. Works very well.

Any link?

Could you use (say) Truecrypt to host the data files on a encrypted 
partition?

well, but at some point, you open-up that partition in order to be 
able to access it. The security is not there anymore. What I would 
like to have is direct SQLite low-level encryption, so that file 
might be visible to FS, but still encrypted. And your app provides 
password or something like that ... IIRC BrianH is using some such 
solution, I just don't remember its name.

I am not hesitating to invest some money into it ...

Look on the SQLite homepage under professional support.

IIRC encryption costs $2000

uff, so much? I expected something sub 500 ;-)

Robert, I was thinking we can depreciate the /create refinement by 
making that implict as well ... and the change required to support 
additional CONNECTs after the first should be as simple as changing 
the line that reads:

	all [dbid sql-error "Already connected"]

to something like:

	if all [dbid file? database]  [
		unless find file %/ [insert file what-dir]
		sql rejoin ["attach '" ...
		return
	]


which then raises the interesting question as to whether we should 
force database to be file! (so you'd have to attach multiple databases 
by issuing multiple CONNECTs ... it would certainly simply the CONNECT 
logic! ;)

I see one problem, if the to-be-opened database doesn't exists yet 
it needs to be created. This can only be done by a call to sqlite3_open 
and not via the ATTACH sql command.

And than the returned DB handle has to be used for all actions against 
this database file. It's much like a file handler.

I see two options:

1. We enhance the driver to be able to handle more database handles 
at the same time. This needs a way to select a database handle as 
the current one.


2. We make the driver as a prototype object which carries everything 
for one database handle. Than we need a way how to state which instance 
to use.

Banging my head against a wall!

This works:

SQL reduce [{UPDATE notes SET note=?, up_date=? WHERE book=? AND 
chapter=? AND section=?} note_text now bk_tit/1 bk_tit/2 bk_tit/3]
But this does not:

SQL reduce ["Update fdbooks SET up_date=? WHERE ref_number=?" now 
ref]
nor this:

SQL reduce [{UPDATE notes SET note=?, up_date=? WHERE book=? AND 
chapter=? AND section=?} note_text now bk_tit/1 bk_tit/2 bk_tit/3] 
Any ideas?

Sorry I mean:
nor this:

SQL reduce [{UPDATE fdbooks SET bk=?, chap=?, sec_num=?, ftext=?, 
key_words=?, up_date=? WHERE ref_number=?} blk/2 blk/3 blk/4 blk/5 
blk/6 (to-date blk/7) blk/1]

I found part of the problem...

If I'm updating multiple records it fails to update but if update 
one record it updates.
I'm using a transaction here. do I need to place each var in ()?

which version of sqlite dll are you using? there was one bug with 
update, which I reported one year ago, which even corrupted data. 
They fixed it thought in next version. So hopefully this is not your 
case ...

Found it!
I had my 'begin' of my transaction inside the loop.

dope!

Thanks Pekr.

That's what happens when you work in hte middle of the night.

Hello, I am experimenting with SQLite and I have a question:

How do I store a web page retrieved with READ onto a SQLite field 
?

Forget it, just found the solution. My fear was all about string 
handling but everything seems going well.

It seems to work even with block of lines

Yep, by default the driver molds and loads REBOL values. Use /direct 
if you don't want this behaviour.

how do you search for a string within a column. Is that what they 
refer to as full text search? (fts)

does sqlite.r support these fts tables?

Let me first make sure i need fts...
I want to find a sub-string within a larger string:

for example: find the the rows that contain word "table" in a column 
called f_text one row of which might contain  "When inserting into 
an fts table, if no docid is provided,"
This would be a hit since table is contained in the string.

like
 is your friend

syntax?

See SQLite homepage. It's something like this: LIKE '%...%'

Begins with is LIKE '...%'
Anywhere is LIKE '%...%'
End is LIKE '%...'
Rebol filenames: LIKE '%.r'