BBC BASIC Programmers' Reference

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
=====SQLite Example Program (PhoneNos)=====

//by Jon Ripley, October 2012//\\ \\ **Please note:** This program requires [[http://jonripley.com/bb4w/libraries/SQLite_BB4W_Release_1.exe|SQLite library for BB4W]].\\ \\  This example program shows how to create a simple contact database using SQLite and includes methods to create, update, delete, find and list records.\\ 
<code bb4w>
        INSTALL @lib$+"SQLite"
        PROCsqlite_initialise

        ON ERROR PROCsqlite_close:PROCsqlite_finalise:PRINT:REPORT:END
        ON CLOSE PROCsqlite_close:PROCsqlite_finalise:QUIT

        file$ = @dir$+"Phonenos.db"
        PROCsqlite_open(file$)

        PROCsqlite_exec( \
        \ "CREATE TABLE IF NOT EXISTS phonenos (" + \
        \     "name varchar(100), " + \
        \     "phone varchar(100), " + \
        \     "email varchar(100))")

        PRINT "PhoneNos SQLite Example"
        REPEAT
          PRINT '"  1. Create a record"
          PRINT "  2. Update a record"
          PRINT "  3. List all records"
          PRINT "  4. Search for a record"
          PRINT "  5. Delete a record"
          PRINT "  6. Exit"
          INPUT '"Enter 1-6 : "choice
          PRINT
          IF choice = 1 PROCcreate
          IF choice = 2 PROCupdate
          IF choice = 3 PROClist
          IF choice = 4 PROCsearch
          IF choice = 5 PROCdelete
        UNTIL choice = 6
        PROCsqlite_close
        PROCsqlite_finalise
        QUIT

        DEF PROCcreate
        LOCAL name$, phone$, email$
        PRINT "Enter details"
        INPUT LINE "    Name          : " name$
        INPUT LINE "    Phone number  : " phone$
        INPUT LINE "    Email address : " email$
        name$ = FNsqlite_encodestring(name$)
        phone$ = FNsqlite_encodestring(phone$)
        email$ = FNsqlite_encodestring(email$)
        PROCsqlite_exec( \
        \ "INSERT INTO phonenos (name, phone, email) " + \
        \ "VALUES ("+name$+","+phone$+","+email$+")")
        ENDPROC

        DEF PROCdelete
        LOCAL id$
        INPUT "Enter the RowID of the record to delete : "id$
        id$ = FNsqlite_encodestring(id$)
        PROCsqlite_exec( \
        \ "DELETE FROM Phonenos " + \
        \ "WHERE rowid="+id$+"")
        ENDPROC

        DEF PROClist
        LOCAL row, numResults
        PROCsqlite_get_array(\
        \ "SELECT rowid, name, phone, email " + \
        \     "FROM Phonenos ORDER BY name", A%())
        numResults = DIM(A%(),1)
        IF numResults = 0 THEN
          PRINT "No people found"
        ELSE
          FOR row = 1 TO numResults
            PRINT "Person"
            PRINT "    RowID         : " $$A%(row, 0)
            PRINT "    Name          : " $$A%(row, 1)
            PRINT "    Phone number  : " $$A%(row, 2)
            PRINT "    Email address : " $$A%(row, 3)
            PRINT
          NEXT row
        ENDIF
        PROCsqlite_free_array(A%())
        ENDPROC

        DEF PROCsearch
        LOCAL name$, row, numResults
        INPUT"Enter the name to look for : "name$
        name$ = FNsqlite_encodestring("%"+name$+"%")
        PROCsqlite_get_array( \
        \ "SELECT rowid, name, phone, email FROM Phonenos " +\
        \ "WHERE name LIKE " + name$, A%())
        numResults = DIM(A%(),1)
        IF numResults = 0 THEN
          PRINT "Not found"
        ELSE
          PRINT "    RowID         : " $$A%(1, 0)
          PRINT "    Name          : " $$A%(1, 1)
          PRINT "    Phone number  : " $$A%(1, 2)
          PRINT "    Email address : " $$A%(1, 3)
        ENDIF
        PROCsqlite_free_array(A%())
        ENDPROC

        DEF PROCupdate
        LOCAL id$,  numResults, name$, phone$, email$, change$
        INPUT"Enter the RowID of the record to update "id$
        person$ = FNsqlite_encodestring(id$)
        PROCsqlite_get_array( \
        \ "SELECT rowid, name, phone, email FROM Phonenos "+ \
        \ "WHERE rowid=" + id$, A%())
        numResults = DIM(A%(),1)
        PRINT
        IF numResults = 0 THEN
          PRINT "Not found"
        ELSE
          PRINT "Person"
          PRINT "    RowID         : " $$A%(1, 0)
          PRINT "    Name          : " $$A%(1, 1)
          PRINT "    Phone number  : " $$A%(1, 2)
          PRINT "    Email address : " $$A%(1, 3)
          INPUT "Change? (Y/N)" change$
          IF INSTR("Yy", change$) THEN
            PRINT '"Enter new details"
            INPUT LINE "    Name          : " name$
            INPUT LINE "    Phone number  : " phone$
            INPUT LINE "    Email address : " email$
            name$ = FNsqlite_encodestring(name$)
            phone$ = FNsqlite_encodestring(phone$)
            email$ = FNsqlite_encodestring(email$)
            id$ = FNsqlite_encodestring($$A%(1, 0))
            PROCsqlite_exec( \
            \ "UPDATE Phonenos SET " + \
            \     "name=" + name$ + \
            \     ", phone=" + phone$ + \
            \     ", email="+ email$ + \
            \ " WHERE rowid="+id$+"")
          ENDIF
          PROCsqlite_free_array(A%())
        ENDIF
        ENDPROC
</code>