World: r3wp

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?

You need a primer in sql ...

I can find the sql commands but I do not know how to rebolize them. 

Where can I find that I need to do this: '%'||?||'%' ?

I do not see anything like that in any docs I can find.

You construct your sql statement and test it.

Then you substitute your variables ... that's it.

the sql statements are standard sql.  You don't need to use the ? 
stuff .... ie. you can compose your statement before hand.

I don't know sqllite's syntax .. but this looks wrong to me


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

it should simply be


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

Those || are string concatenation, afaict. Use JOIN in REBOL for 
that.

Or REJOIN.

I've never tested whether the sql dialect can replace ? inside single 
quotes.

Anyway, you don't need to use the sql dialect ... just create your 
sql statement and then execute it.

And once you learn sql, then you can look at the dialect .... which 
is a simple substitution thing.

Instead of LIKE, you can also use CONTAINS

SQLite has a CONTAINS extension?

oh ... sqlite ? ... ooops