SQLite

This is an old revision of SQLite, edited by BigChimp at . The current revision is 81006.

/* Support for SQLite encryption */ Clarified (windows) binaries can be built using sqlcipher

SQLite and FPC/Lazarus support

SQLite is an embedded (non-server) single-user database that can be used in FPC and Lazarus applications. Various drivers can be used to access SQLite from FPC/Lazarus programs. All drivers do need the SQLite library/dll in the project directory and executable directory (and distributed with your executable) in order to work.

Built-in SQLDB

FPC/Lazarus offers the built-in SQLDB components that include support for SQLite databases, which allow you to e.g. create GUIs with database components such as dbgrids. The advantage of using SQLDB is that it is fairly easy to change to a different databse such as Firebird or PostgreSQL without changing your program too much. See below for details.

Spatialite support

Spatialite are GIS extensions to SQLite which you can use from within SQLDB. See Spatialite.

Support for SQLite encryption

In recent FPC versions (implemented March 2012), SQLDB included support for some extended versions of SQLite3 which encrypt the SQLite database file using the AES algorithm. Use the password property to set the encryption key.

Examples:

- SQLCipher : open source, e.g. Windows binaries not for free (you have to compile them yourself)

- System.Data.SQLite : open source, Windows (32, 64, CE) binaries available, rename System.Data.SQLite.DLL to sqlite3.dll

- wxSQLite3 : open source, some binaries for Linux available (ex: https://launchpad.net/ubuntu/oneiric/+package/libwxsqlite3-2.8-0)

Zeos

Zeos

SQLitePass

SqlitePass components. Status: unknown.

TSQLite3Dataset and TSQLiteDataset

There are also separate TSQLiteDataset and TSQLite3Dataset packages; see below for a description on how to use them. Visit the sqlite4fpc homepage to find the API reference and more tutorials.

TSqliteDataset and TSqlite3Dataset are TDataset descendants that access, respectively, 2.8.x and 3.x.x sqlite databases. For new projects, you would presumably use TSQlite3Dataset as SQLite 3.x is the current version.

Below is a list of the principal advantages and disadvantages compared to other FPC/Lazarus SQLite drivers/access methods:

Advantages:

Disadvantages

Using the SQLdb components with SQLite

These instructions are focused on SQLDB (the TSQLite3Connection) specifics for SQLite. For a general overview, have a look at SqlDBHowto which has some useful information about the SQLdb components.

See SQLdb_Tutorial1 for a tutorial on creating a GUI database-enabled program that is written for SQLite/SQLDB (as well as for Firebird/SQLDB).

We will use a combination of three components from the Lazarus SQLdb tab: TSQLite3Connection, TSQLTransaction and TSQLQuery. The TSQLQuery acts as our TDataset; in the simplest case it just represents one of our tables. For the sake of simplicity: make sure you already have an existing SQLite database file and don't need to create a new one one now. TSQLite3Connection can be found in the sqlite3conn unit, if you want to declare it yourself or are working in FreePascal.

The three components are connected with each other as usual: In the TSQLQuery set the properties Database and Transaction, in the TSQLTransaction set the property Database. There is not much to do in the Transaction and Connection components, most interesting stuff will be done in the TSQLQuery. Configure the components as follows:

TSQLite3Connection:

Note: To set the full library path (if you place your sqlite dll/so/dylib in a place where the OS won't find it, like the application directory on Linux/OSX), you can set the SQLiteLibraryName property (BEFORE any connection is established e.g. in the OnCreate event of the main form), like this:

SQLiteLibraryName:='./sqlite3.so';

TSQLQuery:

SELECT * FROM foo

After the above is all set up correctly you should now be able to use the TSQLQuery like any other TDataset, either by manipulating it's data programmatically or by placing a TDatasouce on the Form, connecting it to the TSQLQuery and then using data contols like TDBGrid etc.

Tips

Using TSQLite3Dataset

This section details how to use the TSQLite3Dataset and TSQLite3Dataset components to access SQlite databases. by Luiz Américo luizmed(at)oi(dot)com(dot)br

Requirements

Before initiating a lazarus projects, ensure that:

How To Use (Basic Usage)

Install the package found at /components/sqlite directory (see instructions here)

At design time set the following properties:

Creating a Table (Dataset)

Double click in the component icon or use the 'Create Table' item of the popup menu that appears when clicking the right mouse button. A simple self-explaining table editor will be show.

Here are all field types supported by TSqliteDataset and TSqlite3Dataset:

Retrieving the data

After creating the table or with a previously created Table, open the dataset with Open method. If the SQL property was not set then all records from all fields will be retrieved, the same if you set the SQL to:

SQL := 'Select * from TABLENAME';

Applying changes to the underlying datafile

To use the ApplyUpdates function, the dataset must contain at least one field that fills the requirements for a Primary Key (values must be UNIQUE and not NULL)

It's possible to do that in two ways:

If one of the two conditions is set then just call

ApplyUpdates;

PS1: If both conditions are set, the field corresponding to PrimaryKey is used to apply the updates.

PS2: Setting PrimaryKey to a field that is not a Primary Key will lead to loss of data if ApplyUpdates is called, so ensure that the chosen field contains not Null and Unique values before using it.

Remarks

Generic examples can be found at fpc/fcl-db/src/sqlite SVN directory

See also