Vovsla 0 Posted July 30, 2013 Author Share Posted July 30, 2013 How open many connections to DB? Link to post Share on other sites
jchd 1,823 Posted July 30, 2013 Share Posted July 30, 2013 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 ... This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to post Share on other sites
Vovsla 0 Posted August 17, 2013 Author Share Posted August 17, 2013 I increase AdlibRegister time to 500 I open and close a second SQL connection inside search functions But the error still exist Link to post Share on other sites
jchd 1,823 Posted August 17, 2013 Share Posted August 17, 2013 Can you please post the changed code? Post it verbatim: no need to translate it or otherwise change it. This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to post Share on other sites
Vovsla 0 Posted August 18, 2013 Author Share Posted August 18, 2013 Code in attachment BalanceOfFood22.12.au3 Link to post Share on other sites
jchd 1,823 Posted August 18, 2013 Share Posted August 18, 2013 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!) This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to post Share on other sites
Vovsla 0 Posted August 19, 2013 Author Share Posted August 19, 2013 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 Link to post Share on other sites
jchd 1,823 Posted August 19, 2013 Share Posted August 19, 2013 (edited) 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 This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to post Share on other sites
Vovsla 0 Posted August 23, 2013 Author Share Posted August 23, 2013 ERROR: _SQLite_TimeOut(): undefined function. I don't have this function in standard library Link to post Share on other sites
jchd 1,823 Posted August 23, 2013 Share Posted August 23, 2013 Oops, sorry; It's _SQLite_SetTimeOut() This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to post Share on other sites
Vovsla 0 Posted October 9, 2013 Author Share Posted October 9, 2013 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. Link to post Share on other sites
Solution jchd 1,823 Posted October 9, 2013 Solution Share Posted October 9, 2013 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. This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to post Share on other sites
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now