World: r3wp

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'

select  * from table where f_text like '%table%'

but the wildcards may vary according the database

Amacleod - http://www.w3schools.com/sql/sql_like.asp

Thanks, I try it out in the morning. 

I googled but could not find it. I've had trouble finding a good 
source of docs for sqlite before.
w3schools looks good. 
Thanks again all!

Amacleod: if you search for ANSI 92 you should be able to find for 
the complete specification.

This also brings me a question: what about SQLite and UNICODE ?

I got the 'like' command working with a text string but I can not 
fiqure out how to use variables...
something like:

rslts: sql reduce [{SELECT * FROM books WHERE ftext LIKE '%?%' } 
var]

This works:

rslts: sql reduce [{SELECT * FROM books WHERE ftext LIKE '%string%' 
} ]

SQL reduce [{INSERT INTO table VALUES (?, ?, ?)} fname lname  phone]

Ignore that last message...Sorry

you have to create your dialected string first

A concatenation like 
like '%' || ? || '%'
shoud work

I got this unexpected behaviour:
>> do %~/Code/Library/Rebol/sqlite.r

Script: "SQLite driver" (26-Nov-2008)

>> connect %~/Code/SQLite3/mydata.db

** User Error: SQLite out of memory

** Near: make error! reform ["SQLite" error]

>> change-dir %~/Code/SQLite3

== %/Users/peter/Code/SQLite3
/
>> connect %mydata.db

== 3400768

sqlab,

reslts: sql reduce [{select * from fdbooks where ftext like '%'||?||'%'} 
srch]
== []
where srch: "ladders"
no error but not finding what should be there.:

reslts: sql reduce [{select * from fdbooks where ftext like '%ladders%'}]

== [[2 "FFP-LADDERS" "1-PORTABLE LADDERS" "2." " SIZES AND TYPES 
OF PORTABLE LADDERS IN USE^/" "" 4-Dec-2008/15:29:1
9] [4 "FFP-LADD...

I got it!
it works when srch: 'ladders

as word?

Not string?

What if I want multi-word string?
as in;

reslts: sql reduce [{select * from fdbooks where ftext like '%aluminum 
ladders%'}]

== [[9 "FFP-LADDERS" "1-PORTABLE LADDERS" "3." " CONSTRUCTION OF 
PORTABLE ALUMINUM LADDERS^/" "" 4-Dec-2008/15:29:19
] [10 "FFP-LADD...

and while we are here...
what about multiple words in string...
ladders
 and  "aluminum" as apposed to "aluminum ladders"

And how could I figure this out myself. Is this syntax a part of 
the sqlite.r interface or sqlite itself?