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.

    cls
    html "<p align=""center"">"
    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 "</p>"
    gosub [countEntries]
    gosub [readFromTable]
    gosub [links]
    wait
    end
 
[readFromTable]
    html "<div align=""center""><br />"
    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 "</div>"
    #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 "<p align=""center"">"
    print "There are ";lastcount;" entries in the database"
    html "</p>"
    #mySQLdb disconnect()
    return
 
[links]
    html "<div align=""center"">"
    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 "</div>"
    return
 
[quit]
    cls
    end
 
function fileExists(file$)
    files #f, file$
    isfile = #f rowcount()
    fileExists = isfile
end function