World: r3wp

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

a ||  b 
is the sqlite syntax for concatenation of two strings.
http://www.sqlite.org/lang_expr.html

Dont't use the reduce

sqlab,

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

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

Equivalent to the above: {select * from fdbooks where ftext like 
'%aluminum%ladder%'}

How do I do that with variables?

Are you checking for whether
- Both of the words are in the string in that order

- Both of the words are in the string directly adjacent to each other 
in that order
- Both of the words are in the string in any order
- Either of the words are in the string in any order
?

It gives different results...


>> reslts: sql [{select * from fdbooks where ftext like '%aluminum%' 
|| '%ladder%'}]

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


>> reslts: sql [{select * from fdbooks where ftext like '%ladder%aluminum%' 
}]
== [[11 "FFP-LADDERS" "1-PORTABLE LADDERS" "3.1.1" {

Solid Beam Aluminum Construction- This type of ladder has a solid 
side rail co...

Answer my question first then I can answer yours :)

Pick one of the 4.

I need to search for multi-word string "aluminum ladder" (in that 
order...

but also would like to beable to search for the occurnace of multiple 
words in any order: "aluminum" and "Ladder" and "water" etc

2 and 3 I suppose

If you want to search for a multiword string, use LIKE '%aluminum 
ladder%', then use OR to add other clauses.
The choices above, with examples:
1: like '%aluminum%ladder%'
2: like '%aluminum ladder%'
3: like '%aluminum%' and like '%ladder%'
4: like '%aluminum%' or like '%ladder%'
The || operator means string concatenation, not or.

Thanks BrianH,
But how do I use it dynamically?

insert and update use "?" so you can use variables. 


reslts: sql reduce [{select * from fdbooks where ftext like '%'||?||'%'} 
srch]
== []
works when srch is a word!
but not when it contains a string!

>> srch: "aluminum"
== "aluminum"

>> reslts: sql reduce [{select * from fdbooks where ftext like '%'||?||'%'} 
srch]
== []

>> srch: 'aluminum
== aluminum

>> reslts: sql reduce [{select * from fdbooks where ftext like '%'||?||'%'} 
srch]

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

It sounds like you are on the right track, but running into bugs 
in the REBOL SQLite access infrastrcture.

Have you considered whether it is a casing issue?

I gues I can build the {select * from fdbooks where ftext like '%ladder%'} 
string dynamically with rejoin and insert it: 

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

== [[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...

If yo can be certain that any ' in your strings is being escaped 
properly, that may be the way to go.

to make it more sophisticated I can parse the search input for "aluminum 
ladders"  and seperate words as having "and" between each...like 
most search engines. THan build it and insert it.