World: r3wp

can't you queue your sql queries?

For now, I've wrapped the SQL calls with "busy" flag logic and that 
seems to work -- but it is a hack. The fact that a SQL function call 
inside a face timer event returns a dataset from a different face 
is shocking to me. REBOL should handle this properly in the background. 
My mental model of single-threaded event handling (rooted in classic 
VB) leads me to believe that this is a bug.


In addition to returning data to the wrong face, the SQLite shared 
object will also sometimes seg fault. It working fine now, though, 
with my busy flag hack.

It does sound like a bug.

Best way to get it looked at / resolved is to add  a bug report to 
RAMBO (the R2 bug database):
    http://www.rebol.net/cgi-bin/rambo.r

OK. Thanks.

I'm not clear what the issue is .. do you have different sql queries 
being triggered by timer events on different faces?

Anyway I use asynchronous database queries and have never seen this 
issue.

Graham, yes, I am triggering queries using timer events, and the 
sql results are being returned to the wrong calling function. My 
classic VB way of doing things doesn't work. How exactly are you 
doing your queries? Putting them in a queue to be serviced by a single 
function? For me that would also require including the face, so it 
can be updated when the sql call completes.

Here is Gabriele's response to my bug report (RAMBO ticket # 4411)


This is not really a REBOL bug. The SQLite driver is doing a WAIT 
1 in the DO-STEP function; calling WAIT inside an event handler (such 
as a face's timer event) is not really supported by REBOL and should 
be avoided. If you can't avoid it, you must be prepared to handle 
other events happening during that WAIT call, and you need to be 
very careful with blocking requesters (SHOW-POPUP, INFORM etc.) as 
they're likely to mess things up (they call WAIT as well).

My suggestions are:


1) File a bug report with the SQLite driver. There needs to be a 
way to avoid the WAIT, though I guess this is going to be complicated 
in R2.


2) Disable other events while you're using the SQLite driver, eg. 
clear SYSTEM/PORTS/WAIT-LIST and restore it afterwards.

3) Use a "global" flag like you're doing.

-Gabriele

Gabriele, when you say "clear SYSTEM/PORTS/WAIT-LIST", do you mean 
setting it to NONE temporarily?

I think he means storing the contents in another var temporarily, 
using CLEAR on it, then restoring the original contents using INSERT 
or APPEND.

Yes, as I use callbacks on all my sql calls

As Gregg said, it still needs to be a block! otherwise WAIT will 
complain (IIRC).

most likely, you'll only have the event port in the wait list, so 
it's just a matter of removing it and adding it back later. I can't 
guarantee this works 100% because I haven't tested it.

another alternative could be:


4) don't use face timers, but rather replace do-events with something 
like:

    forever [
        wait 1
        do-your-sql-calls
    ]


You'll still have other events happening while you're doing your 
sql calls in this case though, not sure if that can be a problem. 
The cleanest solution would be to change the driver so it does not 
wait, but I don't know SQLite so I can't say how easy that would 
be.

Thanks everyone for your explanations and suggestions.

There are several sqlite drivers ... are they much the same?

Also you could try opendbx which doesn't use 'wait

and has a driver for sqlite I think

http://rebol.wik.is/index.php?title=OpenDBX&highlight=opendbx

Thanks, Graham. I'll take a look at that option.

ty

Is there a way to force sqlite to release a database. 


I get a currupt db now and again and I want to automate the process 
of re copying an older version over it once I detect it is currupted 
using a try block.


But once the database is accessed it won't let me delete it or disconnect 
from it. Anywayaround this?

And has anyone been experienceing curruption to their db's...looks 
like binary characters get written into some fields randomly. Other 
people do not seem to be having this problem with the program on 
their computers.

I haven't yet seen a corrupt sqlite db so far. I am having like separate 
1000 databases "running". On what OS are you seeing this?

I"d bet it's being used on a desktop ....

xp and vista. I just had the issue on vista. I do not remember if 
it has occured on my xp machine.

Why Graham? desktop as opposed to server?

What version of SQLite are you using?  What is the pragma setting 
for synchronous? 2 is the safest. Are any processes that use the 
database still running when you have the problem trying to delete 
the database files?

Not sure on the version? (you are talking about the dll? or .r?)
Pragma setting? Not familiar with this.


Yes, my script is running still. That's my problem. I wanted to over 
write without having to close script down...I worked that out with 
a restart...good enough for now.

.r = 1.0.4

i see its up to 1.0.6... I'll try out the newer version...thanks

I'm regularly having corruption in my Firefox 3.5 bookmarks, which 
are stored in SQLite

I'm referring to the SQLite version. The current is 3.7.5. You can 
check the synchronous setting by executing the following query -- 
pragma synchronous. It will return a 0 (off), 1(normal) or 2 (full). 
See http://sqlite.org/pragma.html#pragma_synchronous.


I've had database corruption before because I didn't backup the files 
correctly. It's best to use the backup API and not just copy the 
files.

Is there just one process accessing the files? I also use the WAL 
journal mode for better concurrency. Much less chance of getting 
SQLIte file locks when multiple processes are reading/writing the 
database. See http://sqlite.org/pragma.html#pragma_journal_mode.

desktop because people run all sorts of junk on their PCs causing 
corruption!