It is currently Mon Jan 18, 2021 3:13 pm

All times are UTC + 1 hour




Post new topic Reply to topic  [ 9 posts ] 
Author Message
 Post subject: PureBasic SQLite Database: A Quick Tutorial
PostPosted: Wed Mar 04, 2020 1:04 pm 
Offline
Addict
Addict
User avatar

Joined: Sat Feb 19, 2011 3:47 am
Posts: 2340
Location: Singapore
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:
; 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:
#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:
#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:
#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:
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:
#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:
#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:
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:
#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:
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:
#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:
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:
#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:
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:
#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:
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:
; 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:
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:
; 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:
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:
; 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:
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:
#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:
#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:
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:
#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:
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:
#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:
#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!


Top
 Profile  
Reply with quote  
 Post subject: Re: PureBasic SQLite Database: A Quick Tutorial
PostPosted: Wed Mar 04, 2020 1:08 pm 
Offline
Administrator
Administrator

Joined: Fri May 17, 2002 4:39 pm
Posts: 14181
Location: France
A chance it's a 'Quick' Tutorial ! :lol: . Nice work here !


Top
 Profile  
Reply with quote  
 Post subject: Re: PureBasic SQLite Database: A Quick Tutorial
PostPosted: Wed Mar 04, 2020 1:22 pm 
Offline
Addict
Addict
User avatar

Joined: Sun Sep 11, 2016 2:17 pm
Posts: 803
Thank you for the tutorials :)


Top
 Profile  
Reply with quote  
 Post subject: Re: PureBasic SQLite Database: A Quick Tutorial
PostPosted: Wed Mar 04, 2020 1:26 pm 
Offline
Addict
Addict
User avatar

Joined: Sat Feb 19, 2011 3:47 am
Posts: 2340
Location: Singapore
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!


Top
 Profile  
Reply with quote  
 Post subject: Re: PureBasic SQLite Database: A Quick Tutorial
PostPosted: Wed Mar 04, 2020 1:27 pm 
Offline
Addict
Addict
User avatar

Joined: Sat Feb 19, 2011 3:47 am
Posts: 2340
Location: Singapore
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!


Top
 Profile  
Reply with quote  
 Post subject: Re: PureBasic SQLite Database: A Quick Tutorial
PostPosted: Tue Apr 07, 2020 5:26 pm 
Offline
Addict
Addict
User avatar

Joined: Tue Nov 09, 2010 10:15 pm
Posts: 1719
Thanks again for your tutorials!


Top
 Profile  
Reply with quote  
 Post subject: Re: PureBasic SQLite Database: A Quick Tutorial
PostPosted: Tue Apr 07, 2020 11:24 pm 
Offline
Always Here
Always Here

Joined: Fri Oct 23, 2009 2:33 am
Posts: 6271
Location: Wales, UK
Top-notch db tutorial TI-994A, nicely explained, anyone can follow it.

_________________
IdeasVacuum
If it sounds simple, you have not grasped the complexity.


Top
 Profile  
Reply with quote  
 Post subject: Re: PureBasic SQLite Database: A Quick Tutorial
PostPosted: Wed Apr 08, 2020 5:51 am 
Offline
Addict
Addict
User avatar

Joined: Sat Feb 19, 2011 3:47 am
Posts: 2340
Location: Singapore
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!


Top
 Profile  
Reply with quote  
 Post subject: Re: PureBasic SQLite Database: A Quick Tutorial
PostPosted: Fri Apr 10, 2020 8:08 pm 
Offline
Addict
Addict
User avatar

Joined: Fri May 12, 2006 6:51 pm
Posts: 2808
Location: Germany
Thanks :wink:

Please add an example with memory database...
Code:
r1 = OpenDatabase(#sqlite, ":memory:", "", "")

How to list tables
Code:
r1 = DatabaseQuery(#sqlite, "SELECT * FROM sqlite_master")

_________________
My Projects ThreadToGUI / OOP-BaseClass / OOP-BaseClassDispatch / EventDesigner V3
PB v3.30 / v5.70 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 9 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: robertfern and 25 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye