No+Frills+SQL+Demo

No frills SQL demo database
Here's a starter demo of a SQLite3 database covering most often used SQL statements and using an autoincrement primary key. A database file and table are created when the code is first run.

code format="html4strict" cls html " " fileName$="SQLite3Database.db" if fileExists(fileName$) then print fileName$;" - File already exists" print "Entries ordered by name:" else sqliteconnect #mySQLdb, "SQLite3Database.db" action$ = "create table table1 (name char(50), telNum char(20),id integer primary key autoincrement)" #mySQLdb execute(action$) action$ = "insert into table1 (name, telNum, id) values (""John"", ""01782556677"",NULL)" #mySQLdb execute(action$) action$ = "insert into table1 (name, telNum,id) values (""Mary"", ""01248763132"",NULL)" #mySQLdb execute(action$) #mySQLdb disconnect Print "New SQLlite3 database created" end if   html " " gosub [countEntries] gosub [readFromTable] gosub [links] wait end

[readFromTable] html " " sqliteconnect #mySQLdb, "SQLite3Database.db" 'select all fields in table query$ = "select * from table1 order by name asc;" 'select only specified fields 'query$ = "select name,telNum from table1 order by name asc;" #mySQLdb execute(query$) cssClass "table", "{ width: 400px; text-align:left;border-style:solid;border-width:1px;border-color:#000000;}" cssClass "tr", "{ background: #FFFACD; padding: 3px }" cssClass "td", "{width:45%;}" render #mySQLdb html " " #mySQLdb disconnect return

[addToDatabase] print chr$(160) input "Enter new name: ";n$ input "Enter new phone number: ";p$ input "Add new entry (Y/N): ";c$ if upper$(c$)="Y" then action$ = "insert into table1 (name, telNum,id) values ('"+n$+"', '"+p$+"',NULL)" sqliteconnect #mySQLdb, "SQLite3Database.db" #mySQLdb execute(action$) #mySQLdb disconnect end if   cls gosub [countEntries] gosub [readFromTable] gosub [links] wait

[modifyDatabase] key$="" print "" input "Enter key for entry to modify: ";key$ print "" print "Name: "; textbox #mnme, "" print "Number: "; textbox #mnum, "" print " " Print " " link #conf, "[Accept]", [accept] link #can, "[Cancel]", [cancel] wait [accept] n$=#mnme contents$:num$=#mnum contents$ action$="update table1 set name='";n$;"',telNum='";num$;"' where id='";key$;"'" sqliteconnect #mySQLdb, "SQLite3Database.db" #mySQLdb execute(action$) #mySQLdb disconnect cls [cancel] gosub [countEntries] gosub [readFromTable] gosub [links] wait

[modTable] 'do not execute, only included for reference. Remainder of code does not take into 'account the additional column. print " " print "Table structure change not allowed!" wait 'adds address column to right of table action$="alter table table1 add address char(50)" sqliteconnect #mySQLdb, "SQLite3Database.db" #mySQLdb execute(action$) #mySQLdb disconnect wait

[deleteFromDatabase] input "Enter key for deletion: ";key print " " sqliteconnect #mySQLdb, "SQLite3Database.db" action$="delete from table1 where id="+str$(key) #mySQLdb execute(action$) #mySQLdb execute("vacuum") #mySQLdb disconnect cls gosub [countEntries] gosub [readFromTable] gosub [links] wait

[deleteAllFromDatabase] input "Confirm delete ALL (Y/N): ";conf$ print " " if upper$(conf$)="Y" then sqliteconnect #mySQLdb, "SQLite3Database.db" action$="delete from table1" #mySQLdb execute(action$) #mySQLdb execute("vacuum") #mySQLdb disconnect cls end if   gosub [countEntries] gosub [readFromTable] gosub [links] wait

[findName] input "Enter string to find: ";find$ r$=findEntry$(0,find$) gosub [readFromTable] gosub [links] wait

[findNum] input "Enter string to find: ";find$ r$=findEntry$(1,find$) gosub [readFromTable] gosub [links] wait

function findEntry$(cat, find$) cls print "Search results: " sqliteconnect #mySQLdb, "SQLite3Database.db" if cat=0 then 'using wildcard selection query$ = "select * from table1 where name like '%"+find$+"%' order by id asc;" 'using equality selection 'query$ = "select * from table1 where name='John' order by id asc;" else query$ = "select * from table1 where telNum like '%"+find$+"%' order by id asc;" 'query$ = "select * from table1 where telNum='"+find$+"' order by id asc;" end if   #mySQLdb execute(query$) while #mySQLdb hasanswer result$ = #mySQLdb nextrow$(",") print result$ wend #mySQLdb disconnect print "(End of search list)" print " " end function

[countEntries] sqliteconnect #mySQLdb, "SQLite3Database.db" query$ = "select count(*) from table1" #mySQLdb execute(query$) result$ = #mySQLdb nextrow$("") lastcount = val(result$) html " " print "There are ";lastcount;" entries in the database" html " " #mySQLdb disconnect return

[links] html " " print " " link #add,"[Add]",[addToDatabase] print " "; link #mod,"[Modify]",[modifyDatabase] print " "; 'link #modt,"[Modify Table structure]",[modTable] 'for reference only print " "; link #del,"[Delete]",[deleteFromDatabase] print " "; link #dal,"[Delete All]",[deleteAllFromDatabase] print " "; link #find1,"[Find name]",[findName] print " "; link #find2,"[Find number]",[findNum] print " "; link #quit, "[Quit]",[quit] print " " html " " return

[quit] cls end

function fileExists(file$) files #f, file$ isfile = #f rowcount fileExists = isfile end function code