|
Post by meerkat on Mar 11, 2019 4:44:57 GMT -5
Here is some proof that LB5 does not forget. Not that this solves the lock problem, but it is a problem. Do the following: Run 1 run it to prove that it works. Run 2 1. comment out at the top: 'sqliteconnect #sql, db$ 'sqliteconnect #sql1, db$ 2. un-comment at the top: gosub [connectDb] gosub [connectDb1] 3 Run it and it gets an error. Run 3 go back to the original and 1. un-comment out at the top: sqliteconnect #sql, db$ sqliteconnect #sql1, db$ 2. comment at the top: 'gosub [connectDb] 'gosub [connectDb1] Run it and you get lock errors. Remember we proved it worked with the first run. The only way you can get it to work again is to close LB5 and start over. Somehow LB5 needs to get a fresh start when you click the run button and not have to close it down ans start over.
** Basically you need to start LB5 over each time you test because you don't know if you have left over errors from the previous test. That's why it gets so confusing each time
' ---------------------------------- ' LB5 lock test monster ' ---------------------------------- db$ = "test.db" sqliteconnect #sql, db$ 'gosub [connectDb] 'gosub [connectDb1] ' *************** sqliteconnect #sql1, db$ ' ****************
' No table then create it with a unique key ' sql$ = "SELECT * FROM sqlite_master WHERE tbl_name = 'xx'" #sql execute(sql$) rows = #sql ROWCOUNT()
if rows = 0 then sql$ = " CREATE TABLE xx ( xxNum integer(5), xxDescr VARCHAR(10), xxCount integer(5))" #sql execute(sql$) #sql execute("CREATE UNIQUE INDEX xx_num ON xx(xxNum)") ' give it a unique index print "Table A Created" end if
' re populate the table with new stuff #sql execute("DELETE FROM xx") for i = 1 to 100 #sql execute("INSERT INTO xx VALUES (";i;",'xx_";right$("000"+str$(i),3);"',0)") next i
' ************ this causes it to lock ************* sql$ = "PRAGMA table_info(xx)" ' returns cid|name|type|notnull|dflt_value|pk print sql$ '#sql1 execute(sql$) ' **************** comment this and it works ' *********************** ' read the table 10 rows 10 time ' then do INSERT OR UPDATE to the last row read ' for pass = 1 to 10 #sql execute("SELECT * FROM xx WHERE rowid > ";pass;" ORDER BY xxNum LIMIT 10") rows = #sql ROWCOUNT() 'Get the number of rows print "Number of rows in table xx:";rows for i = 1 to rows #row = #sql #nextrow() xxNum = #row xxNum() xxDescr$ = #row xxDescr$() xxCount = #row xxCount() ' ******************* sql1$ = "UPDATE xx SET xxCount = xxCount + 5 WHERE xxNum = ";xxNum #sql1 execute(sql1$) ' *********************** print xxNum;chr$(9);xxDescr$;chr$(9);xxCount next i print "** end of select ** pass: ";pass ' INSERT OR REPLACE the last record read ' sql$ = "INSERT OR REPLACE INTO xx VALUES(";xxNum;",'";xxDescr$;"',";pass;")" print sql$ #sql execute(sql$) '#sql execute(sql$)
next pass
#sql disconnect() end ' ------------------------------------- ' Connect to Database ' -------------------------------------- [connectDb] ' Connect to the DB sqliteconnect #sql, db$ ' Connect to the DB #sql execute("PRAGMA journal_mode=WAL") #sql execute("PRAGMA locking_mode=NORMAL") #sql execute("PRAGMA synchronous=0") #sql execute("PRAGMA wal_autocheckpoint=0") #sql execute("PRAGMA cache_spill=0") #sql execute("PRAGMA journal_size_limit=0") return [connectDb1] ' Connect to the DB sqliteconnect #sql1, db$ ' Connect to the DB #sql1 execute("PRAGMA journal_mode=WAL") #sql1 execute("PRAGMA locking_mode=NORMAL") #sql1 execute("PRAGMA synchronous=0") #sql1 execute("PRAGMA wal_autocheckpoint=0") #sql1 execute("PRAGMA cache_spill=0") #sql1 execute("PRAGMA journal_size_limit=0") return
|
|
|
Post by metro on Mar 11, 2019 5:28:37 GMT -5
The only way you can get it to work again is to close LB5 and start over. Somehow LB5 needs to get a fresh start when you click the run button and not have to close it down ans start over. ** Basically you need to start LB5 over each time you test because you don't know if you have left over errors from the previous test. That's why it gets so confusing each time Dan, I copied your code
and immediately made the changes you recommended then started lb5 (so NO previous run of the code or lb5)
and I get the lock error now it's as clear as mud whats happening!
EDIT: remove the PRAGMA statements and it functions without error and yes more than once without closing lb5
|
|
|
Post by meerkat on Mar 11, 2019 5:42:02 GMT -5
Yes the error is another thing. I want to write the same program using SQLite DLL and see if it works.
What is important is we can all be doing the same test. But if you start LB5 over, and I don't, we will likely get different results.
At least I now understand why we get so confused when comparing notes..
LB5 definitely has a problem here. Now when I test I'll bring up a fresh copy of LB5 each time.
It's raining here, so I'll be inside. And probably have a brew or two to clear this up. Dan
|
|
|
Post by metro on Mar 11, 2019 5:51:18 GMT -5
Yes the error is another thing. I want to write the same program using SQLite DLL and see if it works. What is important is we can all be doing the same test. But if you start LB5 over, and I don't, we will likely get different results. At least I now understand why we get so confused when comparing notes.. LB5 definitely has a problem here. Now when I test I'll bring up a fresh copy of LB5 each time. It's raining here, so I'll be inside. And probably have a brew or two to clear this up. Dan It's my understanding that Carl is actually using the DLL, if that's the case there is something a miss with lb5's treatment of PRAGMA I seem to recall him commenting in a previous post that it was easier to use the DLL rather than write his own. PS
I prefer a red if its cold and wet,.......... enjoy
|
|
|
Post by meerkat on Mar 11, 2019 7:52:50 GMT -5
Ok! Tested LB4 and LB5 doing the same thing.
LB4 works without locks LB5 gives a record lock. Both open the DB twice. Both do updates after a record read. Here is where LB5 locks.
Hopefully this code is correct. Maybe you guys who know LB can check it and make sure it's correct.
Here is LB5 code' ---------------------------------- ' LB5 lock test monster ' ---------------------------------- db$ = "test.db" sqliteconnect #sql, db$ sqliteconnect #sql1, db$ ' ****************
' No table then create it with a unique key ' sql$ = "SELECT * FROM sqlite_master WHERE tbl_name = 'xx'" #sql execute(sql$) rows = #sql ROWCOUNT()
if rows = 0 then sql$ = " CREATE TABLE xx ( xxNum integer(5), xxDescr VARCHAR(10), xxCount integer(5))" #sql execute(sql$) #sql execute("CREATE UNIQUE INDEX xx_num ON xx(xxNum)") ' give it a unique index print "Table A Created" end if
' re populate the table with new stuff #sql execute("DELETE FROM xx") for i = 1 to 100 #sql execute("INSERT INTO xx VALUES (";i;",'xx_";right$("000"+str$(i),3);"',0)") next i
' ************ this causes it to lock ************* sql$ = "PRAGMA table_info(xx)" ' returns cid|name|type|notnull|dflt_value|pk print sql$ #sql execute(sql$) ' **************** comment this and it works ' *********************** ' read the table 10 rows 10 time ' then do INSERT OR UPDATE to the last row read ' for pass = 1 to 10 #sql execute("SELECT * FROM xx WHERE rowid > ";pass;" ORDER BY xxNum LIMIT 1") rows = #sql ROWCOUNT() 'Get the number of rows print "Number of rows in table xx:";rows #row = #sql #nextrow() xxNum = #row xxNum() xxDescr$ = #row xxDescr$() xxCount = #row xxCount() ' ******************* sql1$ = "UPDATE xx SET xxCount = xxCount + 5 WHERE xxNum = ";xxNum #sql1 execute(sql1$) ' *********************** print xxNum;chr$(9);xxDescr$;chr$(9);xxCount print "** end of select ** pass: ";pass ' INSERT OR REPLACE the last record read ' sql$ = "INSERT OR REPLACE INTO xx VALUES(";xxNum;",'";xxDescr$;"',";pass;")" print sql$ #sql execute(sql$) '#sql execute(sql$)
next pass
#sql disconnect() end
This is LB4 code
' ========================================== ' Lock test monster ' ==========================================
Struct RS,_ BOF as long,_ ' is True when CurrPos = 1 EOF as long,_ ' is True when CurrPos = Rows Handle as long,_ ' address of recordset data returned by sqlite3.dll (dont't use) Rows as long,_ ' number of rows in recordset Cols as long,_ ' number of columns in recordset CurrPos as long,_ ' current row in recordset StrAdr as long ' address of data item (pointer to a string)
db$ = "test.db" ' Database Meta Data File dim info$(10,10) ' array for fileExists function
' -------------------------- ' DLL for #sq3 ' -------------------------- Open "SQ3_4_LB.dll" for DLL As #sq3 ' open SQ3_4_LB.dll calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long ' was sqlite3.dll loaded by SQ3_4_LB.dll msg$ = Winstring(result) ' LastMessage would be "General Error - Couldn't open 'sqlite3.dll'" if instr(msg$,"Error") then ' If we have an error notice msg$ : close #sq3 ' close sqlite and end program end end if
files "c:\LB5\", db$, info$() ' check if meta data exist If val(info$(0,0)) <> 0 then overwrite = 0 ' if there then open it gosub [connectDb] else overwrite = 1 ' if not there then make it x$ = createDb$(db$) if x$ <> "" then notice x$ wait end if end if
' ------------------------------------- ' See if db Table exist. ' If not create it ' ------------------------------------- sql$ = "SELECT * FROM sqlite_master WHERE tbl_name = 'xx'" gosub [sqlExec] print "error:";sqlExec$
if rows < 1 then sql$ = "CREATE TABLE xx (xxNum integer(5),xxDescr VARCHAR(10),xxCount integer(5))" gosub [sqlExec] print "error:";sqlExec$ sql$ = "CREATE UNIQUE INDEX xx_num ON xx(xxNum)" ' give it a unique index gosub [sqlExec] print "error:";sqlExec$ print "Table Created" end if
' re populate the table with new stuff sql$ = "DELETE FROM xx" ' clear previous data gosub [sqlExec] print "error:";sqlExec$
for i = 1 to 100 '-=-=-=-=-=-= sql$ = "INSERT INTO xx VALUES (";i;",'xx_";right$("000"+str$(i),3);"',0)" gosub [sqlExec] next i print "inserted 100 records" print "error:";sqlExec$
' ************ this causes it to lock ************* sql$ = "PRAGMA table_info(xx)" ' returns cid|name|type|notnull|dflt_value|pk print sql$ gosub [sqlExec] ' **************** comment this and it works print "error:";sqlExec$ ' ************ this causes it to lock ************* gosub [connectDb1] ' *********************** ' read the table 10 rows 10 time ' then do INSERT OR UPDATE to the last row read ' for pass = 1 to 10 sql$ = "SELECT * FROM xx WHERE rowid > ";pass;" ORDER BY xxNum LIMIT 1" gosub [sqlExec] print "Number of rows read:";rows;" error:";sqlExec$ xxNum$ = fldData$("xxNum") xxDescr$ = fldData$("xxDescr") xxCount$ = fldData$("xxCount") print xxNum$,xxDescr$,xxCount$
' ******************* sql$ = "UPDATE xx SET xxCount = xxCount + 5 WHERE xxNum = ";xxNum gosub [sqlExec1] print "Updated record error:";sqlExec$
' ***********************
print "** end of ** pass: ";pass ' INSERT OR REPLACE the last record read ' ' sql$ = "INSERT OR REPLACE INTO xx VALUES(";xxNum;",'";xxDescr$;"',";pass;")" print sql$ gosub [sqlExec] print "INSERT OR REPLACE error:";sqlExec$
next pass
' ----------------------------- ' Create database ' if exists it will open it ' if not it will create it ' ----------------------------- FUNCTION createDb$(db$) overwrite = 1 ' overwrite = 1 means existing file will be deleted calldll #sq3, "SQ3_4_LB_CreateDB",DB$ as ptr, overwrite as long, DBhnd as long If DBhnd = 0 then calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long createDb$ = Winstring(result) end if END FUNCTION
' ---------------------------------------- ' get field values from table ' ---------------------------------------- FUNCTION fldData$(field$) calldll #sq3, "SQ3_4_LB_GetRecordsetValueByName",field$ as ptr, result as long if result = 1 then fldData$ = Winstring(RS.StrAdr.struct) else calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long fldData$ = Winstring(result) end If end function
' -------------------------------------- ' Execute SQL command ' -------------------------------------- [sqlExec] calldll #sq3, "SQ3_4_LB_GetRecordset", sql$ as ptr,DBhnd as long,RS as struct, ret as long if ret = 0 then calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long sqlExec$ = Winstring(result) cols = 0 rows = 0 else cols = RS.Cols.struct rows = RS.Rows.struct sqlExec$ = "" end if RETURN
' -------------------------------- ' Go to next row in selection set ' -------------------------------- FUNCTION nextRow() calldll #sq3, "SQ3_4_LB_RecordsetMoveNext", result as void ' Next Row END FUNCTION
' -------------------------------------- ' Connect to Database ' -------------------------------------- [connectDb] calldll #sq3, "SQ3_4_LB_OpenDB",db$ as ptr, overwrite as long, DBhnd as long ' see if it exist If DBhnd = 0 then calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long connectdb$ = Winstring(result) notice connectdb$ end if RETURN
' -------------------------------------- ' Execute SQL command ' -------------------------------------- [sqlExec1] calldll #sq3, "SQ3_4_LB_GetRecordset", sql$ as ptr,DBhnd1 as long,RS as struct, ret as long if ret = 0 then calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long sqlExec$ = Winstring(result) cols = 0 rows = 0 else cols = RS.Cols.struct rows = RS.Rows.struct sqlExec$ = "" end if RETURN
' -------------------------------------- ' Connect to Database ' -------------------------------------- [connectDb1] calldll #sq3, "SQ3_4_LB_OpenDB",db$ as ptr, overwrite as long, DBhnd1 as long ' see if it exist If DBhnd1= 0 then calldll #sq3, "SQ3_4_LB_GetLastMessage", result as long connectdb$ = Winstring(result) notice connectdb$ end if RETURN
|
|
|
Post by Carl Gundel on Mar 11, 2019 8:25:06 GMT -5
Thanks for all that work Dan. I will pour over it and see if I can figure out why the Smalltalk wrapper behaves different from your LB4 example.
|
|
|
Post by meerkat on Mar 11, 2019 10:27:57 GMT -5
No problem.. We are all after the same thing.
The tricky one was having LB5 up and running and pasting new code in it and getting bad results. But if you brought LB5 up new and ran the same thing it would sometimes give you different results.
I know I've never ever got LB4 with the DLL to ever lock.. Not sure that means anything..
Good luck! Dan
|
|
|
Post by Rod on Mar 11, 2019 13:43:42 GMT -5
There has been .dlls that lock up in the past. Wavemix.dll for example. Most .dll's deal with very specific data structures and will fail instantly if you get it wrong. More complex .dll's that deal with command line statements might fail, some result in a runtime error some return a fail code you can deal with. Some just hang about in the background, opened by Liberty but never closed.
So it appears we have a runtime error that stops Liberty and leaves the resource hanging. IE the sql connect. If you fully close LB5 that connection is closed. If you attempt to rerun the program with continue or run it fails because the connect is still outstanding.
That's not an unexpected situation to me. We have seen similar in the past. If the LB5 program stalls and cannot properly close the connection and you don't close LB5 then the failed connection persists.
So its not so much that LB5 remembers the connection on restart, it has simply never forgotten or closed it. If a simple disconnect would work that would be great but LB probably never checks for hung connections on startup so new connections get new handles.
Old habits for this sort of stuff is to close LB5, fix/debug the code and restart avoiding the fail. Trying to persistently restart a failed program is not the correct approach. Does that make sense?
|
|
|
Post by meerkat on Mar 12, 2019 2:20:14 GMT -5
Just to make sure other systems did not have problems, I wrote the same test using RFO-BASIC on Android. There are some differences, but the functions are basically the same. The feasibility program used HTML <tables> instead of a grid. With the "lock test monster" test, I tried both multiple connects and single connects. I could not get any of the test to lock.
|
|