Looks like my syntax is wrong. I am getting a syntax error on this this SQLitening statement:
rc = slExe "BEGIN IMMEDIATE", "E1"
How should this be written?
Shouldn't there be parenthesis?
rc = slExe("BEGIN IMMEDIATE", "E1")
Paul, Right you are... Without the parenthesis I was getting this compiling error.
Thanks.... I should have tried that before asking... The error message confused me.
I am trying to prevent a failure of the "BEGIN IMMEDIATE" in the event that some other user has already issued the BEGIN statement and locked the file. So, I'm testing and retrying for up to 3 seconds, does that sound like enough time? This is the routine, would you do it differently?
Do
rc = slExe ("BEGIN IMMEDIATE", "E1")
If rc = 0 Then Exit Do
Sleep 1000
c = c + 1
Loop Until c > 3
If c > 3 Then Function = -1 : Exit Function ' Lock Failed
'write records here
slEXE "END"
Are these the same questions that I see over on the SQLitening forum?
To wait for a lock shouldn't we be using the Timeout modchar in the slOpen function rather than a cumbersome loop?
Ya, I was debating that. A fellow on the other forum suggest the slEXE "Begin" method and it seamed to me to make sence from a "clarity" point of view. But like you said the necessity of adding a loop might make it a bit more cumbersome.
But, even if you used the ModChar in a slOpen or slSEL statement you would still have the problem of if it failed due to someone else (milliseconds before you) locking the file first. Wouldn't you still have to loop to test for failure?
Hi Marty,
The locking can be a little bit confusing. Here is my understanding (granted, I am only now starting to implement it in my program):
Internally, SQLitening server will loop and retry to perform your lock request depending on the timeout specified in the slOpen statement (default is 10000 milliseconds or 10 seconds).
If another user has locked the database - normally because they are in the midst of performing an slBuildInsertOrUpdate - then SQLitening will return a locked message if you also try to do a slBuildInsertOrUpdate.
Here is the beauty of SQLite and SQLitening in general. When you set the slOpen timeout value (10000ms by default), SQLite itself will continually poll the database to see if it is locked and if it is not locked then it continues on. It will continue to wait until the timeout value is exceeded and then it will report a BUSY message to the calling program. There is no need for you to have to do the loops (polling) yourself.
The only question that you need to ask yourself is how long you wish to wait in a situation where your database is locked before you report a BUSY signal.... 5 seconds, 10 seconds, 2 seconds, etc... and then set the timeout value accordingly.
In order for this to work well, I find that sending the "BEGIN IMMEDIATE" is the way to go prior to attempting an update to a potentially locked database. Doing so tells SQLite to try to get exclusive write access immediately and it seems to initiate the internal SQLite polling immeidately.
If this is not your understanding as well then please let me know. I haven't done a lot of multiuser SQLitening stuff yet.
Wonderful, Then all I should need to do is check the return code for success or failure and procede accordingly. Paul, thanks for the explanation.