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