How To Open a SQLite connection and use it

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:

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

The resulting code will be:

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.

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:

The resulting code will be:

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.

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.

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

The resulting code will be:

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.

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:

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:

The resulting code will be:

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.

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

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

Verification if DB not exist and create as test2.sqlite

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

SQLite Example 6: manage transactions and determine afected rows

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:

See also