How To Open a SQLite connection and use it

This its a work in progress, Spanish documentation its up to date

This document describe how to connect to any database, using the SQLite software layer that is featured as engine-less file based Data Base, and use widely in software like smart-phones and web browsers.

Overall process

An object of type Connection is created, assigning the necessary properties (database type, and name only), and the method calls Open to open it. When you call Open the database is active and ready to query.

Communication query can be made using the Exec method with the object connection open and active, SQL queries are performed, but there are other more specific methods like Create, Edit or Delete for making SQL to database.

The result are represented as an object Result whose properties depend on the type of database handler Type, such as the affected or returned rows depending on the type of SQL query performed.

The object Result is the one that is finally accessed for the data manage from the database, and it is iterable, that means is crossed according to the amount of rows returned (if it was a SQL DDL) or affected (if it was a SQL DML), in each iteration has an array but where each element is of a own type of specification depend if was queried or manipulated.

Unless you can handle multiple connections, you can locate the code to connect on a module; It is possible to open a call from any part of the project in your classes and modules, but if they use multiple connections in different instances it is advisable to use classes, especially to handle data transfer between different databases need map Database Datatype Mapping

Requirements

  1. You should be sure to have all the database components gb.db installed and all packages marked with Sqlite installed.

  2. This is not an example about SQL statements.

  3. Access to the database and privileges depend on the file permission and sqlite database path.

  4. The component gb.db has been installed and compiled with support for gb.db.sqlite3, and is selected for the project.

Simple SQLite Examples: connect, query, manage

SQLite Example 1: Connecting to a SQLite Database file

This example targets a file database called "test.sqlite" refer to the Connection documentation.

1 Create a new module in your project, named MdbMain.

2 Create the "Main" entry in the module, this will have the code for connection:

       PUBLIC SUB Main()

      END

3 Create the connection code with a variable "$con" of type sqlite3 and the code that configures the properties;

                DIM $con = New Connection
                try $con.Close()             ' Close the connection the try allows it to fail without error 
                $con.Type = "sqlite3"        ' Defines the type of connection 
                $con.Host = Application.path ' Host will be the path where the sqlite db file is 
                $con.Name = "test.sqlite"    ' database name is the name of the database file 
                $con.Open()                  ' We activate and open the connection, the try is to allow an error 

The resulting code will be:

       PUBLIC SUB Main()

                DIM $con AS New Connection
                try $con.Close()             ' Close the connection. The try allows it to fail without error
                $con.Type = "sqlite3"        ' Defines the type of connection
                $con.Host = Application.path ' Host will be the path where the sqlite db file is
                $con.Name = "test.sqlite"    ' database name is the name of the database file
                $con.Open()                  ' We activate and open the connection, the try is to allow an error

                PRINT "Connected? : " & $con.Opened ' It will return TRUE or FALSE depending on whether "try $con.Open()" was successful in "conectarodbc()"
       END

This will print TRUE or FALSE depending on the success of the connection, as the documentation states in ODBC .

4 Verifications:

If cannot see it the "T" or " " must or can test if the file is inside the project directory, the Host property defines where it will search and use the file of the database:

If something is wrong, you can also use the SQLite software and verify if the file is inside the project root directory.

Please refer to the Connection documentation for further major details.

  1. Note that here our the connection object $con is not handled globally in the whole module.

  2. Note that the Name property defines the file name database, consult Connection documentation for further major details.

  3. For the purposes of this example, a database file called "test.sqlite" exists inside the root project directory and it is used in the Host

SQLite Example 2: Execute a simple DDL SQL Creation Statement

For the purposes of this example, the previous steps are assumed and a file database named "test.sqlite" is inside root project the code from ODBC example 1 above is used

5 Using the module and the previous connection code, insert in the process "Main" *before* the "PRINT" statement query:

       $con.Exec("CREATE TABLE IF NOT EXISTS tabla1 ( columna1 TEXT, columna2 TEXT)")

The resulting code will be:

       PUBLIC SUB Main()

                DIM $con AS New Connection
                try $con.Close()             ' Cierra la conexion el try permite falle sin error
                $con.Type = "sqlite3"        ' Define el tipo de Conexion
                $con.Host = Application.path ' Host sera la ruta en donde esta el archivo de la db sqlite
                $con.Name = "test.sqlite"    ' nombre de la base de datos es el nombre del archivo base de datos
                $con.Open()                  ' Activamos y Abrimos la conexion, el try es para que permita un error

                $con.Exec("CREATE TABLE IF NOT EXISTS tabla1 ( columna1 TEXT, columna2 TEXT)")
                PRINT "Conectado? : " & $con.Opened ' Devolvera TRUE o FALSE segun si "try $con.Open()" fue exitoso en "conectarodbc()"
       END

This will create a new table in the database, regardless of whether the table already exists or not, by the "IF NOT EXISTS" part, but it will not guarantee the same columns if the existing table is different.

  1. Note that here we execute a statement and we do not worry about the actual obtaining of the result in the handle.

  2. For the purposes of this example, a database file called "test.sqlite" exist inside the root project directory and it is used in the Host

  3. Gambas has a special type of treatment of the query results, represented in a object which is called Result, see next.

SQLite Example 3: Execute a simple data manipulation or SQL DML statement

For the purposes of this example, the previous steps are assumed and a file database named "test.sqlite" is inside root project the code from ODBC example 2 above is used Gambas has a special type of treatment with the query results, which is called Result.

6 Define a new variable in the main thread "rs" of type Result, which abstracts the handling of data with respect to results.

       DIM rs AS Result

7 Using the module and the previous connection code, insert in the "Main" process *after* the "PRINT" statement the following code:

       rs = $con.Exec("INSERT INTO tabla1 ( columna1, columna2) VALUES ( 'pepe', 'pablo')")
       PRINT "registro insertados: " & rs.Count

The resulting code will be:

       PUBLIC SUB Main()

         DIM rs AS Result
         DIM $con AS New Connection

           try $con.Close()          ' Cierra la conexion el try permite falle sin error
            $con.Type = "sqlite3"        ' Define el tipo de Conexion
            $con.Host = Application.path ' Host sera la ruta en donde esta el archivo de la db sqlite
            $con.Name = "test.sqlite"    ' nombre de la base de datos es el nombre del archivo base de datos
            $con.Open()                  ' Activamos y Abrimos la conexion, el try es para que permita un error

           $con.Exec("CREATE TABLE IF NOT EXISTS tabla1 ( columna1 TEXT, columna2 TEXT)")
           PRINT "Conectado? : " & $con.Opened ' Devolvera TRUE o FALSE segun si "try $con.Open()" fue exitoso en "conectarodbc()"

           rs= $con.Exec("INSERT INTO tabla1 ( columna1, columna2) VALUES ( 'pepe', 'pablo')")
           PRINT "registro insertados: " & rs.Count
  
     END

This will create a new record in the newly created table, regardless of whether the table exists or if the record exists, since a primary key was never defined. The output will show "0" in old Gambas versions up to 3.7.1 where the gb.db.sqlite3 module was rewritten.

In SQLite, data blob types are manage different respect others DBMS, please refers to Database Datatype Mapping to property use of.

  1. The property Count get rows count only if are a SQL DML, for rest of connection types retrieve TRUE as (-1) o FALSE

  2. The Result object handles the results, if there affected registers or not independent of Count result.

  3. For the purposes of this example, a database file called "test.sqlite" exist inside the root project directory and it is used in the Host

SQLite Example 4: Get a resulset data manipulation or SQL DML statement

For the purposes of this example, the previous steps are assumed and a file database named "test.sqlite" is inside root project the code from ODBC example 3 above is used Gambas has a special type of treatment with the query results, which is called Result.

7 Using the module and the previous connection code, insert in the process "Main" *after* the last "PRINT" statement the code:

       rs = $con.Exec("SELECT columna1, columna2 FROM tabla1 ")

7 This gets the records query on the object Result, to access each row and its columns is iterated on it with the following code:

       rs = $con.Exec("SELECT columna1, columna2 FROM tabla1 ")
       WHILE (rs.Available)
             PRINT "registro encontrado: valor columna1:" & rs!columna1 & ", valor columna2:" & rs["columna2"]
             rs.MoveNext()
       WEND

The resulting code will be:

       PUBLIC SUB Main()

         DIM rs AS Result
         DIM $con AS New Connection

           try $con.Close()          ' Cierra la conexion el try permite falle sin error
            $con.Type = "sqlite3"        ' Define el tipo de Conexion
            $con.Host = Application.path ' Host sera la ruta en donde esta el archivo de la db sqlite
            $con.Name = "test.sqlite"    ' nombre de la base de datos es el nombre del archivo base de datos
            $con.Open()                  ' Activamos y Abrimos la conexion, el try es para que permita un error

           $con.Exec("CREATE TABLE IF NOT EXISTS tabla1 ( columna1 TEXT, columna2 TEXT)")
           PRINT "Conectado? : " & $con.Opened ' Devolvera TRUE o FALSE segun si "try $con.Open()" fue exitoso en "conectarodbc()"

           rs= $con.Exec("INSERT INTO tabla1 ( columna1, columna2) VALUES ( 'pepe', 'pablo')")
           PRINT "registro insertados: " & rs.Count

         rs = $con.Exec("SELECT columna1, columna2 FROM tabla1 ")
         WHILE (rs.Available)
               PRINT "registro encontrado: valor columna1:" & rs!columna1 & ", valor columna2:" & rs["columna2"]
               rs.MoveNext()
         WEND
  
     END

This will create a new record in the newly created table, regardless of whether the table exists or if the record exists, since a primary key was never defined. The output will show "0" in old Gambas versions up to 3.7.1 where the gb.db.sqlite3 module was rewritten.

NOTE : This will create another record in the newly created table, regardless of whether the table exists or not, or if the record exists, since a primary key was never defined.

In SQLite, data blob types are manage different respect others DBMS, please refers to Database Datatype Mapping to property use of.

  1. The property Count brings quantities when it is a SQL DML, otherwise it only returns TRUE as (-1) or FALSE

  2. The object Result has the result, if there are rows inserted/affected or not using Count.

  3. For the purposes of this example, a database file called "test.sqlite" exist inside the root project directory and it is used in the Host

See also

Advanced SQLite examples: transform data, procedures, manage data and handle errors

See the spanish documentation for Advanced ODBC examples for procedures, data manipulation and handle errors.

SQLite Example 5: Create a database if no exist

Due SQlite are file based access, the database can create on demand by the user that currently runs the process and will have the same permission.

For the purposes of this example, the previous steps are assumed and a file database named "test2.sqlite" will be created if not exist.

So lest move the main code, separate the access process to a subroutine, so in the main function routine remove the connection process, the $con will be a global variable, and the connection process in a subroutine below of the Main routine:

main module code

PUBLIC $con AS Connection

       PUBLIC SUB Main()

         DIM rs AS Result

           getConn()      ' llamada a la rutina que abrira la conexcion

           $con.Exec("CREATE TABLE IF NOT EXISTS tabla1 ( columna1 TEXT, columna2 TEXT)")
           PRINT "Conectado? : " & $con.Opened ' Devolvera TRUE o FALSE segun si "try $con.Open()" fue exitoso en "conectarodbc()"

           rs= $con.Exec("INSERT INTO tabla1 ( columna1, columna2) VALUES ( 'pepe', 'pablo')")
           PRINT "registro insertados: " & rs.Count

         rs = $con.Exec("SELECT columna1, columna2 FROM tabla1 ")
         WHILE (rs.Available)
               PRINT "registro encontrado: valor columna1:" & rs!columna1 & ", valor columna2:" & rs["columna2"]
               rs.MoveNext()
         WEND
  
     END

sub routine creation code: change the db name to "test2.sqlite"

Private Sub getConn()
    
       $con = New Connection
           try $con.Close()          ' Cierra la conexion el try permite falle sin error
            $con.Type = "sqlite3"        ' Define el tipo de Conexion
            $con.Host = Application.path ' Host sera la ruta en donde esta el archivo de la db sqlite
            $con.Name = "test2.sqlite"    ' nombre de la base de datos es el nombre del archivo base de datos
       try $con.Open()                  ' Activamos y Abrimos la conexion, el try es para que permita un error

       If Not IsNull($con) Then
           If $con.Opened Then
              $conopen = true
           Else
              $conopen = false
              Print "Could not connect, error I/O or something!"
              Quit
           Endif
       Endif
    
End

Verification if DB not exist and create as test2.sqlite

'verificating if db exist
IF Not Exist(Application.path & "/test.sqlite") THEN
    'create test.sqlite
    $con = New Connection    ' borramos toda anterior configuracion con creacion nueva instancia
    $con.Type = "sqlite3"    ' Define el tipo de Conexion
    $con.Host = Application.path ' Host sera la ruta en donde esta el archivo de la db sqlite
    $con.Name = ""           ' si esta en vacia se usa una temporal para usarla como db base
    $con.Open
    $con.Databases.Add("test2.sqlite")
    $con.Close
ENDIF 

Now change the db name and use test2; final code for:

PUBLIC $con AS Connection

   PUBLIC SUB Main()

      DIM rs AS Result

        getConn()      ' llamada a la rutina que abrira la conexcion

        $con.Exec("CREATE TABLE IF NOT EXISTS tabla1 ( columna1 TEXT, columna2 TEXT)")
        PRINT "Conectado? : " & $con.Opened ' Devolvera TRUE o FALSE segun si "try $con.Open()" fue exitoso en "conectarodbc()"

        rs= $con.Exec("INSERT INTO tabla1 ( columna1, columna2) VALUES ( 'pepe', 'pablo')")
        PRINT "registro insertados: " & rs.Count

        rs = $con.Exec("SELECT columna1, columna2 FROM tabla1 ")
         WHILE (rs.Available)
               PRINT "registro encontrado: valor columna1:" & rs!columna1 & ", valor columna2:" & rs["columna2"]
               rs.MoveNext()
         WEND
  
   END

   Private Sub getConn()
    
      IF Not Exist(Application.path & "/test.sqlite") THEN
          $con = New Connection         ' borramos toda anterior configuracion con creacion nueva instancia
          $con.Type = "sqlite3"         ' Define el tipo de Conexion
          $con.Host = Application.path  ' Host sera la ruta en donde esta el archivo de la db sqlite
          $con.Name = ""                ' si esta en vacia se usa una temporal para usarla como db base
          $con.Open                     ' como name es vacio siemrpe abrira la db en memoria
          $con.Databases.Add("test2.sqlite") '  esto creara una db neuva en la ruta definida pro "host"
          $con.Close
      ENDIF 
       $con = New Connection
           try $con.Close()              ' Cierra la conexion el try permite falle sin error
            $con.Type = "sqlite3"        ' Define el tipo de Conexion
            $con.Host = Application.path ' Host sera la ruta en donde esta el archivo de la db sqlite
            $con.Name = "test2.sqlite"    ' nombre de la base de datos es el nombre del archivo base de datos
       try $con.Open()                   ' Activamos y Abrimos la conexion, el try es para que permita un error

       If Not IsNull($con) Then
           If Not $con.Opened Then
              $conopen = false
              Print "Could not connect, error I/O or something!"
              Quit
           Endif
          $conopen = true
       Endif
    
   End

  1. For the purposes of this example, a database file "test2.sqlite" do not exist inside the root project directory and it is used in the Host

  2. To see if can create or not, be sure or delete it the database file name from project root directory and run the code.

SQLite Example 6: manage transactions and determine afected rows

See the spanish documentation for Advanced ODBC examples for procedures, data manipulation and handle errors.

SQLite Example 7: Handling huge amount of rows in grid objects

Fil a grid row by row can be slow, there a "on deman" way to fill thousand of row in a grid, using the Data event and a global Result object from the database:

PUBLIC SUB GridView1_Data(Row AS Integer, Column AS Integer)
  $res.moveTo(row)                                                 ' using global $res Result object with MoveTo a specific row
  GridView1.Data.text = Str($res[GridView1.Columns[column].text])  ' setting the content in current grid are equial to the current row position
END

See also