program : LB5 lock test monster.
This is the last post I will make on this subject.
Whoever is interested, may read it. If nobody is, never mind. No bad feelings.
At least it was my pleasure to go to the bottom of it.
In my previous posts I commented among other things the first version of the program (the two 'sqliteconnect' commands
and the 'pragma table_info' lock). What was on my to do list, was the 'WAL-file' version of the program with the two gosubs
[connectDb] and [connectDb1].
Here are the fixes that have to be applied to release the locks or supposed locks.
1) the pragma table_info section. This was the predictable lock.You could provoke it by unquoting the corresponding line.
But of course, you had to retrieve the corresponding data, because if you didn't do so, the database would complain.
So you can a) leave out the whole pragma table_info section, or
b) replace it with the following code:
' ************ this causes it to lock ---> PROBLEM SOLVED *************
sql$ = "PRAGMA table_info(xx)" ' returns cid|name|type|notnull|dflt_value|pk
print sql$
#sql1 execute(sql$) '*************** comment this and it works ---> PROBLEM SOLVED **********'
if #sql1 hasanswer() then
print #sql1 columnnames$()
for x = 1 to #sql1 rowcount()
result$ = #sql1 nextrow$(chr$(45))
cid$ = trim$(word$(result$,1,chr$(45)))
name$ = trim$(word$(result$,2,chr$(45)))
type$ = trim$(word$(result$,3,chr$(45)))
notnull$ = trim$(word$(result$,4,chr$(45)))
dfltValue$ = trim$(word$(result$,5,chr$(45)))
pk$ = trim$(word$(result$,6,chr$(45)))
print cid$;" ";name$;" ";type$;" ";notnull$;" ";dfltValue$;" ";pk$
next x
end if
2) The gosubs [connectDb] and [connectDb1].
This lssue was not easy to find. I can understand the logic of it. Others, perhaps, might complain about
the SQLite behaviour....
We are working with two threads : '#sql' and '#sql1' that have both their own sub program for the database
connection. Both connect to the same database.
Obviously, SQLite doesn't like the two series of identical pragma statements modifying the database (there are
perhaps security reasons like the synchronous pragma).
As soon as the pragma statements of the second sub program are encountered, there is a table lock
and the program falls at the first write attempt.
A bug ? In my opinion not really, because separately from the good reasons SQLite could have, it is also true
that one of the two series is completely redundant and, above all, easy to avoid.
Here is how the section could be re-written. It is possible to make it shorter but we would have to change
more lines in the program.
' -------------------------------------
' Connect to Database
' --------------------------------------
[connectDb] ' Connect to the DB
sqliteconnect #sql, db$ ' Connect to the DB
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
Once this correction made, the program falls again on the following error - another table lock.
3) There was a mismatch in the program between the handles #sql and #sql1.
In the third line of the 'Insert or replace' section the handle #sql must be replaced by: #sql1.
This was causing an error.
For more clarity the first two lines could also be changed: The section could be written as follows:
sql1$ = "INSERT OR REPLACE INTO xx VALUES(";xxNum;",'";xxDescr$;"',";pass;")"
print sql1$
#sql1 execute(sql1$) ' <----- fix : changed #sql to #sql1
N.B. I prefer to use #read and #write in order to avoid confusion.
4) At the end of the program, we should add the following missing line:
#sql1 disconnect()
This is not a bug fix. It didn't cause an error, but it is much better to close both handles at the end.
With these fixes the program should work just fine, after quoting the two sqliteconnect commands and unquoting
the two gosubs at the beginning of the program. In any case, it works for me
Update:
The pragma table_info:
It is true that LB 4.5.1 does not lock. The corresponding pragma data is there, it can be retrieved, but you don't have to do so.
The only thing I can conclude, is that the dedicated dll for LB 4.5.1 sends an kind of "acknowledgement of receipt" to the database even before the data is actually retrieved.
RB and LB 5-350 both need apparently to retrieve the data (what is best done by the hasanswer() method --> see the RB help file) before sending this confirmation....or to disconnect/reconnect, of course, but that is not the most elegant way to do it.