SQLite Toy - Up and Running Quickly with SQLite

[chris319]

Code updated For 5.20+ (Same as Database libary)

Here is a quick and dirty, no-frills demo showing how to connect to an SQLite database and load and fetch data.

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

[rsts]

hmm - does it work for you?

[chris319]

hmm - does it work for you?

Works like a charm for me.

[blueb]

Some of your commands are not 'stqandard' PureBasic.
For example SQLiteExec(cmd$)

You probably have a LIB installed on your system that you have forgotten about.

--blueb

[rsts]

My system doesn't like procedure Execute ().

Nor do I have a toy.db

[chris319]

Some of your commands are not 'standard' PureBasic.
For example SQLiteExec(cmd$)

You probably have a LIB installed on your system that you have forgotten about.

Um, well you DO have to have SQLite set up in your PureBasic environment. Here is where you can get El_Choni's library:

http://purearea.net/pb/english/userlibs ... rsion_desc

and you can get the necessary sqlite.dll from www.sqlite.org. Should've mentioned that in the O.P., sorry.