Post by mikeukmid on Oct 24, 2007 15:51:36 GMT -5
Here's a starter demo of a SQLite database covering most often used SQL statements and using an autoincrement primary key. Hopefully of some use.
Mike.
fileName$="C:\rbp\SQLite3Database.db"
isFile = fileExists(fileName$)
if isFile then
print fileName$;" - File already exists"
print "Entries ordered by name:"
else
sqliteconnect #mySQLdb, "SQLite3Database.db"
query$ = "create table table1 (name char(50), telNum char(20),id integer primary key autoincrement)"
#mySQLdb execute(query$)
query$ = "insert into table1 (name, telNum, id) values (""John"", ""01782556677"",NULL)"
#mySQLdb execute(query$)
query$ = "insert into table1 (name, telNum,id) values (""Mary"", ""01248763132"",NULL)"
#mySQLdb execute(query$)
#mySQLdb disconnect()
Print "New SQLlite3 database created"
end if
gosub [countEntries]
gosub [readFromTable]
gosub [links]
wait
end
[readFromTable]
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$)
while #mySQLdb hasanswer()
result$ = #mySQLdb nextrow$(",")
print result$
wend
#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
query$ = "insert into table1 (name, telNum,id) values ('"+n$+"', '"+p$+"',NULL)"
sqliteconnect #mySQLdb, "SQLite3Database.db"
#mySQLdb execute(query$)
#mySQLdb disconnect()
end if
gosub [countEntries]
gosub [readFromTable]
gosub [links]
wait
[modifyDatabase]
'demo changes name in selected key to Anonymous
input "Enter key for entry to modify: ";key$
action$="update table1 set name='Anonymous' where id='"+key$+"'"
sqliteconnect #mySQLdb, "SQLite3Database.db"
#mySQLdb execute(action$)
#mySQLdb disconnect()
gosub [countEntries]
gosub [readFromTable]
gosub [links]
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 disconnect()
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 disconnect()
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]
cls
sqliteconnect #mySQLdb , "SQLite3Database.db"
query$ = "select count(*) from table1"
#mySQLdb execute(query$)
result$ = #mySQLdb nextrow$("")
lastcount = val(result$)
#mySQLdb disconnect()
print "There are ";lastcount;" entries in the database"
return
[links]
print " "
link #add,"[Add]",[addToDatabase]
link #mod,"[Modify]",[modifyDatabase]
link #del,"[Delete]",[deleteFromDatabase]
link #dal,"[Delete All]",[deleteAllFromDatabase]
link #find1,"[Find name]",[findName]
link #find2,"[Find number]",[findNum]
print " "
return
function fileExists(file$)
files #f, file$
isfile = #f rowcount()
fileExists = isfile
end function
Mike.
