Feasibility of grid for SQL file maintenance.

[Rod]

Just to be clear. It might feel like I am keeping the wolves at bay. But I am not. I feel like I have engaged with two keen supporters of Liberty. So don't take too much slight from my knockbacks. We could both be wrong. I just don't want to send Carl down a tunnel when we are desperately needing clarity and documentation on functionality at a much more basic level than grids and sql.

[Rod]

Sorry, but I think that is proving that SQL is driving the problem. Cant we anticipate the lock? why even try four connects to the same database? what is the logic in that.


From all my recent reading SQL locking is NOT a simple "managed" process. There are pages and pages of file locked errors on google.


So the first thing we need to do is simplify and make one connect that does not lock. When we are sure of that lets try two.

[meerkat]

Rod..
No.. I've had no problems with anything. Like you say, we all want the same thing.
And if it's the code I wrote, I have no problem admitting the problem. It's all about getting the stuff to work..

BTW.. The above program with the 4 connects.. I'll write it in LB4x with the DLL and see what happens..

Dan

[Rod]

Someone would have to check the internal working of LB5 to see.

I did, it does open the .db twice

Don't bother going back to Liberty BASIC 4.5.1 or any other .dll. If we cant make LB5 and its SQLite wrapper work there is little point in continuing with SQL.


We don't want multiple iterations of SQL interface, lets make one work. The solution may be simple.


However googling for an hour or so makes me think Carl might struggle to solve this because many have failed in the past. What we need is an SQL expert to take an interest for a day or so.

[Carl Gundel]

Mar 8, 2019 14:33:28 GMT -5 Rod said:

Someone would have to check the internal working of LB5 to see.

I did, it does open the .db twice

How did you check that?

[Rod]

Task. Manager, it shows we are opening the .db twice. Whether through bad SQL code or bad LB5 Library calls I know not. But two copies of the same file = lock.

[Rod]

Ok refreshed and clear headed I can see that the simple database.bas example does not hold open the .db It never appears in the task manager list at any point.

The feasibility code on the other hand holds open two instances of the .db that are listed in task manager.

So perhaps we have a fundamental misunderstanding of how to use SQL in LB5 I don't have time today but I will start to better understand Dan's code and see what he does differently from the simple database example.


Edit to add, I can already see that the simple database example opens and closes the connection on every use. Dan's is probably holding it open. Need more time.

[meerkat]

The major difference is the connection.
In the simple db it connects, does the SQL command, and disconnects.
In the feasibility code it does one connect and keeps it throughout the entire program.
I hope it's only one. However from what I understand, the connection and handle are common.
So if it did connect twice, it uses the same handle #sql and should only still have one connection. But I could be wrong about this.
You have to remember, this code was a quick write to do a quick test of grid, not the SQLite. If it were a production program I would have checked further. It just kinda got labeled with the problem. But that's ok, it does give us some guidelines on what is good and bad.

The program could easily be modified with a subroutine for all the #sql execute(sql$) commands to connect, do the sql, and disconnect. If that's the rule for RB5 then I can accept that.

But there are problems. I have systems where I have the database opened several times and need to stay open to maintain the cursors with multiple accesses to a table, and multiple updates. But one problem at a time..

[metro]

At the risk of confirming my limited competence does the task manager simply show open windows.
therefore what is seen is simply two windows with the same name ie
open "DB:";db$;" Table:";tblName$ for window as #w
open "DB:";db$;" Table:";tblName$ for window as #wm

happy to get a lesson here(maybe), I certainly feel vulnerable to criticism at this point.

[BeeTrap]

Metro, no criticism from me, ask away! Rod, I appreciate your knowledge and willingness to help out! Please continue with ALL of these "back and forth" discussions. Surely I am not the only one that reads 99% of all new posts. By watching, from the back of the bus, I can learn from what all of you "drivers" are doing. I don't have a real need for SQL at this time, but do feel a Movie database program for my Wife is in my future. So ask away, that way I don't have to interrupt with my questions. Rather often someone knowledgeable will give me that perfect piece of code that will make my day. Thanks!

[Rod]

I had a little browse today but did not get very far understanding why Dan's code throws an error. Both Carl and Dan write code that holds the database connection and generally work well. Carl manages to write new data by zipping in and out of a connection in his sub. Dan appears to try to write while the database is connected and also selected. I think Dan is opening, selecting, attempting to write and disconnecting. But we never get there because of the file lock. So perhaps it is the select that is the problem?


A much simpler example of the problem would help. I don't think it is anything to do with the grid which is just a display vehicle so we should drop that part out for now.

[Rod]

Taking the example Carl provides that works and inserting a new record while it is held open and selected * in the later half of the code works to. So it isn't simply select.

I use full paths in this code which you will need to amend. It writes the two rows and a third row and will repeat.


 files #accessor, "c:\atemp\Records.db"
 exists = #accessor hasAnswer()
 if exists = 0 then
   call sqlExec "create table Payables (company char(50), invoice char(12), due integer, amt char(10))"
print "Table Payables created."
   input "Press Enter to continue."; a$
 end if

 call sqlExec "insert into Payables (company, invoice, due, amt) values (""IBM"", ""1029A"", 38945, ""19.95"")"
 call sqlExec "insert into Payables (company, invoice, due, amt) values (""Commodore"", ""VIC-20"", 84545, ""299.95"")"
 print "Two rows added."
 input "Press Enter to continue."; a$

 sqliteconnect #records, "c:\atemp\Records.db"
 query$ = "select * from Payables"
 #records execute(query$)
 if #records hasanswer() then
   for x = 1 to #records rowcount()
     result$ = #records nextrow$("|")
print result$
   next x
 end if
 call sqlExec "insert into Payables (company, invoice, due, amt) values (""IBM"", ""1029A"", 38945, ""19.95"")"
 #records disconnect()
 print "Two rows retrieved."
 print "Done."
 end

sub sqlExec query$
   sqliteconnect #records, "c:\atemp\Records.db"
   #records execute(query$)
   #records disconnect()
end sub

[metro]

Its 3am here I can't help feeling déjà vu
Rod do you have time to attempt reading (selecting) a record then modify and write back with insert or replace within the same thread. That is there is no disconnect.
I'm wondering if simultaneously reading and writing is causing the error.

Maybe for write backs we need to disconnect and reconnect.

Just a thought...

[metro]

Mar 9, 2019 11:08:21 GMT -5 Rod said:

A much simpler example of the problem would help. I don't think it is anything to do with the grid which is just a display vehicle so we should drop that part out for now.

I agree, I spent 30 minutes this morning re-hashing Dan's code to introduce subs to connect and disconnect to the db.

I'm short on time today (building a granny flat) will get back to it tonight.

It maybe simpler for me to modify Rods code and split the sub sqlExec query$

into two for connect and disconnect.

I'm pretty sure multiple reads are possible but I suspect writing back a changed record immediately after a read

without a disconnect first maybe the issue.

but I'm a long way short of being an expert on this.(looonnnggg....way)

Thanks for persevering with this Rod.

[metro]

With Dan's code I can now Chg a record without throwing an error (lock)
I disconnect and re-connect prior to saving the changes
I have introduced a couple of bugs in the code which affect other functioning which is now work in progress
it's easier when it's your own code though.
So it would seem you can have as many reads as you like with the one sqliteconnect

BUT you need to close and re-open to make changes because (I believe) the db is locked for the reads

' -------------------------------------------------
' Supply database and table name
' -------------------------------------------------
[getDb]
filedialog "Find a Database File", "*.db", db$
if db$ = "" then
notice "No file selected. Thank you and have a great day!"
end
end if
' --------------------------------
' see if the file is sqlite format
' --------------------------------
open db$ for input as #1
a$    = lower$(input$(#1,15))
close #1
if left$(a$,13) <> "sqlite format" then ' check file format
notice db$;" ** Not SQLite file format **";a$
goto [getDb]
end if
' ----------------------------------------
' get tabel names from DB for selection
' ----------------------------------------
sqliteconnect    #sql,  db$  ' Connect to the DB
sql$ = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
#sql execute(sql$)
rows = #sql ROWCOUNT() 'Get the number of rows
dim tblList$(rows)
for i = 1 to rows
#row = #sql #nextrow()
tblList$(i) = #row name$()
next i
[getTbl]
combobox #tbl.list, tblList$(), [tblSel], 5, 35, 200, 50+rows*8
button  #tbl.ex "Exit",     [tblEx], UR, 15, 5
WindowWidth = 270 : WindowHeight = 100 + rows*12
open "Select a Table" for window as #tbl
#tbl trapclose("[tblEx]")

wait
[tblSel]
tblName$ = #tbl.list selection$() ' what was selected
print "tblName:";tblName$
if tblName$ = "" then
notice "Table selected is MT"
'goto [getTbl]

end if

'wait
[tblEx]
if tblName$ = "" then
#sql disconnect()
end
end if
'close #tbl
' ---------------------------------
' How many records are in the table
' ---------------------------------
sql$   = "SELECT count(*) as numRecords FROM ";tblName$
#sql execute(sql$)
#row   = #sql #nextrow()
numRecords = #row numRecords()
' -----------------------------------------
' names and number of columns
' -----------------------------------------
sql$ = "SELECT * FROM ";tblName$;" LIMIT 1"
#sql execute(sql$)
colNames$ = #sql columnnames$()
j = 0
for numCols = 1 to len(colNames$)
j = instr(colNames$,",",j+1)
if j = 0 then exit for
next numCols
' ------- this also gets the type and sizes
sql$ = "PRAGMA table_info("+tblName$+")" ' returns cid|name|type|notnull|dflt_value|pk
#sql execute(sql$)
numFlds = #sql ROWCOUNT()
dim fldName$(numFlds)
dim fldType$(numFlds)
dim fldSize$(numFlds)
dim fldDecm$(numFlds)
for i = 1 to numFlds
result$ = #sql nextrow$(" |") ' use nextrow because data has underline (dflt_value) in it
fldName$(i) = word$(result$,2," |")
a$ = word$(result$,3," |") + "( )"
fldType$(i) = word$(a$,1,"(")
a$ = word$(a$,2,"(")
a$ = word$(a$,1,")")
if instr(a$,",") then ' see if it has decimals
fldSize$(i) = word$(a$,1,",")
fldDecm$(i) = word$(a$,2,",")
else
fldSize$(i) = a$
end if
next i
' --------------------------------------------------------
' get max widths of data in each column
' and force it between 2 and 12 characters
' --------------------------------------------------------
x$ = strRep$(colNames$,",",")),max(length(")
sql$ = "SELECT max(length("+x$+")) FROM ";tblName$;" LIMIT 1"
#sql execute(sql$)
rows = #sql ROWCOUNT() 'Get the number of rows
if rows < 1 then
notice "No data in table:";tblName$
goto [getTbl]
end
end if
result$ = #sql nextrow$(",")
dim colWid(numCols)
colWid$ = "40,40"
cma$ = ","
totWid = 80
for i = 1 to numCols
colWid(i) = min(12,max(2,val(word$(result$,i,",")))) ' width between 2 and 12
colWid$ = colWid$ + cma$ + str$(colWid(i)* 10) ' adjust for pixels
totWid = totWid + colWid(i) * 10
'cma$ = ","
next i
' ----- set some start stuff -----------
pageNum = 1 ' start with page one
lpp = 20 ' num of lines per page
limit$ = " LIMIT ";lpp ' limit for sql
' ---------------------------------------
' sho a page of table data
' ---------------------------------------
[nextPage]

dim a$(numFlds+2, lpp)
print "141 dim a(:";numFlds+2;" ";lpp
sql$ = "SELECT rowid,* FROM ";tblName$;" ORDER BY ";tblName$;".rowid ";limit$
#sql execute(sql$)
rows = #sql ROWCOUNT() 'Get the number of rows
for i = 1 to rows
a$(0,i-1) = "ACD"
result$ = #sql nextrow$(" ";chr$(251))
for j = 1 to numCols
x$ = left$(trim$(word$(result$,j,chr$(251))),12)
a$(j,i-1) = x$
next j
next i
grid #w.grid, a$(), [clicked], 120, 10, 100+totWid, 100+rows*17.5 ' create a grid widget
' ----------sho page options --------------------------           x   y   w   h
statictext #w.pgeHd, "_________Page_________" ,000,030,180,020
button #w.pre, "Prev", [pre]   , UL ,000,060,030,020
button #w.nxt, "Next", [nxt] , UL ,080,060,030,020
button #w.ext, "EXIT", [ext] , UL ,040,090,030,020
textbox #w.pge             ,040,058,030,020
statictext #w.rec, "Records:";numRecords ,002,110,100,020
open "DB:";db$;"    Table:";tblName$ for window as #w ' open the grid
#w  trapclose("[tblEx]")
a = eval("#w.grid columnwidths("+colWid$+")") ' set grid col widths
print "166:";colWid$
#w.pge pageNum ' sho current page
q$ = chr$(34)
cma$ = ","
cn$ = """Mnt"",""ID""" ' column names for grid
for i = 1 to numCols ' set length of colNames and center then
cn$ = cn$ + cma$ + q$ + align$(fldName$(i),colWid(i),"c") + q$
'cma$ = ","
next i
print "176:";cn$
'input "xxx";x
a = eval("#w.grid columnnames("+cn$+")") ' put column names on the grid '
a = eval("#w.grid rownames("+rn$+")") 'label the rows
#w.grid rowlabelwidth(40) 'set sizes for rows

wait
[clicked]
xy$ = #w.grid cellxy$()
val$ = #w.grid value$() 'contents of cell clicked
print "xy:";xy$
print "val:";val$
c = val(word$(xy$,1))
if c <> 1 then wait
' ===================================================
' Record Maintenance
' ===================================================
r = val(word$(xy$,2))
#w.grid selectxy(2,r)
rowid$ = #w.grid value$() 'contents of cell clicked
dim m$(1,numFlds)
sql$ = "SELECT * FROM ";tblName$;" WHERE rowid = ";rowid$
#sql execute(sql$)
rows = #sql ROWCOUNT() 'Get the number of rows
if rows < 1 then
notice "Record not on file"
wait
end if
' ----- put data in grid array --------
result$ = #sql nextrow$(" ";chr$(251))
dim fldVal$(numFlds)
for i = 1 to numFlds
fldVal$(i) = trim$(word$(result$,i,chr$(251)))
m$(0,i -1) = fldVal$(i)
next i
grid #wm.grid, m$(), [clkMnt], 0, 10, 300, 100+numFlds*20 ' create a grid widget
' ----------sho Add Change Delete and Exit buttons ----           x   y   w   h
button #wm.add, "Add", [add]   , UL ,010,060,030,020
button #wm.chg, "Chg", [chg] , UL ,010,090,030,020
button #wm.del, "Del", [del] , UL ,010,120,030,020
button #wm.ext, "EXIT", [mex] , UL ,010,150,030,020
open "DB:";db$;"    Table:";tblName$ for window as #wm ' open the grid
#wm trapclose("[tblEx]")
#wm.grid columnnames( "Value" )
#wm.grid columnwidths( 100 )
' ----- maintenance row names"
cma$ = ""
rmn$ = "" ' row names for grid
for i = 1 to numFlds ' set length of colNames and center then
rmn$ = rmn$ + cma$ + q$ + align$(fldName$(i),12,"r") + q$
cma$ = ","
next i
a = eval("#wm.grid rownames("+rmn$+")") 'label the rows
#wm.grid rowlabelwidth(200) 'set sizes for rows



wait
' ------------ Clicked maintenance -------
[clkMnt]
xy$ = #wm.grid cellxy$()
val$ = #wm.grid value$() 'contents of cell clicked
x = val(word$(xy$,1))
y = val(word$(xy$,2))
p$ = "Field: ";fldName$(y);" val: ";val$
prompt p$ ; val$
#wm.grid setvalue( val$ )
fldVal$(y) = val$
wait
' --------------------------------
' add a new record
' --------------------------------
[add]
val$ = ""
f$ = ""
goto [acd]
' --------------------------------
' change the record
' --------------------------------
[chg]
call  sqlDisconnect
sqliteconnect    #sql,  db$  ' Connect to the DB
sep$ = ""
f$ = " (rowid,"
for i = 1 to numFlds
f$ = f$ + sep$ + fldName$(i)
sep$ = ","
next i
f$ = f$ + ")"
val$ = rowid$ + "','"
[acd]
sep$ = ""
for i = 1 to numFlds
val$ = val$ + sep$ + fldVal$(i)
sep$ = "','"
next i
sql$ = "INSERT OR REPLACE INTO ";tblName$;f$;" VALUES('";val$;"')"
print sql$
#sql execute(sql$)
goto [mex]
' -------------------------------
' Delete a record
' -------------------------------
[del]
sql$ = "DELETE FROM ";tblName$;" WHERE rowid = ";rowid$
print sql$
#sql execute(sql$)
[mex] ' get outta here
'close #wm.grid
'close #w.grid
'goto
call  sqlDisconnect
'sqliteconnect    #sql,  db$  ' Connect to the DB ' re show the record list
wait
' ------- exit------------------
[ext]
close #w
#sql disconnect()
end
' ---------------------------------
' Previous Next or User Page Number
' ----------------------------------
[pre]
ap = -1
goto [pageIt]
[nxt]
ap = 1
[pageIt]
prePage = pageNum
pageNum = pageNum + ap
totPages = int(numRecords / lpp)
#w.pge "!contents? goPageNum$"
if goPageNum$ <> "" then
goPageNum = int(val(goPageNum$))
if goPageNum <> prePage then
if goPageNum > totPages or goPageNum < 1 then
notice "Page must be between 1 and ";totPages
wait
end if
pageNum = goPageNum
end if
end if
pageNum  = max(1,pageNum) ' make suer it has a page number
if lpp < 1 then lpp = 30 ' lines per page must be specified
lpp = max(5,lpp)     ' make sure it has a least 5 lines per page
lpp = min(60,lpp)     ' don not allow over 60 lines per page
if lpp * totPages <> numRecords then totPages = totPages + 1
pageNum = min(totPages,pageNum)
pageNum = max(1,pageNum)
limitBeg = (pageNum * lpp) - lpp  'limit begin value
limit$ = " LIMIT " ; limitBeg ; "," ; lpp
close #w
goto [nextPage]
' ---------------------------------------
' string replace rep str with
' ---------------------------------------
function strRep$(str$,rep$,with$)
ln  = len(rep$)
ln1 = ln - 1
i = 1
while i <= len(str$)
if mid$(str$,i,ln) = rep$ then
strRep$ = strRep$ + with$
i = i + ln1
else
strRep$ = strRep$ + mid$(str$,i,1)
end if
i = i + 1
wend
end function
' -------------------------------------
' Align fld$ to left right or center
'  of a given width
' -------------------------------------
function align$(fld$,width,lrc$)
s    =  width - len(fld$)
fld$ = left$(fld$,width)
if s < 1 then
align$ = fld$
else
b$ = space$(width)
if lrc$ = "l" then align$ = fld$;left$(b$,s)
if lrc$ = "r" then align$ = left$(b$,s);fld$
if lrc$ = "c" then align$ = left$(b$,int(s / 2));fld$;left$(b$,int(s / 2) + (s and 1))
end if
end function
'untitled.bas


sub sqlDisconnect
   #sql disconnect()
end sub