World: r3wp

how to do effectively following using SQL?

our subsidiary company changed IDs of some products during the inventory 
in our system ;-) I need to create table called e.g. transform-id(oldid, 
newid) .... and now I would like to somehow do:


foreach row maintable [if found? in transform table maintable/id, 
then replace maintable/id with newid) :-)

I really sometimes think of simply submitting two queries into sql, 
having results in block, doing it in rebol level, and then to build 
tables from the scratch by inserting into new tables :-)

Yep. Rebol is superb for data manipulation

Perhaps the REBOL driver hasn't caught up.

 ... SQLite datatypes have not changed for a long time, the driver 
 supports them all in direct mode.

I don't understand what is our /direct mode good for
 ... http://www.dobeash.com/SQLite/user-guide.html#section-2.1.3

OK, I think that is what Petr concluded :)

even if column type is text
 ... see http://www.sqlite.org/datatype3.html#affinity

WTF! I got burried by very strange behavior, which I would like to 
know what happened. IMO it is not related to SQLite itself, but maybe 
it is a deeper REBOL bug? Simply put I have following statement:


sql "update or ignore produkty set kod = (select novy_kod from prevodnik_devitky 
where prevodnik_devitky.kod=produkty.kod) where exists (select kod 
from 
prevodnik_devitky where prevodnik_devitky.kod=produkty.kod)"


... and I was becoming crazy, that the update did not happen. No 
indexes used (well, I am starting practically with sql, so no need 
to mess things more :-), and when I put EXACTLY the same line into 
SQLiteAdmin tool, it was performed OK.


I was really becoming mad, because it seemed to randomly work, when 
I changed/simplified the expression. Then I remembered my 2 years 
old ODBC scripts, when we imported data into SAP, from Database Advantage 
Server. I remembered there was some problem with multiline statement 
unless I used trim/lines.


I thought to myself, well, it was ODBC driver related, but why not 
to try it? So I tried to reformat my query to:


sql trim/lines "update or ignore produkty set kod = (select novy_kod 
from prevodnik_devitky where prevodnik_devitky.kod=produkty.kod) 
where exists (select kod from 
prevodnik_devitky where prevodnik_devitky.kod=produkty.kod)"


... and it started to work from my script. And I ask once again - 
What is going on here? 2 hours lost, which drove me nearly insane 
:-) I use no special editor but Notepad. The statement returned no 
error, so I thought it got performed, just incorrectly. It all seems 
to be related to one aspect - line is too long, so it wraps in Notepad 
and unless I use trim/lines, it is not performed.


Any educated gues to what is happening here? It is not SQLite related 
imo, I just did not know where to put it, as general bugs group is 
not here ....

ah, today I got another data corruption with sqlite driver ....

uh, it is update or replace which messes my data with nonsense :-(


update or replace produkty set kod = (select novy_kod from prevodnik 
where prevodnik.kod=produkty.kod)  where exists (select kod from 
prevodnik where prevodnik.kod=produkty.kod)


It is supposed to simply to lookup the "prevodnik" (translator) table, 
if there is new code (novy_kod). both tables seem to be OK, but after 
that statement kod in produkty (products) gets messed up ....

produkty.kod is unique ... if I remove "or replace" part, data seem 
to be OK, but it just no more does what I need it to do ....

I found the culprit but I doubt it is enough to track possible bug. 
Issuing following statement will make subsequent update corrupting 
data:


sql trim/lines "insert into prevodnik select kod, nazev, puvodni_kod, 
puvodni_kod, typ from produkty where substr(kod,1,1)='9' and kod 
like '%BZ'"


update or replace produkty set kod = (select novy_kod from prevodnik 
where prevodnik.kod=produkty.kod) where exists (select kod from prevodnik 
where prevodnik.kod=produkty.kod)

Petr, the new SQLite 3.5.3 release states:


Fix a long-standing bug in INSERT INTO ... SELECT ... statements 
where the SELECT is compound.

Maybe that's the problem?

gee, what are they doing? 50KB more size of dll to fix few bugs? 
They should aredy fix their docs whee they claim that other engine 
libraries start at 450KB ... they are nearly there too ...

... and they definitely should completly remove their claim that 
1 file for db is an advantage. That is the most serious obstacle 
of sqlite ... simplicity comes via ability to easily backup ... one 
file per index, table ...

Robert - tried that. Sadly it does not fix the issue. I will try 
to get to their ml and post the finding. Maybe I am doing something 
incorrectly, otoh I think that corruption should never happen, even 
if query would be nonsense :-)

Well, simplicity lies in the eyes of the beholder ... just having 
to back up a single file seems pretty easy to me ...

me too

if that size is not necessarily 100MB and you don't need to back-up 
all the files all the time ....

..then why don't you just back up the tables that you ?

..want ????

you can, by creating some code to do so, not by simply looking into 
the directory and using default OS facilities.

well, SQLite is really cool, but simply noone can defend that one 
argument to me. I also communicated that "defficiency" on their ml, 
and simply put - it is the way it is. 'Attach function is just nasty 
workaround, nothing more. But - I talk about some 5% of feature, 
which would I found usefull, I will use sqlite anyway, as simply 
put there is no simple replacement to what it provides ...

I didn't think that you needed to write any code to backup individual 
tables in SQLite but just supply the table name as a parameter to 
the .dump command.

I believe you can do this from the command line with SQLite3.

It seems I found a bug :-) http://www.sqlite.org/cvstrac/tktview?tn=2832

I ask here too, I want to adopt a database system for my rebol projects: 
which are the advantages of SQLLite over RebDB ? Why should adopt 
the first or the latter ?

I ask here too
 No need to cross post. We can all see the messages.

SQLite has one main advantage - it supports SQL like syntax - you 
can join tables. I can't imagine living without those features anymore.

RebDB is also mostly - in memory only database. It does not live 
on hd. So - SQLite has one advantage here - it supports locking over 
file-shared SQLite database.

Sorry Henrik my intentions where not to SPAM, I have later found 
the SQL lite group than the RebDB. Promise I'll write only once starting 
from now. Don't be upset :-)

That is still not full-fledget SQL server - those provide you with 
network independent access ...

Pekr, joining tables and having the data on disk are already two 
good points for SQL Lite. Obviusly I need Rebol/Pro licence before 
using it.

not necessarily.

I ahve read in the doc that Rebol/Pro is necessary. Could you explain 
?

Well - there is btn-sqlite (better than nothing) driver on rebol.org 
It works, with one bad effect - black console window appearing during 
the shell call. Just recently I put it on high priority list for 
W.7.6 to be fixed - all is needed is to set one flag for shell call 
....

I hope that it gets fixed.

Thanks pekr, I'll take a look at it. Next year, when I'll have more 
knoledge in RebGUI and Rebol2 I need to start a project which needs 
a database but it will be a single user project so SQL lite.is good 
enough.

Next year you will be using R3 :-)

Then I'll give me Rebol/Pro or the whole command/sdk as present.

NO, I want to use R2. I will be using R3 only for small projects 
and to help the comminity into debugging ! Have you already read 
what I think about people complaining R3. Your is a provocation !!! 
:-)))))

To GiuseppeC there is a simple server based on SQLite look at SQLIte 
Wiki/SQLiteNetworks/uSQLiteServer a protocol to this is also in rebol.org.


Anyway I can tell you a secret not to tell anybody: try to use rebface.exe 
from public available REBOL/SDK 2.7.5 BETA . It seems the /Pro restriction 
is not applied there for some reason and SQLite protocol works sweet 
for me there.

You can got  an idea run "local database backend" in one rebface 
process and application in another on rebol version of your choice. 

THANKS ASHLEY FOR A GOOD JOB.

Now I expect a totally free Rebol/Command :-)

You don't need /Command SQLite, just library access and you get that 
in /Pro. There are other advantages to /Command though.

I know. I will buy the /pro version next year and the whole package 
when my application will be ready.

(Hoping in a free upgrade to R3)

Petr, congrats to really find a bug. I think I have been hit by this 
one too.

I will make an update of the SQLite engine. I'm still using a rather 
old one.

New version released ... hehe, my ticket is part of the announcement 
:-) http://www.sqlite.org/news.html

what's sqlite's domain?