SQLite.au3 Error

[Vovsla]

How open many connections to DB?

[jchd]

That's dead easy:

_SQLite_Startup()
...
Local $hDB1 = _SQLite_Open($DBFile1)
...
Local $hDB2 = _SQLite_Open($DBFile2)
...
Local $hDB3 = _SQLite_Open($DBFile3)
...
; operations on any DB independantly
...
Local $hDB1Bis = _SQLite_Open($DBFile1) ; opens another connection to DB1
...
Local $hDBMem = _SQLite_Open()   ; this is a memory DB
...
; use the handle you need
_SQLite_GetTable2d($hDB3, "select ...

[Vovsla]

I increase AdlibRegister time to 500
I open and close a second SQL connection inside search functions
But the error still exist

[jchd]

Can you please post the changed code? Post it verbatim: no need to translate it or otherwise change it.

[Vovsla]

Code in attachment

BalanceOfFood22.12.au3

[jchd]

Hi,

I found issues with your new script, but I'm not sure they contribute to the problem you're facing, but they need fixing anyway.

Inside all of Import/Export functions, you use:
_SQLite_Close()
_SQLite_Open($FileName)
...

_SQLite_Close() will close the last connection and I'd rather see a connection handle passed here.
_SQLite_Open($FileName) is not assigned, so you don't have the handle to this new connection and can't use it. to wit, you invoke _SQLite_GetTable2d($MainstreamSQL,...) right after that. It is probably not doing what you expect.

Worse, you do:
FileCopy($DBFile, $FileName, 1)
*** Never *** ever *** do *** that *** : you omit the case where a hot journal file exists. You should use my SQLite backup UDF (use the search feature to find it, I'm typing offline right now) to duplicate a live DB.

You should fix these Import*/Export* functions wrt to the points above.

Unless I've misread your code, it seems you don't have to open and close the DB inside AutoSearch and subsequent functions. I'd open one connection for that at start and close it explicitely at termination, before shutdown. Repeating useless opens are slow operations, need reread of the schema and flush the DB cache pointlessly.

Another hint: you have what you should consider as two threads (the mainstream code and the AutoSearch code) even if they are not threads, technically speaking.
It's correct to use separate connections to the same DB, but I'd go one step beyond. To avoid any possibility of deadlock or SQLITE_BUSY conditions, what I always do is put a very large timeout on every connection I open:
_SQLite_TimeOut($hDB1, 60000)  ; one minute!
The timeout (in ms) must be longer that the worst case sequence of the longest sequence of the longest transaction(s).  Forget the verbiage, and retain that it must be large enough up to ridiculously large.

Then wrap any transaction where write(s) may occur (inserts/updates/selects triggering modifications/pragmas which may modify the DB) with immediate transaction:
_SQLite_Exec($hDB1, "begin immediate;")
...
_SQLite_Exec($hDB1, "commit;")

This way, all transactions will patiently wait for the DB to be ready without resulting in error condition due to concurrency.

Also to ease concurrency (you never know how your program will evolve over time), I recommend you use the WAL journaling mode. You see, default journaling allows ONE writer OR multiple readers at any given time. WAL mode, allows ONE writer AND multiple readers concurrently. Drawback: WAL mode uses shared memory internally and won't work over a network, but since existing network file sharing protocols (Windows, Linux, OS X, any OS) are SOooooo buggy, use of bare SQLite over a network is nothing but blind gamble.
You only need to set WAL mode once for a given DB: it persists for subsequent connections, open/close and power cycling.
_SQLite_Exec($hDB1, "PRAGMA journal_mode = WAL;")  ; right after _SQLite_Open
WAL mode uses two hot journal files which must *_never_* be dealt with: let SQLite handle them transparently.


Also can you please switch your source to UTF-8, so that I (or someone else not using the same ANSI charset as you) don't get garbage instead of Russian?
Doing so is easy in SciTe: with your file open, hit: File > Ecoding > UTF8 with BOM, then modify the script anyhow (add a space and remove it is OK), then save it. Et voilà!

(sorry for the text wall!)

[Vovsla]

Thank you for information about Import/Export functions

In previous version i made two threads the mainstream code and for the AutoSearch code, streams opened at the beginning of the program, but i had the same error. However i didn't set _SQLite_TimeOut

Yes, i'll switch source to UTF-8 with BOM

Thank you very much for useful information

[jchd]

Also don't forget immediate transactions for those which may write. That's an efficient barrier to advisory locks trying to promote to write locks when a deadlock condition would result. It avoids all SQLITE_BUSY, *_SCHEMA, *_LOCKED, *_PROTOCOL errors, so you don't have to even think about them, lest code against. A great simplification to robust code.

Next step with your revised code will be to try hard(er) to reproduce and isolate at my end. I definitely want to have the final word about that situation.

Edited August 19, 2013 by jchd

[Vovsla]

ERROR: _SQLite_TimeOut(): undefined function.

I don't have this function in standard library
 

[jchd]

Oops, sorry; It's _SQLite_SetTimeOut()

[Vovsla]

Error disappeared only when i created new database, all data was imported from old database.

Thank you very much for attention to my problem, you gave me very useful tips.

Thank you again.

Best regards.

[jchd]

Good news! Indeed I suspect it was due to some datatype discrepancy. I confess I couldn't devote enough time at my end to pinpoint the issue.

Best of luck for the future of your developments.