database to grid

[meerkat]

Here is my attempt to display database information in a grid.
Given a database and table it displays the information in a grid.
It tries to size the grid columns based on the maximum size of information in the given table rows.
Being a newbe, this is a little crude. But you can browse from page to page. I tried it on a small table with about 25 columns and 70K rows. 
Still having problems sizing the grid and placement of the grid?? Maybe someone knows how to do that.



' -------------------------------------------------
' 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
listbox #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
wait

[tblSel]
tblName$ = #tbl.list selection$() ' what was selected

[tblEx]
close #tbl
if tblName$ = "" then
#sql disconnect()
end
end if

' ---------------------------------
' 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

' --------------------------------------------------------
' 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$ = ""
cma$ = ""
totWid = 0
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$(numCols, lpp)
sql$ = "SELECT * FROM ";tblName$;" ORDER BY ";tblName$;".rowid ";limit$

#sql execute(sql$)
rows = #sql ROWCOUNT() 'Get the number of rows
for i = 1 to rows
result$ = #sql nextrow$(" ";chr$(251))
for j = 1 to numCols
x$ = left$(trim$(word$(result$,j,chr$(251))),12)
a$(j - 1,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 dialog as #w ' open the grid
a = eval("#w.grid columnwidths("+colWid$+")") ' set grid col widths

#w.pge pageNum ' sho current page

q$ = chr$(34)
cma$ = ""
cn$ = "" ' column names for grid
for i = 1 to numCols ' set length of colNames and center then
cn$ = cn$ + cma$ + q$ + align$(word$(colNames$,i,","),colWid(i),"c") + q$
cma$ = ","
next i
a = eval("#w.grid columnnames("+cn$+")") ' put column names on the grid '

cma$ = ""
rn$ = "" ' row names for grid
for i = 1 to numRows ' set length of colNames and center then
rn$ = rn$ + cma$ + q$ + "Rec_" + right$("00" + str$(i),2) + q$
cma$ = ","
next i

a = eval("#w.grid rownames("+rn$+")") 'label the rows
#w.grid rowlabelwidth(40) 'set sizes for rows
wait

[clicked]
print "cell contents: ";#w.grid value$() 'contents of cell clicked
'#w.grid  "reload"
wait

' -------------------------
[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

[Rod]

It is early days and we still have a lot to learn about the grid command. I just wonder if we should adopt the SQLite sample database for use in our testing. It is available for down load here


www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip



It would mean we could all run any demo code posted.

[Rod]

Couple of quick thoughts. I am not sure you have the window as a control container concept just yet. You would need to set WindowWidth and WindowHeight to something useful say the calculated width and height of your table prior to opening the container window. In this case you have


open "DB:";db$;" Table:";tblName$ for dialog as #w ' open the grid

This is actually opening the window that will contain the grid so just before that set WindowWidth and WindowHeight

Now instead of closing the whole thing down why not simply replace the contents of the grid? The size and structure of the grid and the labels wont change all you need change are the contents of each cell using your pagecounter as the starting point. You would need code to blank out unused cells at the tail end.

Impressive so far.