BBC BASIC Programmers' Reference

by Jon Ripley, October 2012

Please note: This program requires 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.

      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