PureBasic Forums - English

Code: Select all

;SQLITE_TOY.PB
;A QUICK AND DIRTY DEMO TO GET YOU WRITING TO AND READING FROM SQLITE IN A HURRY

Global Rows, Cols, cmd$, result.l, name$, number$, ct.l, dbh.l

Procedure execute()
  Shared cmd$
  result = SQLiteExec(cmd$)
  If result <> 0
    Print("Failure: "): PrintN(cmd$): Print("Press Enter To Exit Program"): Input(): End
  EndIf
EndProcedure

OpenConsole()

;CONNECT To DATABASE And GET HANDLE
dbh = SQLiteOpen("toy.db")
If dbh = 0
  PrintN("Error opening database"): End
Else
  PrintN("Connected to SQLite")
EndIf

;CREATE A TABLE NAMED "test"
cmd$ = "create table test (name char(8), id_num long)": SQLiteExec(cmd$)
PrintN( "CREATE result = "+Str(Result)+" : "+SQLiteError(Result) )

;NOW INSERT ROWS OF DATA INTO OUR TABLE
cmd$ = "insert into test values ('TOM', 52)": execute()
PrintN( "INSERT result = "+Str(Result)+" : " + Str(SQLiteRowsChanged()) + " row changed")
cmd$ = "insert into test values ('DICK', 36)": execute()
PrintN( "INSERT result = "+Str(Result)+" : " + Str(SQLiteRowsChanged()) + " row changed")
cmd$ = "insert into test values ('HARRY', 18)": execute()
PrintN( "INSERT result = "+Str(Result)+" : " + Str(SQLiteRowsChanged()) + " row changed")
cmd$ = "insert into test values ('MAURICE', 44)": execute()
PrintN( "INSERT result = "+Str(Result)+" : " + Str(SQLiteRowsChanged()) + " row changed")

;WE ARE NOW FINISHED SETTING UP AND LOADING THE DATABASE
;TIME TO READ DATA OUT OF IT

;FIRST MAKE UP A COMMAND STRING
cmd$ = "select name , id_num from test"

;SEND IT OFF TO SQLITE
result = SQLiteGetTable(cmd$)

;HOW MANY ROWS AND COLUMNS WERE RETURNED?
PrintN( "Columns: " + Str(SQLiteCols()) ): PrintN ( "Rows: " + Str(SQLiteRows()) )

;EXTRACT DATA FROM RETURNED TABLE -- NOTE THAT SQLITE RETURNS STRINGS
For ct = 1 To 4
  name$ = SQLiteData(ct, 0): number$ = SQLiteData(ct, 1)
  PrintN (name$ + "  " + number$)
Next

;WE ARE DONE!
;CLOSE DATABASE AND DELETE DATABASE FILE
;IF DATABASE FILE WERE NOT DELETED, WE WOULD HAVE AN EVER-GROWING DATABASE EACH TIME THIS
;PROGRAM IS RUN DUE TO ROWS BEING INSERTED REPEATEDLY
PrintN(""): PrintN("Press Enter To Close and Delete Database")
Input()
result = SQLiteClose()
If result = 0
  PrintN("Database successfully closed")
EndIf

result = DeleteFile("toy.db")
If result <> 0
  PrintN("Database successfully deleted")
EndIf

PrintN(""): PrintN("Press Enter To Exit Program")
Input()
End