The conventional filing system is a very adequate means of storing data for almost any requirement. However, speed and performance might prove an issue in cases of large and unstructured datasets.
Databases, on the other hand, are designed to handle multiple tables, each acting like independent files, indexed and safely stored according to data size and type. And they do this with great speed and efficiency.
Among the databases that are supported in PureBasic, SQLite is the easiest and most lightweight option that could justifiably substitute the use of conventional files. Moreover, it's quite easy and straightforward to implement and utilise.
This short step-by-step tutorial aims to demonstrate the syntax and usage of PureBasic's built-in SQLite functions. Please do take note that the sample snippets should be run sequentially, as most depend on the preceding ones in order to obtain the correct results and output.
Creating an empty file for the database
Code: Select all
; create an empty file - ensure file path
If CreateFile(0, "sqliteFile.sqlite")
; close the file
CloseFile(0)
EndIf
Creating a new SQLite database
Code: Select all
#sqlite = 0
; initialise SQLite library
UseSQLiteDatabase()
; re-open the newly created file as an SQLite database.
; the last two parameters of the OpenDatabase() function
; are for username & password and should be left blank unless
; using an externally-created SQLite database that has set them.
; this feature to set username and password is not supported in PureBasic
If OpenDatabase(#sqlite, "sqliteFile.sqlite", "", "")
; tables must be created before the database can be used.
; this query instructs the database to create a table named
; CONTACTS and format it with the following data fields:
; 1. id - numerical data type
; 2. age - numerical data type
; 3. name - text data type with a length of 100
; 4. address - text data type with a length of 200
; 5. telephone - text data type with a length of 50
query.s = "CREATE TABLE contacts (id INTEGER PRIMARY KEY, age INTEGER, " +
"name CHAR(100), address CHAR(200), telephone CHAR(50))"
; update the database with the prepared query
If DatabaseUpdate(#sqlite, query)
Debug "database tables created successfully."
Else
Debug "error creating database tables! " + DatabaseError()
EndIf
; close the database file
CloseDatabase(#sqlite)
Else
Debug "error opening database! " + DatabaseError()
EndIf
Writing data to SQLite database
Code: Select all
#sqlite = 0
; initialise SQLite library
UseSQLiteDatabase()
; re-open the newly created SQLite database
If OpenDatabase(#sqlite, "sqliteFile.sqlite", "", "")
; write some data to the database table with inline literal values.
; notice that no value is being set for the ID - this is because it was
; created as the PRIMARY key which would be automatically set and inserted.
query.s = "INSERT INTO contacts (age, name, address, telephone) " +
"VALUES (70, 'Billy Joel', 'The Bronx, New York, USA.', '5551234')"
; update the database with the literal prepared query and confirm the write
If DatabaseUpdate(#sqlite, query) And AffectedDatabaseRows(#sqlite) = 1
Debug "data successfully inserted."
Else
Debug "error inserting data! " + DatabaseError()
EndIf
; close the database file
CloseDatabase(#sqlite)
Else
Debug "error opening database! " + DatabaseError()
EndIf
Reading data from SQLite database (reading routine)
Code: Select all
#sqlite = 0
; initialise SQLite library
UseSQLiteDatabase()
; re-open the SQLite database
If OpenDatabase(#sqlite, "sqliteFile.sqlite", "", "")
; retrieve ALL (*) data and records from the CONTACTS table
query.s = "SELECT * FROM contacts"
; results matching query retrieved
If DatabaseQuery(#sqlite, query)
; iterate through all the retrieved results
While NextDatabaseRow(#sqlite)
; retrieve & display the ID number which is in the first column (column 0)
Debug #CRLF$ + "Record #" + Str(GetDatabaseLong(#sqlite, 0)) + ":"
; determine the number of columns in the retrieved row
numberOfColumns = DatabaseColumns(#sqlite)
; iterate through the columns from column 1
For i = 1 To (numberOfColumns - 1)
; determine the column type
columnType = DatabaseColumnType(#sqlite, i)
; this database contains only text & numbers
; so we'll be filtering only those data types
If columnType = #PB_Database_String
Debug GetDatabaseString(#sqlite, i)
ElseIf columnType = #PB_Database_Long Or
columnType = #PB_Database_Quad
Debug Str(GetDatabaseLong(#sqlite, i))
EndIf
Next i
Wend
; release the database query resources
FinishDatabaseQuery(#sqlite)
Else
Debug "error retrieving data! " + DatabaseError()
EndIf
; close the database file
CloseDatabase(#sqlite)
Else
Debug "error opening database! " + DatabaseError()
EndIf
Code: Select all
Record #1:
70
Billy Joel
The Bronx, New York, USA.
5551234
Also, the first record was inserted into the database through literal queries, meaning that the values were manually inserted into the query strings themselves (eg: name='Billy Joel'). The next snippet will insert data values through a method called data binding.
Writing data to SQLite database with data binding
Code: Select all
#sqlite = 0
; initialise SQLite library
UseSQLiteDatabase()
; re-open the SQLite database
If OpenDatabase(#sqlite, "sqliteFile.sqlite", "", "")
; insert another record into the database table but this
; time we'll be using data binding instead of literal values
query.s = "INSERT INTO contacts (age, name, address, telephone) " +
"VALUES (?, ?, ?, ?)" ; binding order = 0, 1, 2, 3
; the question marks above will be substituted with these values
; column 0 = age (numerical)
SetDatabaseLong(#sqlite, 0, 61)
; column 1 = name (text)
SetDatabaseString(#sqlite, 1, "Michael Jackson")
; column 2 = address (text)
SetDatabaseString(#sqlite, 2, "123 Neverland, USA.")
; column 3 = telephone (text)
SetDatabaseString(#sqlite, 3, "5554321")
; update the database with the bound prepared query and confirm the write
If DatabaseUpdate(#sqlite, query) And AffectedDatabaseRows(#sqlite) = 1
Debug "data (Michael Jackson) successfully inserted."
; insert another record - but this time we'll rearrange the
; binding sequence by inserting the values in an arbitrary order
query = "INSERT INTO contacts (address, name, telephone, age) " +
"VALUES (?, ?, ?, ?)" ; binding order = 0, 1, 2, 3
; the question marks above will be substituted with these values
; column 0 = address
SetDatabaseString(#sqlite, 0, "Duluth, Minnesota, USA.")
; column 1 = name (text)
SetDatabaseString(#sqlite, 1, "Bob Dylan")
; column 2 = telephone (text)
SetDatabaseString(#sqlite, 2, "5550001")
; column 3 = age (numerical)
SetDatabaseLong(#sqlite, 3, 78)
; update the database with the bound prepared query and confirm the write
If DatabaseUpdate(#sqlite, query) And AffectedDatabaseRows(#sqlite) = 1
Debug "data (Bob Dylan) successfully inserted."
Else
Debug "error inserting data (Bob Dylan)! " + DatabaseError()
EndIf
Else
Debug "error inserting data (Michael Jackson)! " + DatabaseError()
EndIf
; close the database file
CloseDatabase(#sqlite)
Else
Debug "error opening database! " + DatabaseError()
EndIf
Reading data from SQLite database (reading routine)
Code: Select all
#sqlite = 0
; initialise SQLite library
UseSQLiteDatabase()
; re-open the SQLite database
If OpenDatabase(#sqlite, "sqliteFile.sqlite", "", "")
; retrieve ALL (*) data and records from the CONTACTS table
query.s = "SELECT * FROM contacts"
; results matching query retrieved
If DatabaseQuery(#sqlite, query)
; iterate through all the retrieved results
While NextDatabaseRow(#sqlite)
; retrieve & display the ID number which is in the first column (column 0)
Debug #CRLF$ + "Record #" + Str(GetDatabaseLong(#sqlite, 0)) + ":"
; determine the number of columns in the retrieved row
numberOfColumns = DatabaseColumns(#sqlite)
; iterate through the columns from column 1
For i = 1 To (numberOfColumns - 1)
; determine the column type
columnType = DatabaseColumnType(#sqlite, i)
; this database contains only text & numbers
; so we'll be filtering only those data types
If columnType = #PB_Database_String
Debug GetDatabaseString(#sqlite, i)
ElseIf columnType = #PB_Database_Long Or
columnType = #PB_Database_Quad
Debug Str(GetDatabaseLong(#sqlite, i))
EndIf
Next i
Wend
; release the database query resources
FinishDatabaseQuery(#sqlite)
Else
Debug "error retrieving data! " + DatabaseError()
EndIf
; close the database file
CloseDatabase(#sqlite)
Else
Debug "error opening database! " + DatabaseError()
EndIf
Code: Select all
Record #1:
70
Billy Joel
The Bronx, New York, USA.
5551234
Record #2:
61
Michael Jackson
123 Neverland, USA.
5554321
Record #3:
78
Bob Dylan
Duluth, Minnesota, USA.
5550001
Writing data to SQLite database in a loop
Code: Select all
#sqlite = 0
; initialise SQLite library
UseSQLiteDatabase()
; re-open the SQLite database
If OpenDatabase(#sqlite, "sqliteFile.sqlite", "", "")
; inserting multiple records in a loop
; for clarity we'll use inline literal values
; prepare and assign the queries into an array
Dim arrayQuery.s(2)
arrayQuery(0) = "INSERT INTO contacts (age, name, address, telephone) " +
"VALUES (68, 'Gordon Sumner', 'Wallsend, UK.', '4441234')"
arrayQuery(1) = "INSERT INTO contacts (age, name, address, telephone) " +
"VALUES (72, 'Elton John', 'Pinner, UK.', '4444321')"
arrayQuery(2) = "INSERT INTO contacts (age, name, address, telephone) " +
"VALUES (65, 'Neil Tenant', 'Northumberland, UK.', '4442244')"
; execute the array of prepared queries in a loop
For i = 0 To 2
DatabaseUpdate(#sqlite, arrayQuery(i))
; verify that three (3) records were written
updatedCount + AffectedDatabaseRows(#sqlite)
Next i
Debug Str(updatedCount) + " records written."
; if three (3) records were not inserted something went wrong
If updatedCount < 3
Debug "error inserting records! " + DatabaseError()
EndIf
; close the database file
CloseDatabase(#sqlite)
Else
Debug "error opening database! " + DatabaseError()
EndIf
Code: Select all
Record #1:
70
Billy Joel
The Bronx, New York, USA.
5551234
Record #2:
61
Michael Jackson
123 Neverland, USA.
5554321
Record #3:
78
Bob Dylan
Duluth, Minnesota, USA.
5550001
Record #4:
68
Gordon Sumner
Wallsend, UK.
4441234
Record #5:
72
Elton John
Pinner, UK.
4444321
Record #6:
65
Neil Tenant
Northumberland, UK.
4442244
Updating existing data in SQLite database
Code: Select all
#sqlite = 0
; initialise SQLite library
UseSQLiteDatabase()
; re-open the SQLite database
If OpenDatabase(#sqlite, "sqliteFile.sqlite", "", "")
; update one of the previously-written database records with an
; inline string literal - change Michael Jackson's address details
query.s = "UPDATE contacts SET address='Gary, Indiana, USA.' WHERE name='Michael Jackson'"
; update the database with the literal prepared query and confirm the write
If DatabaseUpdate(#sqlite, query) And AffectedDatabaseRows(#sqlite) = 1
Debug "record (Michael Jackson) updated successfully."
; update another one of the previously-written database records with a bound value -
; change a telephone number with the existing telephone number as the query condition
query = "UPDATE contacts SET telephone=? WHERE telephone=?"
; 0 = new telephone (text)
SetDatabaseString(#sqlite, 0, "5552244")
; 1 = existing telephone (text)
SetDatabaseString(#sqlite, 1, "5550001")
; update the database with the literal prepared query and confirm the write
If DatabaseUpdate(#sqlite, query) And AffectedDatabaseRows(#sqlite) = 1
Debug "record (Bob Dylan) updated successfully."
Else
Debug "error updating record (Bob Dylan)! " + DatabaseError()
EndIf
Else
Debug "error updating record (Michael Jackson)! " + DatabaseError()
EndIf
; close the database file
CloseDatabase(#sqlite)
Else
Debug "error opening database! " + DatabaseError()
EndIf
Code: Select all
Record #1:
70
Billy Joel
The Bronx, New York, USA.
5551234
Record #2:
61
Michael Jackson
Gary, Indiana, USA.
5554321
Record #3:
78
Bob Dylan
Duluth, Minnesota, USA.
5552244
Record #4:
68
Gordon Sumner
Wallsend, UK.
4441234
Record #5:
72
Elton John
Pinner, UK.
4444321
Record #6:
65
Neil Tenant
Northumberland, UK.
4442244
Deleting existing data in SQLite database
Code: Select all
#sqlite = 0
; initialise SQLite library
UseSQLiteDatabase()
; re-open the SQLite database
If OpenDatabase(#sqlite, "sqliteFile.sqlite", "", "")
; delete the record where the name is Elton John
query.s = "DELETE FROM contacts WHERE name='Elton John'"
; delete the record from the database and confirm the deletion
If DatabaseUpdate(#sqlite, query) And AffectedDatabaseRows(#sqlite) = 1
Debug "record (Elton John) successfully deleted from contacts database."
Else
Debug "error deleting record (Elton John)! " + DatabaseError()
EndIf
; close the database file
CloseDatabase(#sqlite)
Else
Debug "error opening database! " + DatabaseError()
EndIf
Code: Select all
Record #1:
70
Billy Joel
The Bronx, New York, USA.
5551234
Record #2:
61
Michael Jackson
Gary, Indiana, USA.
5554321
Record #3:
78
Bob Dylan
Duluth, Minnesota, USA.
5552244
Record #4:
68
Gordon Sumner
Wallsend, UK.
4441234
Record #6:
65
Neil Tenant
Northumberland, UK.
4442244
Reading data from SQLite database with conditional queries (conditional reading routine)
Code: Select all
#sqlite = 0
; initialise SQLite library
UseSQLiteDatabase()
; re-open the SQLite database
If OpenDatabase(#sqlite, "sqliteFile.sqlite", "", "")
; retrieve only records with the name Gordon Sumner
query.s = "SELECT * FROM contacts WHERE name='Gordon Sumner'"
; results matching query retrieved
If DatabaseQuery(#sqlite, query)
; iterate through all the retrieved results
While NextDatabaseRow(#sqlite)
; retrieve & display the ID number which is in the first column (column 0)
Debug #CRLF$ + "Record #" + Str(GetDatabaseLong(#sqlite, 0)) + ":"
; determine the number of columns in the retrieved row
numberOfColumns = DatabaseColumns(#sqlite)
; iterate through the columns from column 1
For i = 1 To (numberOfColumns - 1)
; determine the column type
columnType = DatabaseColumnType(#sqlite, i)
; this database contains only text & numbers
; so we'll be filtering only those data types
If columnType = #PB_Database_String
Debug GetDatabaseString(#sqlite, i)
ElseIf columnType = #PB_Database_Long Or
columnType = #PB_Database_Quad
Debug Str(GetDatabaseLong(#sqlite, i))
EndIf
Next i
Wend
; release the database query resources
FinishDatabaseQuery(#sqlite)
Else
Debug "error retrieving data! " + DatabaseError()
EndIf
; close the database file
CloseDatabase(#sqlite)
Else
Debug "error opening database! " + DatabaseError()
EndIf
Code: Select all
Record #4:
68
Gordon Sumner
Wallsend, UK.
4441234
Code: Select all
; retrieve only records with telephone numbers prefixed with 555
query.s = "SELECT * FROM contacts WHERE telephone LIKE '555%'"
Code: Select all
Record #1:
70
Billy Joel
The Bronx, New York, USA.
5551234
Record #2:
61
Michael Jackson
Gary, Indiana, USA.
5554321
Record #3:
78
Bob Dylan
Duluth, Minnesota, USA.
5552244
Code: Select all
; retrieve only records with addresses containing UK
query.s = "SELECT * FROM contacts WHERE address LIKE '%UK%'"
Code: Select all
Record #4:
68
Gordon Sumner
Wallsend, UK.
4441234
Record #6:
65
Neil Tenant
Northumberland, UK.
4442244
Code: Select all
; retrieve only records with ages below 70
query.s = "SELECT * FROM contacts WHERE age < 70"
Code: Select all
Record #2:
61
Michael Jackson
Gary, Indiana, USA.
5554321
Record #4:
68
Gordon Sumner
Wallsend, UK.
4441234
Record #6:
65
Neil Tenant
Northumberland, UK.
4442244
Altering the SQLite database table
Code: Select all
#sqlite = 0
; initialise SQLite library
UseSQLiteDatabase()
; re-open the SQLite database
If OpenDatabase(#sqlite, "sqliteFile.sqlite", "", "")
; alter the database table to add another column with decimal type
query.s = "ALTER TABLE contacts ADD COLUMN height REAL"
; execute the alteration query
If DatabaseUpdate(#sqlite, query)
Debug "column added successfully."
Else
Debug "error adding column! " + DatabaseError()
EndIf
; close the database file
CloseDatabase(#sqlite)
Else
Debug "error opening database! " + DatabaseError()
EndIf
Code: Select all
#sqlite = 0
; initialise SQLite library
UseSQLiteDatabase()
; re-open the SQLite database
If OpenDatabase(#sqlite, "sqliteFile.sqlite", "", "")
; retrieve ALL (*) data and records from the CONTACTS table
query.s = "SELECT * FROM contacts"
; results matching query retrieved
If DatabaseQuery(#sqlite, query)
; iterate through all the retrieved results
While NextDatabaseRow(#sqlite)
; retrieve & display the ID number which is in the first column (column 0)
Debug #CRLF$ + "Record #" + Str(GetDatabaseLong(#sqlite, 0)) + ":"
; determine the number of columns in the retrieved row
numberOfColumns = DatabaseColumns(#sqlite)
; iterate through the columns from column 1
For i = 1 To (numberOfColumns - 1)
; determine the column type
columnType = DatabaseColumnType(#sqlite, i)
; this database contains only text & numbers
; so we'll be filtering only those data types
If columnType = #PB_Database_String
Debug GetDatabaseString(#sqlite, i)
ElseIf columnType = #PB_Database_Long Or
columnType = #PB_Database_Quad
Debug Str(GetDatabaseLong(#sqlite, i))
ElseIf columnType = #PB_Database_Double Or
columnType = #PB_Database_Float
Debug StrD(GetDatabaseDouble(#sqlite, i))
EndIf
Next i
Wend
; release the database query resources
FinishDatabaseQuery(#sqlite)
Else
Debug "error retrieving data! " + DatabaseError()
EndIf
; close the database file
CloseDatabase(#sqlite)
Else
Debug "error opening database! " + DatabaseError()
EndIf
Code: Select all
Record #1:
70
Billy Joel
The Bronx, New York, USA.
5551234
0
Record #2:
61
Michael Jackson
Gary, Indiana, USA.
5554321
0
Record #3:
78
Bob Dylan
Duluth, Minnesota, USA.
5552244
0
Record #4:
68
Gordon Sumner
Wallsend, UK.
4441234
0
Record #6:
65
Neil Tenant
Northumberland, UK.
4442244
0
Code: Select all
#sqlite = 0
; initialise SQLite library
UseSQLiteDatabase()
; re-open the SQLite database
If OpenDatabase(#sqlite, "sqliteFile.sqlite", "", "")
; insert height values for all the records
Dim arrayQuery.s(4)
arrayQuery(0) = "UPDATE contacts SET height=1.66 WHERE name='Billy Joel'"
arrayQuery(1) = "UPDATE contacts SET height=1.75 WHERE name='Michael Jackson'"
arrayQuery(2) = "UPDATE contacts SET height=1.71 WHERE name='Bob Dylan'"
arrayQuery(3) = "UPDATE contacts SET height=1.81 WHERE name='Gordon Sumner'"
arrayQuery(4) = "UPDATE contacts SET height=1.79 WHERE name='Neil Tenant'"
; reset the counter variable
updatedCount = 0
; execute the array of prepared queries in a loop
For i = 0 To 4
DatabaseUpdate(#sqlite, arrayQuery(i))
; verify that five (5) records were updated
updatedCount + AffectedDatabaseRows(#sqlite)
Next i
Debug Str(updatedCount) + " records updated."
If updatedCount < 5
Debug "error updating records! " + DatabaseError()
EndIf
; close the database file
CloseDatabase(#sqlite)
Else
Debug "error opening database! " + DatabaseError()
EndIf
Code: Select all
Record #1:
70
Billy Joel
The Bronx, New York, USA.
5551234
1.66
Record #2:
61
Michael Jackson
Gary, Indiana, USA.
5554321
1.75
Record #3:
78
Bob Dylan
Duluth, Minnesota, USA.
5552244
1.71
Record #4:
68
Gordon Sumner
Wallsend, UK.
4441234
1.81
Record #6:
65
Neil Tenant
Northumberland, UK.
4442244
1.79
Writing BLOB data into SQLite database
Code: Select all
#sqlite = 0
; initialise SQLite library
UseSQLiteDatabase()
; re-open the SQLite database
If OpenDatabase(#sqlite, "sqliteFile.sqlite", "", "")
; alter the database table to add another column
query.s = "ALTER TABLE contacts ADD COLUMN picture BLOB"
; execute the alteration query
If DatabaseUpdate(#sqlite, query)
Debug "column added successfully."
; get the path to the image file
CompilerIf #PB_Compiler_OS = #PB_OS_Windows
picture$ = #PB_Compiler_Home + "examples\sources\data\PureBasic.bmp"
CompilerElse
picture$ = #PB_Compiler_Home + "examples/sources/data/PureBasic.bmp"
CompilerEndIf
; open the sample image file - ensure paths
If ReadFile(0, picture$)
; get the file size as the image size
pictureSize = Lof(0)
; allocate a memory block to store the image
*pictureBuffer = AllocateMemory(pictureSize)
If *pictureBuffer
; load the image into the allocated memory block
bytes = ReadData(0, *pictureBuffer, pictureSize)
; insert the sample image into Bob Dylan's contact picture
query = "UPDATE contacts SET picture=? WHERE name='Bob Dylan'"
; bind the contents of the memory block to the query to set the picture
SetDatabaseBlob(#sqlite, 0, *pictureBuffer, pictureSize)
; update the record into database and confirm the write
If DatabaseUpdate(#sqlite, query) And AffectedDatabaseRows(#sqlite) = 1
Debug "record updated successfully."
Else
Debug "error updating record! " + DatabaseError()
EndIf
Else
Debug "error allocating memory buffer!"
EndIf
Else
Debug "error opening image file!"
EndIf
Else
Debug "error adding column! " + DatabaseError()
EndIf
; close the database file
CloseDatabase(#sqlite)
Else
Debug "error opening database! " + DatabaseError()
EndIf
For this example, please ensure that the paths to PureBasic's sample folder and image are correct. Alternatively, any image file can be substituted in its place, along with the relevant image libraries (UseJPEG_xxx, UsePNG_xxx, etc).
This next and final snippet reads Bob Dylan's contact record from the database, including the newly inserted image, and displays them in a demo window:
Code: Select all
#sqlite = 0
; initialise SQLite library
UseSQLiteDatabase()
; re-open the SQLite database
If OpenDatabase(#sqlite, "sqliteFile.sqlite", "", "")
; retrieve Bob Dylan's contact record
query.s = "SELECT * FROM contacts WHERE name='Bob Dylan'"
; results matching query retrieved
If DatabaseQuery(#sqlite, query)
; only one result is expected
If NextDatabaseRow(#sqlite)
; an array to hold the extracted column values
Dim columnValues.s(5)
; determine the number of columns in the retrieved row
numberOfColumns = DatabaseColumns(#sqlite)
; iterate through the columns from column 0
For i = 0 To (numberOfColumns - 1)
; determine the column type
columnType = DatabaseColumnType(#sqlite, i)
; this database contains only text & numbers
; so we'll be filtering only those data types
If columnType = #PB_Database_String
columnValues(i) = GetDatabaseString(#sqlite, i)
ElseIf columnType = #PB_Database_Long Or
columnType = #PB_Database_Quad
columnValues(i) = Str(GetDatabaseLong(#sqlite, i))
ElseIf columnType = #PB_Database_Double Or
columnType = #PB_Database_Float
columnValues(i) = StrD(GetDatabaseDouble(#sqlite, i))
ElseIf columnType = #PB_Database_Blob
; get the size of the BLOB column
pictureSize = DatabaseColumnSize(#sqlite, i)
; allocate a memory block to store the image
*picture = AllocateMemory(pictureSize)
; retrieve the picture data from the query results into the memory block
GetDatabaseBlob(#sqlite, i, *picture, pictureSize)
EndIf
Next i
EndIf
; release the database query resources
FinishDatabaseQuery(#sqlite)
wFlags = #PB_Window_SystemMenu | #PB_Window_ScreenCentered
OpenWindow(0, 0, 0, 300, 300, "PureBasic SQLite Tutorial", wFlags)
TextGadget(0, 10, 10, 280, 30, "Record #: " + columnValues(0))
TextGadget(1, 10, 40, 280, 30, "Name: " + columnValues(2))
TextGadget(2, 10, 70, 280, 30, "Age: " + columnValues(1))
TextGadget(3, 10, 100, 280, 30, "Height: " + columnValues(5))
TextGadget(4, 10, 130, 280, 30, "Address: " + columnValues(3))
TextGadget(5, 10, 160, 280, 30, "Telephone: " + columnValues(4))
TextGadget(6, 10, 190, 280, 30, "Picture: ")
; assign an image identifier to the image data in memory
If CatchImage(0, *picture)
ImageGadget(7, 10, 220, 0, 0, ImageID(0))
Else
Debug "error capturing picture data!"
EndIf
While WaitWindowEvent() ! #PB_Event_CloseWindow : Wend
Else
Debug "error retrieving data! " + DatabaseError()
EndIf
; close the database file
CloseDatabase(#sqlite)
Else
Debug "error opening database! " + DatabaseError()
EndIf
And that's about it for this tutorial!
While we've covered almost all the fundamental aspects of using SQLite databases in PureBasic, there is still a vast scope of functionalities that can be applied, from structured and modular executions, to complex and compound SQLite queries and transactions. Nevertheless, this should provide a fairly decent foundation to get started with database programming.
A practical example of these functions in action can be found in this little utility:
> MealMaster Recipe Extractor