World: r3wp

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.

BrianH,

Its working...I just do not know how to make it dynamic.

That is a good approach anyways, as it will help prevent SQL injection 
attacks.

Thanks for all the help.

I am not as familiar with the bugs in your SQLite access infrastructure 
as I am with SQLite itself, so I helped where I could :(

. .

FYI: I'm currently adding some stuff to Ashley's SQLite driver to:

1st: Handle in memory databases (":memory:")
2nd: To handle connection to more than one database file at once.

So, if someone did this already pleasae let me know :-)

A bit OT: Has anybody an idea how a "schema driven" database export 
does/could work?


I have an applicaiton that uses some tables, and records are linked 
by primary index IDs. Now I want to export a record and all its dependend 
records either into a new database or over the network to some other 
process.


Because ID ranges are different in the export target database or 
on the remote server, I need to rewrite the old IDs with the new 
ones.


At the moment I have a hand written, very app specific (and error 
prone) function for this. But I would like to do this in a much more 
generic fashion. Maybe just specifcing the relationship with some 
simple dialect and than have a generic function collecting everything.

rewriting IDs? A risky business :-) I have never done anything like 
that.

How else will you do it if you transfer one set of related records 
from database A to a database B?

of course you are right. You just have to be carefull or you could 
end-up with some "dead" child records.

Why not transfer the old ID to a new  indexed field oldID?

I just tried using an extra object with only the reference pointers. 
But I never checked, if it is working with more than one opened database 
because of the limitations regarding simulataneous access by more 
than one process.

sqlab, don't understand what you mean. How will an oldID help me 
to get all records back together? This would require that the application 
knows about oldID.

The new field OldID holds the former primary key.
You have to  join via the oldID instead of the primary key.

If you can not alter your select statement, maybe you can generate 
an adequate view.

Ok, I thought there was a different trick. Well, I'm not using JOINS 
nor VIEWS a lot in my app. I preferr to get back Rebol blocks and 
traverse these and collect what I need. Much simpler than hacking 
long SQL statements.

The newest version of SQLite adds support for nested transactions. 
I'm going to look at the SQLite driver to see how we can support 
this.

It would be nice to support collations too, but I was not successfull 
in wrapping that functionality ....

Is this used to define user defined comparing functions?

Do you have a use-case for this? Never had this requirement.

use case? proper national sorting? IF you do some SELECT on field 
like last name, and you want some in-between results, e.g. A - D, 
then Czech alphabet has C with a hook upon it, and it is supposed 
to sort right after C, but without collation support it will sort 
after Z ....

Ah, ok. How about sorting in Rebol? Does this help?

Putting this into SQLite would require, that we add those country 
specific sorting rules at the C level and provide a Rebol call, so 
select the correct sorting.

Do you have any references to an country specific sorting implementation? 
Than I can take a look how to add it.

There is some collation function which we need to wrap. I posted 
it here some time ago, but we were not succesfull in wrapping and 
utilising it. I also tried to look into IIRC Python sources, and 
it was not clear to me, how to specify it in REBOL level. IIRC it 
is callback type function ...

SET-SORTING-MODE "CZ"

And than have those collations hard coded to a Rebol SQLite DLL.

REBOL SQLite DLL? I don't want other DLL ... We need better interfacing 
to do it in REBOL as a binary, with back-pointer from C level :-)

... one more point for future. Imagine obtaining correct order from 
SQL, then using REBOL level grid, and column sort facility. I think 
that we also will need to get such things adressed in R3 directly, 
or it will distort sort order ...

But you can sort today in R2. Why do you need SQLite collations if 
you don't want to sort in SQLite?

I want to sort in SQLite. But then you receive your recordset to 
REBOL, you put it into grid for e.g., which has facility for sorting 
columns. Then you press particular column, and grid sorts your result 
recordset using rebol's built in 'sort function ... and the result 
is wrong ... (well, but this is minor issue, the importance is to 
get correct resultset from the query. I just tried to say, that R3 
has to address some localisation principles itself too ....)

Ok, now I got it. ;-)

this is not sqlite retated, but:

do http://box.lebeda.ws/~hmm/rebol/projects/ucs2/latest/make-ducet.r

Oldes - thanks - that is an interim solution for R2 :-)

I'm getting errors when I try to insert a binary file into sqlite


I have no problem when I read/binary an image from disk and insert 
it but when I download it from a mysql db its saving as those crazy 
text characters.


I'm converting it back to binary with "to-binary" and when I probe 
it it looks right but it keeps converting back to the original mysql 
output...

Any ideas what might be going on?


I can view the outputed image  from mysql when I use to-binary so 
I know that its not currupted.

I see what might be the prob...
Some images are large...Too large? And may be getting truncated.

I was using binary..should I use blob?

Actually size of the image does not seem to be the prob as this works: 

SQL reduce [{insert into images values (?,?,?,?,?,?,?,?)} "img/1" 
"img/2" "img/3" "img/4" "img/5" pic "img/7" "img/8"]
where pic is a large 4000x3000 full color photo.
I get no error.

But if I loop 50 and insert the above data 50 times I get an error???

IIRC there is a bug in Rebol's GC that can show up when using big 
datasets and corrupts data. Have you tried to play around with RECYCLE