|
Post by Rod on Mar 7, 2019 13:12:09 GMT -5
Ok, so LB5 is using SQLite.dll directly? I just want to understand how we are interfacing with SQL to narrow the bug hunt.
|
|
|
Post by Rod on Mar 7, 2019 13:19:42 GMT -5
I would also suggest setting SQL. not aside, but in its correct place in the development queue. Till we have more time. I would far rather understand the basics like text and graphics. Sorry metro and meerkat.
|
|
|
Post by Carl Gundel on Mar 7, 2019 13:30:15 GMT -5
Ok, so LB5 is using SQLite.dll directly? I just want to understand how we are interfacing with SQL to narrow the bug hunt. It is using an SQLite3 wrapper library, but if I didn't use the one that comes with VisualWorks I would have to write my own, and that would also be a wrapper. I'm sure we will get to the bottom of this.
|
|
|
Post by meerkat on Mar 7, 2019 14:48:56 GMT -5
I guess this on one of those newbe questions.. Can someone tell me how to trap SQLite errors. Before the #sql execute(sql$) that does the update, I added this code: ------------- on error goto [sqlErr] and at [sqlErr] I disconnect and connect and try again. -------------
But it errors everything.. help... Dan
|
|
|
Post by metro on Mar 7, 2019 19:34:43 GMT -5
I would also suggest setting SQL. not aside, but in its correct place in the development queue. Till we have more time. I would far rather understand the basics like text and graphics. Sorry metro and meerkat. I completely understand if Carl puts this at the bottom of the list Hope you don't mind if I keep experimenting
|
|
|
Post by Carl Gundel on Mar 7, 2019 23:29:53 GMT -5
I would also suggest setting SQL. not aside, but in its correct place in the development queue. Till we have more time. I would far rather understand the basics like text and graphics. Sorry metro and meerkat. I completely understand if Carl puts this at the bottom of the list Hope you don't mind if I keep experimenting I'm digging into this. Hopefully this will be a simple fix.
|
|
|
Post by Rod on Mar 8, 2019 6:32:34 GMT -5
The simple database example is failing because DefaultDir$ is pointing at \bas_files which the .db is not in. Using an explicit path gets it working.
I also tried putting the .bas and .db in their own project folder. LB5 gets the correct directory info and finds the file in the project directory.
|
|
|
Post by meerkat on Mar 8, 2019 7:27:56 GMT -5
Metro, I'm with you. If there are other problems more important to fix in LB5, I can understand.
Do you still have a copy of the program ionSQL.bas written in LB4x. It uses the SQLite DLL. Basically it does the same thing the above program does. Using the [Browse] option you can list the info in a database table, with the option to Add, Change, or Delete.
Is it possible to use the exact same DB that you got the "database is locked" error in your above example, and select the same record and try to change it in the exact same way. Just wondering if you'd get the same error. So far I can't get that program to lock. If you do, then - we'd need to know if the lock is ever released. The problem in LB5 is that the lock is never released. If it does lock and it never releases, then LB5 should be good, and maybe we need to do something like override the "exclusive lock" Rod suggested. However I've tried that with no luck. I even tried BEGIN and END TRANSACTION - still no luck. But I'm going to try all that stuff again, hopefully one of them will work.
I tried all kinds of bad stuff with the LB4 program and it never has locked. But lets hope it does and it proves LB5 to be ok.
It's hard to believe that LB5 could have a problem since a lot of people have used smalltalk for years without any problems.
|
|
|
Post by metro on Mar 8, 2019 7:44:21 GMT -5
Yes I do have a copy Dan, I'll take a look at it in about 12 hours, far too much merlot consumed To look at it tonight It is Friday Afterall 😀
|
|
|
Post by Carl Gundel on Mar 8, 2019 8:18:10 GMT -5
It's hard to believe that LB5 could have a problem since a lot of people have used smalltalk for years without any problems. We need to figure this out, and there is no reason to think that we will not.
|
|
|
Post by meerkat on Mar 8, 2019 9:04:00 GMT -5
To look at it tonight It is Friday Afterall 😀 Very funny.. At least you wait till Friday.. My excuse is "It's gotta be 5:00 PM somewhere" I know this is a novice question. How do you trap SQLite errors. If I can trap the error then I could try to disconnect/connect and try again, or wait a second or something. Or is it even possible to trap it. I tried: ---------------------------------- on error goto [sqlError] #sql execute(sql$) ... [sqlError] do something --------------------------------------- Thanks for the help...
|
|
|
Post by Rod on Mar 8, 2019 12:03:23 GMT -5
Still trying to debug. If I run Dan's original code and mess it about I get one red herring wher Dan tries to close the grid control just ahead of closing the windows.
[mex] ' get outta here ' controls dont need closed individually 'close #wm.grid close #w.grid goto [nextPage] ' re show the record list
However more interestingly when I open Task Manager and watch what is happening I can clearly see the .db is open twice. It happens as we move from select a table to change the contents. Whatever is happening is not closing the original .db and is opening a duplicate .db
So the debugger is new to me and so too SQLite. But before we cry bug can we be absolutely sure that out SQL code is not opening the .db twice. That is what appears to be happening.
|
|
|
Post by meerkat on Mar 8, 2019 14:03:20 GMT -5
Thanks Rod.. Hmmm! Far as I know it shouldn't make any difference how many times you open it. As long as it's the same handle (#sql). If it were to connect again with a different handle, like #sql1, then it would be open twice. But who knows, maybe internally LB5 actually opens it again even though it has the same handle. It shouldn't. But maybe that's the problem. Maybe LB5, for whatever reason, actually opens it again internally. That could be a problem. Someone would have to check the internal working of LB5 to see.
Dan
|
|
|
Post by Rod on Mar 8, 2019 14:10:19 GMT -5
Or, don't open it twice? And what do you mean by the same handle? Opening a file name twice gets two handles and two instances of the same resource.
|
|
|
Post by meerkat on Mar 8, 2019 14:17:36 GMT -5
Here is a extreme example with 4 connects. If there were some way we could trap the lock, we could do something to get around it. But I don't know how to trap the SQL errors.
Here is a program that will lock it every time. But based on the code, it probably should. You can play with it and delete some of the connects to see where it begins to work. It creates a test.db file and does some nasty stuff to it:
' ------------------------------------ ' LB5 SQLite lock test with 4 cursors ' ------------------------------------ db$ = "test.db" sqliteconnect #sql1, db$ ' Connect to the DB sqliteconnect #sql2, db$ ' Connect to the DB sqliteconnect #sql3, db$ ' Connect to the DB sqliteconnect #sql4, db$ ' Connect to the DB
' ---------------------------------------------------------- ' if the DB don't exist - create it and populate it ' ---------------------------------------------------------- sql1$ = "SELECT * FROM sqlite_master WHERE tbl_name = 'tblA'" #sql1 execute(sql1$) rows = #sql1 ROWCOUNT() 'Get the number of rows
if rows = 0 then sql1$ = " CREATE TABLE tblA ( tblANum integer(5), tblADescr VARCHAR(10), tblACount integer(5))" #sql1 execute(sql1$)
for i = 1 to 100 #sql1 execute("INSERT INTO tblA VALUES (";i;",'tblA_";right$("000"+str$(i),3);"',0)") next i
#sql1 execute("CREATE UNIQUE INDEX tblA_num ON tblA(tblANum)")
print "Table A Created"
end if
sql1$ = "SELECT * FROM tblA LIMIT 10"
#sql1 execute(sql1$) rows = #sql1 ROWCOUNT() 'Get the number of rows
print rows for i = 1 to rows #row = #sql1 #nextrow() tblANum = #row tblANum() tblADescr$ = #row tblADescr$() tblACount = #row tblACount()
print tblANum;chr$(9);tblADescr$;chr$(9);tblACount next i
print "** End of Create **"
for pass = 1 to 3 print "Pass:";pass sql1$ = "SELECT * FROM tblA ORDER BY tblADescr" #sql1 execute(sql1$) while #sql1 hasanswer() #row = #sql1 #nextrow() tblANum = #row tblANum() tblADescr$ = #row tblADescr$() tblACount = #row tblACount()
sql2$ = "UPDATE tblA SET tblAcount = ";pass;" WHERE tblANum = ";i #sql2 execute(sql2$) sql3$ = "UPDATE tblA SET tblAcount = ";pass;" WHERE tblANum = ";i+1 #sql3 execute(sql3$) sql4$ = "UPDATE tblA SET tblAcount = ";pass;" WHERE tblANum = ";i+2 #sql4 execute(sql4$) ' ------------------------------------------------------------------ ' Find the largest count that was updated in the prev 2 sql commands ' ------------------------------------------------------------------ sql4$ = "SELECT * FROM tblA WHERE tblA.tblAcount = " _ + "(SELECT max(ta.tblAcount) FROM tblA as ta)" #sql4 execute(sql4$) wend next pass
print "** End of Update**" #sql1 disconnect() #sql2 disconnect() #sql3 disconnect() #sql4 disconnect() end
|
|