LB5 SQLite Program Generator

[meerkat]

I was writing similar code over and over to maintain, search, and sort SQLite records in LB5.
So wrote a program to do it.
I thought it may be useful for others, so decided to post it.
The header of the program has some docs on what the generated code does.

Hope it helps.
Dan

'lb5 SQLite code generator
' Lists data with 30 rows per page
' Can Add,Change, and Delete records
' Sort any combination of columns including descending
' Search any combination of columns including wild card
' Create CSV based on Sort and Search
'How To Sort
' 1. Clear [Clr] the record detail
' 2. Enter sequence numbers in the fields you want sorted.
'    Follow sequence with a 'd' for descending sort
'    Follow sequence with a 'n' to force a numeric sort
'    For a 3 column sort enter
'           1 in the first field
'           2 in the second field
'           3 in the third field
'     if you want the second column to be descending enter 2d
' 3. click [Sort] button
'How to Search
' 1. Clear [Clr] the record detail
' 2. Enter search for data in the columns you want to search
'    search field for 'x' enter 'x' in that field
'    search field that begins with 'x' enter 'x*' in that field
'    search field that ends with 'x' enter '*x' in that field
'    search field that contains 'x' enter '*x*' in that field
' 3. click [Srch] button
' --------------------------------
' Load Database
' --------------------------------
[loadDb]
filedialog "Find a Database File", "*.db", db$
if db$ = "" then
notice "No file selected.";crlf$;"Thank you and have a great day!"
wait
end if
open db$ for input as #1
a$    = "col"
if lof(#1) > 12 then
a$    = input$(#1,13)
a$    = lower$(a$)
end if
close #1
if left$(a$,13) <> "sqlite format" then ' check file format
notice db$;" ** Not SQLite file format **"
wait
end if

sqliteconnect #sql,  db$ ' Connect to DB
gosub [getTblInfo] ' get database tables

WindowWidth   = 400
WindowHeight  = 450
UpperLeftX    = 200
UpperLeftY    = 200
nomainwin
'                                                       col row wid hi
Statictext    #main.sDb, "Database: ";db$, 010,040,200,024
Statictext    #main.sTbl, "Table", 010,070,170,024
textbox       #main.tTbl, 010,080,130,040
grid      #main.gTbl, tblGrid$(), [useTbl], 010,115,180,224

button        #main.bGen, "Gen", [genProg], UL, 010,350,060,025
button      #main.bex, "Exit", [exit], UL, 085,350,060,025

textbox       #main.tout, 010,380,370,040

open "Program Generator" for window as #main
#main "Font Arial 8 Bold"
'---- grid tbl
#main.gTbl columnwidths(110)
#main.gTbl columnnames("Table")
#main.gTbl rowlabelwidth(0)
wait

[useTbl]
xy$ = #main.gTbl cellxy$()
col$ = word$(xy$,1," ")
row$ = word$(xy$,2," ")
col = val(col$)
row = val(row$)
row = row - 1
useTblRow = row
tblName$ = tblGrid$(0,row)
#main.tTbl tblName$
wait

[exit]
close #main
end

' ----------------------------
' get tables in database
' ----------------------------
[getTblInfo]
sql$  = "SELECT  name
FROM    sqlite_master
WHERE   type = 'table'
ORDER BY name"
#sql execute(sql$)
rows = #sql ROWCOUNT()   'Get the number of rows
numTbls = rows
dim tblGrid$(1,numTbls)
for i = 0 to numTbls -1
#row = #sql #nextrow()
tblGrid$(0,i)    = #row name$()
next i
RETURN

' ----------------------------
' get fields in table
' ----------------------------
[getFldInfo]
sql$ = "PRAGMA table_info("+tblName$+")" ' returns cid|name|type|notnull|dflt_value|pk
#sql execute(sql$)
numFlds = #sql ROWCOUNT()
if numFlds < 1 then
notice "No Field Info"
goto [getFldInfoEx]
end if
dim fldNames$(numFlds)
dim fldTypes$(numFlds)
dim fldSizes$(numFlds)
dim fldDecms$(numFlds)
for i = 1 to numFlds
result$ = #sql nextrow$(" |") ' use nextrow because data has underline (dflt_value) in it
fldNames$(i) = word$(result$,2," |") ' field name
a$ = word$(result$,3," |") + "( )"
fldTypes$(i) = word$(a$,1,"(") ' field type
a$ = word$(a$,2,"(")
a$ = word$(a$,1,")")
if instr(a$,",") then ' see if it has decimals
fldSizes$(i) = word$(a$,1,",") ' field size
fldDecms$(i) = word$(a$,2,",") ' decimals
else
fldSizes$(i) = a$
fldDecms$(i) = ""
end if
next i
[getFldInfoEx]
RETURN

' ==========================================
' Program Generator
' ==========================================
[genProg]
if tblName$ = "" then
notice "Please select a table"
wait
end if

' -----------------------------------------
' names and number of columns
' -----------------------------------------
gosub [getFldInfo]

sql$ = "SELECT * FROM ";tblName$;" LIMIT 1"
#sql execute(sql$)
colNames$ = #sql columnnames$()
colNames$ = strRep$(colNames$," ","")
cn$ = strRep$(colNames$,",",")),max(length(")
sql$ = "SELECT max(length(";cn$;")) from ";tblName$
#sql execute(sql$)
cs$ = #sql nextrow$(chr$(215))
maxLblWid = 0 ' get max field sizes of data
totWid = 0
dim lblWid(numFlds)
for i = 1 to numFlds
s = len(fldNames$(i))
lblWid(i) = s
maxLblWid = max(s,maxLblWid)
s = val(fldSizes$(i))
s = min(40,s) ' no more than 40 characters on list
lblWid(i) = max(lblWid(i),s)
totWid = totWid + lblWid(i) ' total width of grid
next i

' --------------------------
' get detail field sizes
' --------------------------
dim fldSiz(numFlds)
maxFldSiz = 0
ft$ = ""
for i = 1 to numFlds
s = val(fldSizes$(i))
s1 = val(word$(cs$,i,chr$(215))) ' actual size of data
s1 = min(40,s1)
s = max(s,s1)
s = min(s,40) ' only allow a max of 40 characters
s = max(s,5) '  and a minimum of 5 characters
if fldTypes$(i) = "TEXT" then s = 40
if fldTypes$(i) = "DATE" then s = 10
if fldTypes$(i) = "TIME" then s = 10
fldSiz(i) = s
maxFldSiz = max(maxFldSiz,s)
ft$ = ft$ + fldTypes$(i) + ","
next i
gridCol = maxLblWid * 10 + maxFldSiz * 10 + 30
genFile$ = DefaultDir$;tblName$;".bas"
#main.tout "OUTPUT: ";genFile$
OPEN genFile$ for output as #f

print #f, "numFlds = ";numFlds;" ' number of columns"
print #f, "tblName$ = """;tblName$;""""
print #f, "dim  gridData$(numFlds + 1,lpp)"
print #f, "dim srt$(numFlds) ' sort selections"
print #f, "fldNames$ = """;colNames$;""""
print #f, "fldTypes$ = """;ft$;""""
print #f, "WindowWidth = 1600"
print #f, "WindowHeight = 800"
print #f, "UpperLeftX = (DisplayWidth-WindowWidth) / 2"
print #f, "UpperLeftY = (DisplayHeight-WindowHeight) / 2"
print #f, "nomainwin"
print #f, "' col row wid hig"
print #f, "button     #w.BTadd, ""Add"", [add], UL,   010,020,040,020"
print #f, "button     #w.BTchg, ""Chg"", [chg], UL,   055,020,040,020"
print #f, "button     #w.BTdel, ""Del"", [del], UL,   100,020,040,020"
print #f, "button     #w.BTclr, ""Clr"", [clr], UL,   145,020,040,020"
print #f, "button     #w.BTcsv, ""Csv"", [csv], UL,   190,020,040,020"
print #f, "button     #w.BText, ""Exit"", [exit], UL,   240,020,040,020"
print #f, "button     #w.BTpre, ""Prev"", [pre], UL, 320,020,040,020"
print #f, "textbox    #w.pge, 360,016,040,020"
print #f, "button     #w.BTnxt, ""Next"", [nxt], UL,     400,020,040,020"
print #f, "statictext #w.STrec, ""Records: 0"", 460,020,300,020"
print #f, "statictext #w.STtbl, ""Table:";tblName$;""", 710,020,200,020"

print #f, "button     #w.BTsch, ""Srch"", [srch], UL,   010,040,040,020"
print #f, "statictext #w.STsch, ""Search none"", 050,040,900,020"
print #f, "button     #w.BTsrt, ""Sort"", [sort], UL,   010,060,040,020"
print #f, "statictext #w.STsrt, ""Sort order "", 050,060,900,020"

print #f, "statictext #w.gdHdr, ""Database:";db$;""", 460,005,";max(totWid,800);",20"


s = totWid * 10
s = min(1500,s)
x$ =      "grid       #w.grid, gridData$(),   [gridData], ";gridCol;",080,";s;",520"
print #f, x$

' --------------------------------------
' set up fields with caption
' --------------------------------------
row = 80
n = 0
col$ = right$("000";str$(maxLblWid * 10 + 20),3)
for i = 1 to numFlds
row$ = right$("000";str$(row),3)
wid$ = right$("000";maxLblWid * 10,3)
print #f, "statictext #w.ST";fldNames$(i);", """;align$(fldNames$(i),maxLblWid,"r");""", 010,";row$;",";wid$;",020"
wid$ = right$("000";fldSiz(i) * 10,3)
if fldTypes$(i) = "TEXT" then
x$ = "texteditor #w.TB";fldNames$(i);", ";col$;",";row$;",";wid$;",080"
row = row + 80
else
x$ = "textbox    #w.TB";fldNames$(i);", ";col$;",";row$;",";wid$;",020"
row = row + 20
end if

print #f, x$

next i

' -------------------------------------------------
' open the window with grid
' set up font
' -------------------------------------------------
x$ = "open ";chr$(34);tblName$;chr$(34);" for window as #w"
print #f, x$

cw$ = "70,"
cn$ = ""
cma$ = ""
sep$ = chr$(34);"rowid";chr$(34);",";chr$(34)

for i = 1 to numFlds
fn$ = left$(fldNames$(i),lblWid(i))
cn$ = cn$ + sep$ + align$(fn$,lblWid(i),"c")
cw$ = cw$ + cma$ + str$(min(lblWid(i),16) * 10)
sep$ = chr$(34);",";chr$(34)
cma$ = ","
next i

x$ = "#w.grid columnnames(";cn$;chr$(34);")"
print #f, x$
x$ = "#w.grid columnwidths(";cw$;")"
print #f, x$

x$ = "print #w, ""font Courier New 10 bold"""
print #f, x$
print #f, ""
print #f, "' ----- set some start stuff and get number of records -----------"
print #f, "pageNum = 0 ' start with page one"
print #f, "lpp = 30 ' num of lines per page"
print #f, "sqliteconnect    #sql, """;db$;"""  ' Connect to the DB"
print #f, "gosub [numRecs]"
print #f, "goto [nxt]"

print #f, ""
print #f, "' -------------------------"
print #f, "' delete record "
print #f, "' -------------------------"
print #f, "[del]"
print #f, "confirm ""Are you sure you want to delete rowid:""+rowid$; answer$"
print #f, "if answer$ = ""no"" then wait "
print #f, "sql$ = ""DELETE FROM ";tblName$;" WHERE rowid = "";rowid$"
print #f, "#sql execute(sql$)"
print #f, "ap = 0"
print #f, "goto [pageIt]"

print #f, ""
print #f, "' -------------------------"
print #f, "' add record "
print #f, "' -------------------------"
print #f, "[add]"
print #f, "gosub [getData]"
print #f, "ff$ = """""
print #f, "vv$ = """""
print #f, "cma$ = """""
print #f, "sep$ = """""
print #f, "for i = 1 to numFlds"
print #f, " f$ = word$(flds$,i,chr$(251))"
print #f, " v$ = word$(vals$,i,chr$(251))"
print #f, " if v$ = chr$(251) then v$ = """" "
print #f, "ff$ = ff$ + cma$ + f$ "
print #f, "vv$ = vv$ + sep$ + v$ "
print #f, "cma$ = "","" "
print #f, "sep$ = ""','"" "
print #f, "next i"
print #f, "sql$ = ""INSERT INTO ";tblName$;" ("";ff$;"") VALUES ('"";vv$;""')"" "
print #f, "on error goto [gotError]"
print #f, "#sql execute(sql$)"
print #f, "gosub [numRecs]"
print #f, "ap = 0"
print #f, "goto [pageIt]"

print #f, ""
print #f, "' -------------------------"
print #f, "' Error Handler "
print #f, "' -------------------------"
print #f, "[gotError]"
print #f, "notice ""Error:"" + chr$(13) + Err$ + chr$(13) + ""Error number is "";Err"
print #f, "wait"

print #f, ""
print #f, "' -------------------------"
print #f, "' change record "
print #f, "' -------------------------"
print #f, "[chg]"
print #f, "gosub [getData]"
print #f, "sql$ = """""
print #f, "cma$ = """""
print #f, "for i = 1 to numFlds"
print #f, " f$ = word$(flds$,i,chr$(251))"
print #f, " v$ = word$(vals$,i,chr$(251))"
print #f, " if v$ = chr$(251) then v$ = """" "
print #f, " sql$ = sql$ + cma$ + f$;"" = '"";v$;""'"""
print #f, " cma$ = "","";chr$(13)"
print #f, "next i"
print #f, "sql$ = ""UPDATE "";tblName$;"" SET "" + sql$ + "" WHERE rowid = "";rowid$"
'print #f, "print sql$"
print #f, "#sql execute(sql$)"
print #f, "ap = 0"
print #f, "goto [pageIt]"


print #f, "' --------------------------------------"
print #f, "' get outta here "
print #f, "' --------------------------------------"
print #f, "[exit]"
print #f, "#sql disconnect()"
print #f, "end"

print #f, "' --------------------------------------"
print #f, "' get screen data "
print #f, "' --------------------------------------"
print #f, "[getData]"
print #f, "flds$ = """""
print #f, "vals$ = """""
print #f, "sep$ = """""
print #f, "for i = 1 to numFlds"
print #f, " a$ = word$(fldNames$,i,"","")"
print #f, " x$ = eval$(""#w.TB"";a$;"" contents$()"")"
print #f, " x$ = dblQuote$(x$)"
'print #f, " if trim$(x$) <> """" then "
print #f, " flds$ = flds$ + sep$ + a$"
print #f, " vals$ = vals$ + sep$ + x$"
print #f, " sep$ = chr$(251)"
'print #f, " end if"
print #f, "next i"
print #f, "vals$ = vals$ + sep$"
'print #f, "print ""flds:"";flds$"
'print #f, "print ""vals:"";vals$"
print #f, "RETURN"

print #f, "' --------------------------------------------------"
print #f, "' Get rowid from grid for detail record display"
print #f, "' --------------------------------------------------"
print #f, "[gridData]"
print #f, "gosub [clrData]"
print #f, "xy$ = #w.grid cellxy$()"
print #f, "if val(word$(xy$,1,"" "")) <> 0 then "
print #f, " val$ = #w.grid value$() 'contents of cell clicked"
print #f, "else "
print #f, " xy$ = ""1 1"""
print #f, "end if"
print #f, "r = val(word$(xy$,2))"
print #f, "#w.grid selectxy(1,r) 'col 1 is rowid"
print #f, "rowid$ = #w.grid value$() 'rowid of row clicked"
print #f, "sql$ = ""SELECT * FROM ";tblName$;" WHERE rowid = "";rowid$"
print #f, "#sql execute(sql$)"
print #f, "rows = #sql ROWCOUNT() 'Get the number of rows"
print #f, "if rows < 1 then "
print #f, " notice ""Record not on file"""
print #f, " wait"
print #f, "end if"
print #f, "#row = #sql #nextrow()"
print #f, "'-----------------------"
print #f, "' show record detail"
print #f, "'-----------------------"
for i = 1 to numFlds
print #f, "#w.TB";fldNames$(i);" #row ";fldNames$(i);"$()"
next i
print #f, "wait"

' -------------------------------------------------
' drill down and  search
' -------------------------------------------------
print #f, ""
print #f, "' --------------------------------------"
print #f, "' Drill down search "
print #f, "' --------------------------------------"
print #f, "[srch]"
print #f, "#w.STsch """" " 'Clear search area
print #f, "gosub [getData]"
print #f, "search$ = """""
print #f, "aand$ = """""
print #f, "for i = 1 to numFlds"
print #f, " fld$ = trim$(word$(flds$,i,chr$(251)))"
print #f, " val$ = trim$(word$(vals$,i,chr$(251)))"
print #f, " if val$ <> chr$(251) then "
print #f, " if  left$(val$,1) = ""*"" then val$ = ""%"" + mid$(val$,2) "
print #f, " if right$(val$,1) = ""*"" then val$ = left$(val$,len(val$) -1) + ""%"" "
print #f, " if left$(val$,1) = ""%"" or right$(val$,1) = ""%"" then "
print #f, " search$ = search$ + aand$ + fld$ + "" like '"" + val$ + ""'"" "
print #f, " else"
print #f, " search$ = search$ + aand$ + fld$ + "" = '"" + val$ + ""'"" "
print #f, " end if"
print #f, " aand$ = "" AND """
print #f, " end if "
print #f, "next i"
print #f, "if search$ <> """" then  search$ = "" WHERE "" + search$"
print #f, "gosub [numRecs]"
print #f, "if numRecords = 0 then"
print #f, " notice ""No records match search:"";search$"
print #f, " search$ = """" "
print #f, " gosub [numRecs]"
print #f, " wait"
print #f, "end if"
print #f, "#w.STsch search$"
print #f, "pageNum = 0"
print #f, "goto [nxt]"

' -------------------------------------------------
' set up clear data subroutine
' -------------------------------------------------
print #f, ""
print #f, "' --------------------------------------"
print #f, "' clear screen data "
print #f, "' --------------------------------------"
print #f, "[clr]"
print #f, "gosub [clrData]"
print #f, "rowid$ = """""
print #f, "wait"
print #f, "[clrData]"
print #f, "for i = 1 to numFlds"
print #f, " a$ = word$(fldNames$,i,"","")"
print #f, " t$ = word$(fldTypes$,i,"","")"
print #f, " w$ = ""#w.TB"";a$"
print #f, " if t$ = ""TEXT"" then"
print #f, " #w$ ""!cls"""
print #f, " else"
print #f, " #w$ """""
print #f, " end if"
print #f, "next i"
print #f, "RETURN"

print #f, "' ---------------------------------------"
print #f, "' Get sort order"
print #f, "' ---------------------------------------"
print #f, "[sort]"
print #f, "gosub [sortOrd]"
print #f, "ap = 0"
print #f, "goto [pageIt]"
'print #f, "wait"

print #f, "[sortOrd]"
print #f, "orderBy$ = """""
print #f, "j = 0"
print #f, "for i = 1 to numFlds"
print #f, " a$ = word$(fldNames$,i,"","")"
print #f, " x$ = eval$(""#w.TB"";a$;"" contents$()"")"
print #f, " if left$(x$,1) = chr$(13) then x$ = mid$(x$,2)"
print #f, " if x$ <> """" then "
print #f, " j = j + 1"
print #f, " srt$(j) = x$;""  "";chr$(251);a$"
print #f, " end if"
print #f, "next i"
print #f, "if j = 0 then RETURN ' selected nothing to sort"
print #f, "sort srt$(), 1, j   ' get the sort order"
print #f, "cma$ = "" ORDER BY """
print #f, "for i = 1 to j"
print #f, " seq$ = word$(srt$(i),1,chr$(251))"
print #f, " orderBy$ = orderBy$;cma$;tblName$;""."";word$(srt$(i),2,chr$(251))"
print #f, " if instr(seq$,""n"") then orderBy$ = orderBy$ ; "" + 0 """
print #f, " if instr(seq$,""d"") then orderBy$ = orderBy$ ; "" desc"""
print #f, " cma$ = "","""
print #f, "next i"
print #f, "#w.STsrt orderBy$"
print #f, "RETURN"

print #f, "' -----------------------------------------------"
print #f, "' How many total records based on your search? "
print #f, "' -----------------------------------------------"
print #f, "[numRecs]"
print #f, "sql$ = ""SELECT count(*) as numRecords FROM ";tblName$;"""; search$"
print #f, "#sql execute(sql$)"
print #f, "#row = #sql #nextrow()"
print #f, "numRecords = #row numRecords()"
print #f, "numPages = numRecords / lpp"
print #f, "numPages = int(numPages + .9)"
print #f, "pageNum = min(pageNum,numPages)"
print #f, "print #w.pge, pageNum"
print #f, "print #w.STrec, ""Records:"";numRecords;"" Pages:"";numPages"
print #f, "RETURN"

print #f, "' ---------------------------------"
print #f, "' Previous Next or User Page Number"
print #f, "' ----------------------------------"
print #f, "[pre]"
print #f, "ap = -1"
print #f, "goto [pageIt]"
print #f, ""
print #f, "[nxt]"
print #f, "ap = 1"
print #f, ""
print #f, "[pageIt]"
print #f, "prePage = pageNum"
print #f, "pageNum = pageNum + ap"
print #f, "if numRecords < 1 then "
print #f, "    notice ""There are no records to list"" "
print #f, "    wait"
print #f, "end if"
print #f, "totPages = int((numRecords / lpp) + .9)"
print #f, "#w.pge ""!contents? goPageNum$"""
print #f, "if goPageNum$ <> """" then "
print #f, " goPageNum = int(val(goPageNum$))"
print #f, " if goPageNum <> prePage then "
print #f, " if goPageNum > totPages or goPageNum < 1 then "
print #f, " notice ""Page must be between 1 and "";totPages"
print #f, " wait"
print #f, " end if"
print #f, " pageNum = goPageNum"
print #f, " end if"
print #f, "end if"
print #f, "pageNum  = max(1,pageNum) ' make suer it has a page number"
print #f, "if lpp < 1 then lpp = 30 ' lines per page must be specified"
print #f, "lpp = max(5,lpp)     ' make sure it has a least 5 lines per page"
print #f, "lpp = min(60,lpp)     ' don not allow over 60 lines per page"
print #f, ""
print #f, "if lpp * totPages <> numRecords then totPages = totPages + 1"
print #f, "pageNum = min(totPages,pageNum)"
print #f, "pageNum = max(1,pageNum)"
print #f, "limitBeg = (pageNum * lpp) - lpp  'limit begin value"
print #f, "limit$ = "" LIMIT "" ; limitBeg ; "","" ; lpp"

print #f, "' ---------------------------------------"
print #f, "' show a page of table data"
print #f, "' ---------------------------------------"
print #f, "[nextPage]"
print #f, "print #w.pge, pageNum"
print #f, "dim  gridData$(numFlds + 1,lpp)"
print #f, "sql$ = ""SELECT rowid,* FROM ";tblName$;""" ;search$; orderBy$; limit$"
print #f, "#sql execute(sql$)"
print #f, "rows = #sql ROWCOUNT() 'Get the number of rows"
print #f, "for i = 1 to rows"
print #f, " result$ = #sql nextrow$("" "";chr$(251))"
print #f, " for j = 1 to numFlds + 1"
print #f, " x$ = left$(trim$(word$(result$,j,chr$(251))),12)"
print #f, " gridData$(j - 1,i -1) = x$"
print #f, " next j"
print #f, "next i"
print #f, "#w.grid reload()"
print #f, "wait"

print #f, "' -----------------------------------------"
print #f, "' csv output"
print #f, "' output according to search and sort"
print #f, "' enclosed in quotes ("")"
print #f, "' seperated with comma (,)"
print #f, "' ending with semicolon (;)"
print #f, "' convert single quote (') to 2 ('')"
print #f, "' -----------------------------------------"
print #f, "[csv]"
print #f, "outFile$ = DefaultDir$;""";tblName$;".csv"""
print #f, "sep$ = chr$(34);"","";chr$(34)"
print #f, "sql$ = ""SELECT * FROM ";tblName$;""" ;search$; orderBy$"
print #f, "#sql execute(sql$)"
print #f, "rows = #sql ROWCOUNT() 'Get the number of rows"
print #f, "if rows > 0 then"
print #f, " open outFile$ for output as #f"
print #f, " WHILE #sql hasanswer()"
print #f, " result$ = #sql nextrow$(sep$)"
print #f, " print #f, chr$(34);result$;chr$(34);"";"""
print #f, "WEND"
print #f, " close #f"
print #f, " notice ""CSV output:"";outFile$"
print #f, "end if"
print #f, "wait"

print #f, "' -----------------------------------------"
print #f, "' Convert single quotes to double quotes"
print #f, "' -----------------------------------------"
print #f, "FUNCTION dblQuote$(str$)"
print #f, "i    = 1"
print #f, "qq$  = """" "
print #f, "while (word$(str$,i,""'"")) <> """""
print #f, " dblQuote$   = dblQuote$;qq$;word$(str$,i,""'"")"
print #f, " qq$ = ""''"""
print #f, " i   = i + 1"
print #f, "WEND"
print #f, "END FUNCTION"

close #f
wait

' ---------------------------------------
' 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$ = "                                       "
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


[metro]

Thanks Dan,

the only change I had to make was to connect to Sqlite in Linux(32)

sqliteconnect #sql,  db$ ,"/usr/lib/i386-linux-gnu" ' Connect to DB
and further down where the generated code is saved............
print #f, "sqliteconnect    #sql, """;db$;"""," ;chr$(34);"/usr/lib/i386-linux-gnu";""" ' Connect to the DB"

apart from that, it worked flawlessly . I will make the textboxes wider though.

Thanks for the code, I'll find it useful

[meerkat]

I will make the textboxes wider though.

Ya! It's just your basic code. It's easy to modify the output such as increasing the number of listed records.
For your particular preferences, it's probably better to modify the program generator.
I have a generator that lets you do "JOINS", but the interface is a mess. If I could fix that, I'd submit it.
It also has a buttons for CSV output and input. But those are so simple I just left them out.

For me, I found that I no longer needed List Boxes, or Combo Boxes. The GRID works for me.

If you decide to add code to the generator, I'd like to get it. Or simply submit your modified generator here.

Have a great day.
Good luck
Dan

[metro]

I will make the textboxes wider though.

Ok, got to the bottom of this, I sometimes create fields with just the TYPE of "text" or "char" but do not stipulate the length
So that is why with my first run of the program on my phone records db the textboxes were too small.

My second db for postage records I did  set the field length of each field so the generated program has textboxes big enough.

For me, I found that I no longer needed List Boxes, or Combo Boxes. The GRID works for me

same here , though I am looking forward to the final release of LB5 with a grid that looks like a listview (pretty).

[meerkat]

Ok, got to the bottom of this, I sometimes create fields with just the TYPE of "text" or "char" but do not stipulate the length

As you already know the TYPE in SQLite is just a suggestion. You can put characters in Integer fields, or anything in anything. What it does do is lets you know what TYPE you expect and what your format should look like.

I am looking forward to the final release of LB5 with a grid that looks like a listview (pretty).
We know LB5 will not support HTML. But if the grid incorporates some if it, things would be a lot easier. Some of the simple stuff like, cell colors, grids within grids, input with numeric and range checks, date with calendar popup, color popup, time popup. Or at the cell level, mouse over branch, mouse leave branch, character by character branch.
Who knows, maybe some of this will happen down the road.