SQLite/es

This is an old revision of SQLite/es, edited by Jma sp at . The current revision is 91848.

/* TSQLite3Dataset and TSQLiteDataset */

Soporte para SQLite en FPC/Lazarus

SQLite es una base de datos embebida (sin servidor) de simple usuario que puede ser utilizada en aplicaciones FPC y Lazarus. Se pueden utilizar varios drivers para el acceso a SQLite desde programas FPC/Lazarus.

Es necesario que dichos drivers de SQLite (librerias/dll) se ubiquen en el directorio de proyecto y en del ejecutable resultante (y distribuirlos con el ejecutable) para que funcione.

Built-in SQLDB

FPC/Lazarus incluyen componentes SQLDB que aportan soporte para bases de datos SQLitet (TSQLite3Connection), permitiendo por ejemplo crear interfaces gráficos (GUIS) con componentes tales como dbgrids. La ventaja de utilizar SQLDB es que resulta bastante fácil el cambio a otra base de datos diferente como puede ser Firebird o PostgreSQL sin tener por ello que realizar grandes cambios.

Ver debajo para más detalles.

Spatialite support

Spatialite son extensiones GIS para SQLite que se pueden utilizar dentro de SQLDB. Ver Spatialite.

Support for SQLite encryption

En versiones recientes de FPC (implementadas en Marzo de 2012), SQLDB incluye soporte para algunas versiones extendidas de SQLite3 que encriptan la base de datos SQLite mediante el algoritmo AES. Para habilitar la llave (key) de encriptación se tiene disponible la propiedad "password".

Ejemplos:

- 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) binarios disponibles, descarga e.g uno de losbinarios precompilados y renombra SQLite.Interop.dll como sqlite3.dll (si estás utilizando los enlazados estáticamente, presumiblemente necesitas renombrar System.Data.SQLite.DLL como 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. Estado: desconocido.

TSQLite3Dataset y TSQLiteDataset

Existen paquetes diferenciados tales como TSQLiteDataset y TSQLite3Datases; leer más abajo para una descripción sobre como utilizarlos. Para encontrar la API de referencia y más tutoriales puedes visitar la dirección sqlite4fpc homepage.

TSqliteDataset y TSqlite3Dataset son descendientes de TDataset que acceden, respectivamente a bases de datos SQLite2.8.x y 3.x.x. Para nuevos proyectos debería utilizarse presumiblemente TSQlite3Dataset como SQLite 3.x (es la versión actual).

A continuación se muestra un listado de las principales ventajas y desventajas en comparación con otros drivers/métodos de acceso de FPC/Lazarus.

Ventajas:

Disadvantages:

Given the above, many users will use SQLDB or Zeos due to the advantages unless they need lower-level access to the SQLite library

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, PostgreSQL/SQLDB, basically any RDBMS SQLDB supports).

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 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 of the interesting things 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 its 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 project, 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 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 shown.

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 the 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 fulfills 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, just call

ApplyUpdates;

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

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.

Master/detail example

An example of implementing master/detail (customer/orders) using locate can be found here: TSqlite3 Master Detail Example. Implementing master/detail with locate can also be done in SQLDB and probably Zeos.

Remarks

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

See also