PureBasic SQLite Database: A Quick Tutorial

Share your advanced PureBasic knowledge/code with the community.
User avatar
TI-994A
Addict
Addict
Posts: 2512
Joined: Sat Feb 19, 2011 3:47 am
Location: Singapore
Contact:

PureBasic SQLite Database: A Quick Tutorial

Post by TI-994A »

A QUICK & SIMPLE TUTORIAL ON SQLite DATABASE FUNCTIONS

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
A new file named sqliteFile.sqlite will be created to be used as the database file that will be created in the next step. Take note that if the file already exists it will be overwritten with a new empty file. This step is required only once, so be careful not to accidentally overwrite a healthy database.

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
We've successfully created a fully structured SQLite database file. Let's write some data to it.

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
Now, we've successfully written some data to the SQLite database. So, let's try to retrieve them.

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
Et Voila! We've successfully created, written to, and read from an SQLite database. Notice that in this snippet, there's an additional function, FinishDatabaseQuery(), called before the database is closed. This is only required after calls to the DatabaseQuery() function to clean-up and free resources used while reading and retrieving data from the database. It is not required after calls to the DatabaseUpdate() function.

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
Notice that the variables are bound to the query based on the order of the field names in the query and the index values of the SetDatabase_xxx() functions. Another two records have been inserted into the SQLite database, so let's take a look. The next snippet is a repetition of the earlier one to read the database.

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
So far, so good. Now, let's try multiple data insertions in a loop.

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
If we run the earlier reading routine, we should get this output:

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
We've quite covered the basics of SQLite database reading and writing. Time to delve deeper.

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
The above code has changed two records, one with an inline literal query, and another with a bound query. Michael Jackson's address and Bob Dylan's telephone number have been updated. Again, run the earlier reading routine to see the output.

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
Good progress. But we're not quite done yet. Let's try and delete a record.

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
Run the reading routine and you'll see that Elton John's contact details are no longer listed.

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
So far, we've only performed wildcard reads from the database without any conditional filters. This means that all records in the database are retrieved. In the next snippet, we'll apply some filters to the queries to read records based on criteria.

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
The above query returns only one record from the database where the name exactly matches Gordon Sumner:

Code: Select all

Record #4:
68
Gordon Sumner
Wallsend, UK.
4441234
Now, let's broaden the criteria. Replace the query from the conditional reading routine with the following query and notice the results:

Code: Select all

; retrieve only records with telephone numbers prefixed with 555
query.s = "SELECT * FROM contacts WHERE telephone LIKE '555%'"
It displays only records where the telephone numbers are prefixed with 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
Next, we'll query the database for records where the address is in the UK. Again, replace the query from the conditional reading routine with the following query:

Code: Select all

; retrieve only records with addresses containing UK
query.s = "SELECT * FROM contacts WHERE address LIKE '%UK%'"
It now displays only records that contain the word UK.

Code: Select all

Record #4:
68
Gordon Sumner
Wallsend, UK.
4441234

Record #6:
65
Neil Tenant
Northumberland, UK.
4442244
And lastly, we'll try a comparison filter. Replace the query from the conditional reading routine with the following query:

Code: Select all

; retrieve only records with ages below 70
query.s = "SELECT * FROM contacts WHERE age < 70"
It correctly retrieves only the records where the ages are less than 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
When we first created the database, we created a table with five fields; id, name, age, address, and telephone. What if we needed to expand this table to include more fields? Let's do that now.

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
Since we've added a new column with a decimal data type, we'll have to modify the reading routine in order to display it. Here's the previous reading routine with an additional condition to check for decimal values in the database results:

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
The new height column is retrieved and displayed, albeit only with a zero value. So, let's put in some values:

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
Now, it's time to address a fairly important aspect of databases; BLOBs. A BLOB is an acronym for Binary Large Object, which is an SQL data type for storing large data with no particular size or structure. It can be used to store entire files, images, audio & video clips, and anything else. These next two snippets demonstrate the methods for writing and reading BLOBs in PureBasic.

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
In the above snippet, a new column named PICTURE was first added into the SQLite database table. Then a sample image file from PureBasic's samples folder was opened and read into memory. Finally, the contents of the image file was bound with the query to insert it into the database.

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
Image


And that's about it for this tutorial! :D

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
Texas Instruments TI-99/4A Home Computer: the first home computer with a 16bit processor, crammed into an 8bit architecture. Great hardware - Poor design - Wonderful BASIC engine. And it could talk too! Please visit my YouTube Channel :D
Fred
Administrator
Administrator
Posts: 16581
Joined: Fri May 17, 2002 4:39 pm
Location: France
Contact:

Re: PureBasic SQLite Database: A Quick Tutorial

Post by Fred »

A chance it's a 'Quick' Tutorial ! :lol: . Nice work here !
User avatar
Mijikai
Addict
Addict
Posts: 1360
Joined: Sun Sep 11, 2016 2:17 pm

Re: PureBasic SQLite Database: A Quick Tutorial

Post by Mijikai »

Thank you for the tutorials :)
User avatar
TI-994A
Addict
Addict
Posts: 2512
Joined: Sat Feb 19, 2011 3:47 am
Location: Singapore
Contact:

Re: PureBasic SQLite Database: A Quick Tutorial

Post by TI-994A »

Fred wrote:A chance it's a 'Quick' Tutorial !
A little lengthy, but for clarity's sake. :lol:

Thank you, Fred.
Texas Instruments TI-99/4A Home Computer: the first home computer with a 16bit processor, crammed into an 8bit architecture. Great hardware - Poor design - Wonderful BASIC engine. And it could talk too! Please visit my YouTube Channel :D
User avatar
TI-994A
Addict
Addict
Posts: 2512
Joined: Sat Feb 19, 2011 3:47 am
Location: Singapore
Contact:

Re: PureBasic SQLite Database: A Quick Tutorial

Post by TI-994A »

It's truly my pleasure, Mijikai; thank you.
Texas Instruments TI-99/4A Home Computer: the first home computer with a 16bit processor, crammed into an 8bit architecture. Great hardware - Poor design - Wonderful BASIC engine. And it could talk too! Please visit my YouTube Channel :D
User avatar
Tenaja
Addict
Addict
Posts: 1948
Joined: Tue Nov 09, 2010 10:15 pm

Re: PureBasic SQLite Database: A Quick Tutorial

Post by Tenaja »

Thanks again for your tutorials!
IdeasVacuum
Always Here
Always Here
Posts: 6424
Joined: Fri Oct 23, 2009 2:33 am
Location: Wales, UK
Contact:

Re: PureBasic SQLite Database: A Quick Tutorial

Post by IdeasVacuum »

Top-notch db tutorial TI-994A, nicely explained, anyone can follow it.
IdeasVacuum
If it sounds simple, you have not grasped the complexity.
User avatar
TI-994A
Addict
Addict
Posts: 2512
Joined: Sat Feb 19, 2011 3:47 am
Location: Singapore
Contact:

Re: PureBasic SQLite Database: A Quick Tutorial

Post by TI-994A »

Thank you, Tenaja and IdeasVacuum, for your kind words. :D

A little verbose, perhaps, but I have always found such elaboration easier to understand.
Texas Instruments TI-99/4A Home Computer: the first home computer with a 16bit processor, crammed into an 8bit architecture. Great hardware - Poor design - Wonderful BASIC engine. And it could talk too! Please visit my YouTube Channel :D
User avatar
mk-soft
Always Here
Always Here
Posts: 5313
Joined: Fri May 12, 2006 6:51 pm
Location: Germany

Re: PureBasic SQLite Database: A Quick Tutorial

Post by mk-soft »

Thanks :wink:

Please add an example with memory database...

Code: Select all

r1 = OpenDatabase(#sqlite, ":memory:", "", "")
How to list tables

Code: Select all

r1 = DatabaseQuery(#sqlite, "SELECT * FROM sqlite_master")
My Projects ThreadToGUI / OOP-BaseClass / EventDesigner V3
PB v3.30 / v5.75 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace / OneDrive
User avatar
TI-994A
Addict
Addict
Posts: 2512
Joined: Sat Feb 19, 2011 3:47 am
Location: Singapore
Contact:

Re: PureBasic SQLite Database: A Quick Tutorial

Post by TI-994A »

An annexe to the tutorial, in the form of a simple working example, demonstrating the use of remote MySQL Database Servers with native PureBasic commands:

Code: Select all

UseMySQLDatabase()

Enumeration
  #window
  #list
  #mySql
EndEnumeration

#appName = "Remote MySQL Example"

MessageRequester(#appName, "The connection used in this example is " +
                           "to a public read-only MySQL database from " +
                           "Rfam, which is a scientific organisation " +
                           "providing the latest data on RNA families. " +
                           "(https://docs.rfam.org/en/latest/database.html)")

wFlags = #PB_Window_SystemMenu | #PB_Window_ScreenCentered
OpenWindow(#window, 0, 0, 800, 400, #appName, wFlags)
ListViewGadget(#list, 10, 10, 780, 380)

; connecting to the remote MySQL database using given parameters & credentials
If OpenDatabase(#mySql, "host=mysql-rfam-public.ebi.ac.uk port=4497 dbname=Rfam", "rfamro", "") 
 
  ; querying the first ten records of table > family
  If DatabaseQuery(#mySql, "SELECT * from family LIMIT 10")
   
    ; displaying selected columns from the query results (only columns 0, 3, 5, 33)
    While NextDatabaseRow(#mySql)       
      AddGadgetItem(#list, -1, "A/c No: " + GetDatabaseString(#mySql, 0))
      AddGadgetItem(#list, -1, "Description: " + GetDatabaseString(#mySql, 3))
      AddGadgetItem(#list, -1, "Source: " + GetDatabaseString(#mySql, 5))
      AddGadgetItem(#list, -1, "Updated: " + GetDatabaseString(#mySql, 33))
      AddGadgetItem(#list, -1, "")
    Wend 
   
    FinishDatabaseQuery(#mySql)
   
    success = #True

  EndIf
 
  CloseDatabase(#mySql)
 
EndIf

If Not success
 
  AddGadgetItem(#list, -1, "MySQL Error: " + DatabaseError())
 
EndIf

While WaitWindowEvent() ! #PB_Event_CloseWindow : Wend
Texas Instruments TI-99/4A Home Computer: the first home computer with a 16bit processor, crammed into an 8bit architecture. Great hardware - Poor design - Wonderful BASIC engine. And it could talk too! Please visit my YouTube Channel :D
User avatar
doctorized
Addict
Addict
Posts: 854
Joined: Fri Mar 27, 2009 9:41 am
Location: Athens, Greece

Re: PureBasic SQLite Database: A Quick Tutorial

Post by doctorized »

What about running a second query before finishing the current one?
User avatar
Dreamland Fantasy
Enthusiast
Enthusiast
Posts: 330
Joined: Fri Jun 11, 2004 9:35 pm
Location: Glasgow, UK
Contact:

Re: PureBasic SQLite Database: A Quick Tutorial

Post by Dreamland Fantasy »

@TI-994A

Excellent tutorial, and it came in very handy for a project I'm currently working on. :D

Thanks for doing this.

Kind regards,

Francis
User avatar
TI-994A
Addict
Addict
Posts: 2512
Joined: Sat Feb 19, 2011 3:47 am
Location: Singapore
Contact:

Re: PureBasic SQLite Database: A Quick Tutorial

Post by TI-994A »

Dreamland Fantasy wrote: Fri Sep 16, 2022 8:28 pmExcellent tutorial, and it came in very handy for a project I'm currently working on. :D
Thanks for saying so, Francis. So glad you found it useful. :D
Texas Instruments TI-99/4A Home Computer: the first home computer with a 16bit processor, crammed into an 8bit architecture. Great hardware - Poor design - Wonderful BASIC engine. And it could talk too! Please visit my YouTube Channel :D
User avatar
Dreamland Fantasy
Enthusiast
Enthusiast
Posts: 330
Joined: Fri Jun 11, 2004 9:35 pm
Location: Glasgow, UK
Contact:

Re: PureBasic SQLite Database: A Quick Tutorial

Post by Dreamland Fantasy »

TI-994A wrote: Sun Sep 25, 2022 2:47 pm Thanks for saying so, Francis. So glad you found it useful. :D
You're very welcome. :)

I was initially planning on rolling my own solution for storing data, but as the complexity of the project grew it made more sense to utilise something like the SQLite functionality. Your tutorial was a very good introduction. It didn't cover everything I needed (such as duplication of rows), but it gave me enough of a grounding to get started. Anything else I needed to do I just looked up the https://www.sqlitetutorial.net/ site.

Kind regards,

Francis
Post Reply