SQLdb Package/es

The SQLdb package contains FPC units to access a number of SQL databases. It is "packaged" as sqldblaz.lpk in Lazarus, and the components can be found on the "SQLdb" tab.

Componentes

El paquete SQLdb provee los siguientes componentes:

TSQLQuery

Desciende de TDataset y provee los datos como una tabla procedente de la consulta (query) SQL realizada. También puede ser utilizado para ejecutar consultas que no retornan datos.

TSQLTransaction

Encapsula las transacciones en servidor de base de datos. No voy a decir mucho más acerca del mismo dado que tampoco he trabajado demasiado con él. Lo más importante es que tiene los métodos commit y rollback para finalizar o cancelar la transacción en la base de datos.

TXXXConnection

Donde XXX es el tipo de base de datos a la cual se está conectando. Cada uno de estos componentes toma la petición "standard" de los componentes SQLQuery y SQLTransaction y los traduce en peticiones a la base de datos, permitiendo las idiosincracias de la base de datos específica que se está utilizando.

Los componentes actuales son:

• TIBConnection (Borland Interbase / Firebird)
• TODBCConnection (Una conexión ODBC a la base de datos para la cual el PC tiene driver...)
• TOracleConnection (Oracle)
• TMySQL40Connection (MySQL - varias versiones)
• TMySQL41Connection
• TMySQL50Connection
• TPQConnection (PostgreSQL)
• TSQLite3Connection (fpc versión 2.2.2)

Using the SQLdb Package

To use the SQLdb package, you need a TSQLQuery component, a TSQLTransaction component, and one of the connection components.

Here is a quick tutorial:

• Go to the SQLdb tab in Component Palette.
• Add a T___Connection component (suitable for the type of database you are using) to a form, and fill in the appropriate properties to connect to your database. These will vary depending on the actual database. Make sure you can set the "connected" property to true.
• Add a TSQLTransaction component. Return to the Connection component, and set its Transaction Property to the new transaction. This should also set the transaction's database property - check to make sure. You should be able to set active to true.
• Add an SQLQuery component and set its Database and Transaction properties to the components you just added.
• Set the SQLQuery "SQL" property. For a first test, just do a "select * from <tablename>.
• Set active to true. If you can, you have opened your query, and the data is available.

From here on, adding controls is the same as for any database, but just to recap:

• Add a TDatasource component (from the Data Access tab) and set its dataset property to the SQLQuery component
• Add some controls from the Data Controls tab. For a quick check, add a TDBGrid component, and set its datasource property to the Datasource component you just added. You should see the data from the query you entered.

OF course, this is just the beginning, but getting some data in a grid is usually the first step to ensure the database connection is working. More detailed explanations will be found at Working With TSQLQuery.

Notes

More details will be found in SQLdb Programming Reference

The pages related to the SQLdb package were initially written from explorations done using TPQConnection, using a PostgreSQL 8.1 database on Win XP O/S. They are expected to be true for the other databases and OSes as well. If you can confirm this, please leave a note here.

It seems to work similarly on MySQL 5.0.45 on Win XP. TSQLTransaction property 'active' cannot however be set to true, but the live data from the database table are visible in the TDbGrid component already during design of the form and the program compiles.

There are some notes and documentation started: http://z505.com/cgi-bin/powtils/docs/1.6/idx.cgi?file=fcldbnotes

An interface view of the classes is availble: http://z505.com/cgi-bin/powtils/docs/1.6/idx.cgi?file=index-4&unit=sqldb