SQLite+Example+Program+(PhoneNos)

//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. code format="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