Computing: Free Pascal Programming

Using SQLite databases with Lazarus/Free Pascal.

SQLite is the most used, embedded (serverless) relational database in the world. It is open source and free to use. SQLite runs entirely in the application that uses it, and stores all of its durable objects in a single disk file. For information about the installation of SQLite and the creation of the "world" database used in this tutorial, please have a look at Web development environment setup on MS Windows: SQLite databases.

The following tutorial is about using SQLite databases from within a Lazarus/Free Pascal application. It covers the basics, but I think, that's enough to know (at least for beginners) in order to be able to transfer data between a database and your program. Please, note that the tutorial examples have been build and tested on Windows 10, using Lazarus 2.2.6 64bit (with FPC 3.2.2); the database software used has been SQLite 3.44.2.0 64bit. The tutorial is based on my lots more complete tutorial Using MySQL databases with Lazarus/Free Pascal, and only covers the connection to a SQLite database, read and write operations from resp. to an SQLite database being essentially the same as with MySQL (at least I suppose so...).

The most common method to connect to SQLite from a Lazarus application is to use a TSQLite3Connection object, included with the default installation files of Lazarus/Free Pascal. Another way to connect to SQLite (or other databases) is the so called Microsoft Open Database Connectivity (ODBC), an interface that makes it possible for applications to access data from a variety of database management systems. And Lazarus includes the TODBCConnection object, that you need to do so.

The following document shows, how to implement a simple Lazarus application, reading and displaying the number of cities in the "world" database (cf. my Installation of SQLite tutorial), first, using TSQLite3Connection and second, using TODBCConnection. It is supposed that you are familiar with Lazarus and Free Pascal. The tutorial details are limited to the SQLite specific aspects. Some explanations (such those concerning the installation of the ODBC drivers) apply to MS Windows only. Click the following link to download the Lazarus/Free Pascal source code of this application.

Create a Lazarus application project with the form shown on the screenshot below. Save it as SQLite.lpi and then re-save it as SQLite2.lpi. The TSQLite3Connection respectively TODBCConnection specific controls will be added, as explained in the paragraphs below.

Using SQLite with a TSQLite3Connection.

The components allowing access to a variety of databases may be found in the Lazarus SQLdb menu. There are 3 of them, that we need to connect to SQLite: TSQLite3Connection. These are components, that take the requests of the TSQLQuery and TSQLTransaction components and translate them into requests specifically tailored for the database that you are using. TSQLTransaction. This encapsulates the transaction with the database. A TSQLite3Connection object always needs at least one TSQLTransaction associated with it, so that the transaction of its queries is managed. TSQLQuery. This is a descendant of TDataset, and provides the data as a table from the SQL query that you submit. It can also be used to execute SQL queries (e.g. INSERT INTO, stored procedures...).

Add theses 3 components to the form as you do with any other items from the Lazarus menus. I renamed them to dbSQLiteConnection, dbSQLTransaction and dbSQLQuery respectively. Concerning the TSQLite3Connection object, I'll include the database specific information (in particular the database name) within the code. In the dbSQLiteConnection property sheet, I just set "Transaction" to the name of the transaction object ("dbSQLTransaction").

Similarly, I'll include the SQL statements within the code, just setting the "Transaction" property of the TSQLQuery object in the property sheet. Doing so, its "Database" property should be automatically set to the name of the TSQLite3Connection object's name ("dbSQLiteConnection"). The "Database" property of the TSQLTransaction object should now also be set to the name of the TSQLite3Connection object's name.

Connecting to the database.

To connect to SQLite by pushing the "Connect" button, use the following code within your TfSQLite.btConnectClick method (fSQLite being the name of my form, btConnect the name of the button):

procedure TfSQLite.btConnectClick(Sender: TObject);     begin         if dbSQLiteConnection.Connected then             dbSQLiteConnection.Close;         // Set the connection parameters         dbSQLiteConnection.UserName := ''; dbSQLiteConnection.Password := '';         dbSQLiteConnection.DatabaseName := 'C:\Programs\SQLite\world.db';         // Connect to the world database         try             dbSQLiteConnection.Open;             edMess.Text := 'Connection to SQLite database "world" = OK!';         except             on E: ESQLDatabaseError do                 edMess.Text := E.Message;         end;     end;

Note that C:\Programs\SQLite\world.db is the full path to the SQLite "world" database on my system, and you have possibly to adapt it!

If you run the application and try to connect, you may get the error message, shown on the screenshot below. This error condition corresponds to a situation, where the SQLite client library cannot be loaded. This probably means that sqlite3.dll cannot be found; in fact you have to copy it to the directory that contains the Free Pascal executable (during development, this corresponds to your project's output directory). Where do we find this DLL? In the directory, where you placed it when setting up SQLite; in the case of a 64bit operating system, this is normally C:\Windows\SysWOW64.

After having copied the sqlite3.dll to your project output directory and re-running the application, the connection with the database should succeed.

Disconnecting from the database.

The disconnection from the database is coded within the TfSQLite.btExitClick method (activated when pushing the "Exit" button):

procedure TfSQLite.btExitClick(Sender: TObject);     begin         if dbSQLiteConnection.Connected then             dbSQLiteConnection.Close;         Close;     end;

Querying the number of cities.

The number of cities in the "city" table of the "world" database should be read from the SQLite database when the "Query" button is pushed, so has to be coded within the TfSQLite.btQueryClick method. To define the SQL statement, to query the database, as well as to retrieve the data, that SQLite returns, we will use properties and methods of the TSQLQuery object. In particular, dbSQLQuery.SQL.Text to define the SQL SELECT.

procedure TfSQLite.btQueryClick(Sender: TObject);     var         Count: Integer;     begin         if dbSQLiteConnection.Connected then begin             // Query the database             dbSQLQuery.SQL.Text := 'SELECT count(*) FROM city';             try                 dbSQLQuery.Open;                 if dbSQLQuery.EOF then                     Count := 0                 else                     Count := dbSQLQuery.Fields[0].AsInteger;                 dbSQLQuery.Close;                 // Display the query result                 edCities.Text := IntToStr(Count);             except                 on E: ESQLDatabaseError do                     edMess.Text := E.Message;             end;         end;     end;

The screenshot below shows a successful SELECT, with the display of the number of records in the "city" table.

Most relational databases are server-client based, i.e. a database is composed of several files that are managed by the server, that transfers the results of the query submitted by a client to this client. Embedded databases like SQLite (or the embedded version of of Firebird) run within the application, the database being a single file stored within the filesystem, but having its own special format, making data retrieval, based on given selection criteria, lots faster than trying to do so with regular files. If you look at the connection properties that we set for the SQLite database, you see that there is no host and port, as with MySQL and similar, but just the full path to the unique file that makes up the SQLite database. An important thing to know about SQLite is the fact that if you connect to an SQLite database that doesn't exist, the corresponding file is (by default) created (and so, an empty database is now available). The screenshots below show two situations, where I used a wrong full path when connecting with SQLite. On the screenshot on the left, I specified a non-existing directory (e.g. C:\Programs\SQLite3\world.db). The result was that SQLite couldn't find the database and the ESQLDatabaseError exception of the TfSQLite.btConnectClick method was thrown when I pushed the "Connect" button. On the screenshot on the right, I used a correct path, but a non-existing filename (e.g. C:\Programs\SQLite\word.db). The result was that SQLite created a new database called "word.db", thus the message that the connection succeeded. Then, when I pushed the "Query" button, the ESQLDatabaseError exception of the TfSQLite.btQueryClick method was thrown. The new database is empty, thus there is no "city" table to retrieve data from.

This automatic creation of a new database if there is no file with the name specified when opening a connection may not be what you want; I personally prefer that in this case an exception is thrown, just as for an invalid path. This can be realized by changing the openFlags property of the TSQLite3Connection from [sofReadWrite, sofCreate] to [sofReadWrite]; the simplest way to do so is to set openFlags > sofCreate = False in the property sheet of the TSQLite3Connection object.

Using SQLite with a TODBCConnection.

ODBC (Open Database Connectivity) is a technology that allows one to connect to a whole variety of databases using a single API, the ODBC API. There are ODBC implementations for various platforms and there are drivers for most Database Management Systems. Official ODBC documentation can be found at the MSDN ODBC site.

Before you can connect to your database using ODBC, you need to install An ODBC Driver Manager. An ODBC driver specific to the DBMS that you want to connect to.

ODBC Driver Manager and SQLite ODBC driver.

MS Windows includes the ODBC Data Source Administrator, a built in ODBC Driver Manager, which allows DSNs to be set up, and other configuration. You can find it in Control Panel > Administrative Tools. There are two ODBC Driver Managers available: one for 32bit and one for 64bit data sources.

Open ODBC Data Sources (64bit) and choose the Drivers tab. You can see several drivers listed, but none of them is for being used with SQLite, thus, we'll have to find some and install it now. The one that I use and that works fine with Lazarus is developed by Christian Werner Software & Consulting. Click the following link to download the current version of their SQLite ODBC driver. It's for SQLite 3 and is freeware. The driver, that I used when writing this tutorial, is the 64bit driver, version 1.34455. During installation, you can choose to install support for SQLite 2, what is normally not needed.

After installation is terminated, the driver will be listed with the other ones in the Drivers tab of Windows ODBC data source administrator; its name is SQLite3 ODBC Driver (screenshot on the left). The driver setup program also automatically creates a System DSN with name SQLite3 Datasource (screenshot on the right).

Connecting to an ODBC data source.

The parameters for connecting to an ODBC data source are described in a connection string. This is a string of the form: NAME=VALUE;NAME=VALUE...

An ODBC driver manager provides alternative ways to make DSN shortcuts for a set of parameters: DSN (DataSource Name): a system or user defined data source, identified by a (unique) name. DSNs can be configured using the ODBC Data Source Administrator (or by manually editing the odbc.ini file or the registry). File DSN: a file which contains the connection parameters. The parameters in a DSN or File DSN can always be combined with additional parameters in the connection string (for example to specify a password).

By setting up a DSN, the named DSN is all, that is needed, to use the connection. The connection details are thus decoupled from the applications, as only the DSN name is used – the DSN acting as a go-between.

As we saw above, the ODBC driver setup program did also create a system DSN for us. In ODBC Data Source Administrator, choose the System DSN tab and click the Configure button to view its properties.

I let the all defaults, i.e. all checkboxes unselected. If you created your "world" database with foreign keys (I actually didn't), maybe that this option has to be selected here. You may also want to disable database creation during the connection to a database file that doesn't yet exist (as we will see, we can also do that in the Lazarus application).

Using SQLite with a TODBCConnection.

To use SQLite with a TODBCConnection, add the following components to your form: TODBCConnection, TSQLTransaction and TSQLQuery. Set the "Database" property of the TSQLTransaction object to the name of the TODBCConnection object (I named it "dbODBCConnection") and the "Transaction" property of the TSQLQuery object to the name of the transaction ("dbSQLTransaction"); the "Database" property of this object should be automatically set to "dbODBCConnection".

TODBCConnection provides a wrapper around the ODBC data source connection string. Some of its properties are mapped to name-value pairs in the connection string, the other ones can be specified in the "Params" property (TStrings variable).

TODBCConnection properties to ODBC connection string parameters mapping:

Property Type Connection string parameter Driver string DRIVER (name of the ODBC driver) DatabaseName string DSN (name of the DSN, as defined in Windows ODBC DataSource Administrator) FileDSN string FILEDSN (name of the FileDSN, as defined in Windows ODBC DataSource Administrator) HostName string none (there is no corresponding parameter in the ODBC standard; may however be set in Windows ODBC DataSource Administrator) UserName string UID (database user name) Password string PWD (database user password) Params TStrings Used to specify custom parameters. Each item must be of the form NAME=VALUE (see below)

Please, note that the property DatabaseName must be set to the name of the DSN and not the one of the database, that you want to connect to, in our example to "SQLite3 Datasource". The driver has been defined in the DSN (see above), a host is not needed with an embedded database. The only properties among those listed above, that we may set are UserName and Password (both equal to an empty string), then specify the name of the database as element of the Params property TStrings.

Another parameter that can be set using Params is NOCREAT. Setting it to "true" disables the automatic database creation if the file specified does not exist.

To connect to SQLite by pushing the "Connect" button, use the following code within your TfSQLite2.btConnectClick method (fSQLite2 being the name of my form, btConnect the name of the button). The disconnection from SQLite may be coded as part of the TfSQLite2.btExitClick method.

procedure TfSQLite2.btConnectClick(Sender: TObject);     begin         if dbODBCConnection.Connected then             dbODBCConnection.Close;         // Set the connection parameters         dbODBCConnection.DatabaseName := 'SQLite3 Datasource';         dbODBCConnection.UserName := ''; dbODBCConnection.Password := '';         dbODBCConnection.Params.Add('DATABASE=C:\Programs\SQLite\world.db');         dbODBCConnection.Params.Add('NOCREAT=true');         // Connect to the world database         try             dbODBCConnection.Open;             edMess.Text := 'Connection to SQLite database "world" = OK!';         except             on E: ESQLDatabaseError do                 edMess.Text := E.Message;         end;     end;

procedure TfSQLite2.btExitClick(Sender: TObject);     begin         if dbODBCConnection.Connected then             dbODBCConnection.Close;         Close;     end;

To read the number of cities from the "city" table in the "world" database, just use the TSQLQuery object and the code described with the TSQLite3Connection object (changing the name of the connection, of course).

The screenshot on the left shows a successful SELECT, the one on the right corresponds to a situation where I used a wrong database name (e.g. C:\Programs\SQLite\word.db). Note, that without dbODBCConnection.Params.Add('NOCREAT=true'), a new empty database would have been created (instead of an exception being thrown).

Note: The connection to the SQLite database being established, you can perform SELECT, INSERT, UPDATE and DELETE operations in the same (or a similar) way, as you would do with MySQL. For those, who haven't any experience with SQL databases and who need further help to develop Lazarus/Free Pascal applications, using a (SQLite) database: Have a look at my Reading data from a MySQL database and Writing data to a MySQL database tutorials. As I never worked with SQLite myself, that things are the same as with MySQL is more a supposition than being sure that my MySQL programs work without greater changes with SQLite. I also haven't any idea if SQLite correctly handles non ANSI characters (UTF8) by default...

---

If you find this text helpful, please, support me and this website by signing my guestbook.