SqlDBHowto/es

│ Deutsch (de) │ English (en) │ español (es) │ français (fr) │ 日本語 (ja) │ Nederlands (nl) │ polski (pl) │ 中文（中国大陆）‎ (zh_CN) │

Introducción

   Este texto se configura como un "cómo-hacer". Pretende responder a una serie de preguntas una por una, explicando cómo se pueden utilizar las distintas clases. Todas estas cuestiones puestas una detrás de otra forman una especie de tutoría.

   Voy a tratar de esccribir de tal manera que el texto pueda ser utilizado para Lazarus, así como FreePascal. Sin embargo, los ejemplos son para FreePascal (es decir, son aplicaciones de consola)

¿Cómo conectarse a un servidor de base de datos?

   SQLdb no se conecta a un servidor de base de datos directamente sino que utiliza el cliente correspondiente al servidor de base de datos utilizado. SQLdb envía los comandos de la librería de cliente, la cual se conecta a la base de datos y le pasa los comandos. Esto significa que una librería de cliente debe ser instalada en el equipo para establecer una conexión con una base de datos. En Windows un cliente suele ser una .dll, bajo Linux un .so y bajo OS/X una .dylib.

   Cuando la librería de cliente se ha instalado correctamente puede conectarse a un servidor de base de datos utilizando un componente TSQLConnection. Hay disponibles componentes TSQLConnection para diferentes servidores de base de datos. Por ejemplo, utiliza TIBConnection para conectarse a una base de datos Firebird/Interbase; TPQConnection para PostgreSQL y TMySQL40Connection, TMySQL41Connection y TMySQL50Connection para bases de datos MySQL con número de versión 4.0, 4,1 y 5,0 respectivamente.

   Las diferencias entre las versiones de cliente MySQL son grandes, en la medida que los clientes y las conexiones no se pueden intercambiar. Con un cliente MySQL versión 4.1 es instalado, tienes que utilizar un TMySQL41Connection. Esto no está relacionado con el servidor MySQL.

   Aunque los detalles difieren entre las distintas base de datos, por lo general hay que establecer cuatro propiedades para conectarse a un servidor de base de datos: el nombre de la base de datos, el nombre del servidor o dirección IP, el nombre de usuario y la contraseña. Cuando estas propiedades se establecen, se puede crear una conexión con el método open. Si falla la conexión se lanza una excepción EDatabaseError. Utiliza la propiedad connected para comprobar si la conexión se ha establecido con el servidor de base de datos. Utiliza el método close para finalizar la conexión con el servidor.

<delphi>Program ConectaDB

var UnaConexion : TSQLConnection;

Procedure CrearConexion;
begin
  UnaConexion := TIBConnection.Create(nil);
  UnaConexion.Hostname := 'localhost';
  UnaConexion.DatabaseName := '/opt/firebird/examples/employee.fdb';
  UnaConexion.UserName := 'sysdba';
  UnaConexion.Password := 'contranyamaestra';
end;

begin
  CrearConexion;
  UnaConexion.Open;
  if UnaConexion.Connected then
    writeln('¡Conexión realizada!')
  else
    writeln('¡Imposible!, porque si la conexión falla, '+
              'una excepción debe ser lanzada, por lo que este código no '+
               'será Ejecutado');
  UnaConexion.Close;
  UnaConexion.Free;
end.</delphi>

   Si se produce una excepción, lea atentamente el mensaje de error. Tal vez el servidor de base de datos no está funcionando, el nombre de usuario o la contraseña es incorrecta o el nombre o la dirección IP de base de datos estan escritos de forma incorrecta. Si el mensaje de error indica que la librería cliente no puede ser encontrada, comprueba si el cliente está instalado correctamente. A menudo, el mensaje de error muestra literalmente el nombre del archivo buscado.

¿Cómo ejecutar consultas directas o crear una tabla?

   SQLdb -el nombre lo dice todo- sólo funciona con servidores de base de datos que hacen uso de SQL. SQL significa Structured Query Language (Lenguaje estructurado de consulta). SQL es un lenguaje desarrollado para permitir trabajar con bases de datos relacionales. Prácticamente todos los sistemas de base de datos tiene su propio dialecto, pero un gran número de sentencias SQL son iguales en todos los sistemas de base de datos. Podemos hacer una diferencia entre las sentencias SQL que devuelven la información y sentencias que no devuelven información. Si desea utilizar la información que devuelve la sentencias SQL, tienes que utilizar el componente TSQLQuery (ver ¿Cómo leer datos de una tabla?). Si no piensas utilizar la información devuelta por la consulta SQL, entonces también puedes usar el método ExecuteDirect de un TSQLConnection.

   La mayoría de sistemas de base de datos ejecutan las sentencias SQL dentro de una transacción. Si desea que los cambios realizados dentro de una transacción estén disponible en otras transacciones, o disponer de los cambios disponible incluso después de cerrar la transacción, entonces tienes que realizar el commit de la transacción. Para soportar las operaciones SQLdb tiene el componente TSQLTransaction. Una sentencia SQL que se ejecuta por SQLdb siempre debe ser ejecutada dentro de una transacción. Incluso si el sistema de base de datos no soporta las transacciones. Además, hay sistemas de bases de datos que soportan transacciones para los que TSQLConnection no (todavía) soporta la transacción. Incluso entonces, debes utilizar el componente TSQLTransaction.

   Para utilizar TSQLConnection.ExecuteDirect para ejecutar una sentencia SQL debes especificar que Transaction se debe utilizar. A su vez, al utilizar TSQLTransaction debes especificar que TSQLConnection se debe utilizar.

   En el ejemplo siguiente se crea una tabla 'TBLNOMBRE' con los campos NOMBRE y nID e inserta dos registros. Las instrucciones SQL utilizadas no se explican. Para obtener más información acerca de las instrucciones SQL, su uso y su sintaxis, consulte la documentación del sistema de base de datos. El procedimiento CreateConnection se define en el ejemplo de código de ¿Cómo conectarse a un servidor de base de datos?.

<delphi>program CreateTable;

var AConnection : TSQLConnection;
    ATransaction : TSQLTransaction;

procedure CreateTransaction;
begin
  ATransaction := TSQLTransaction.Create;
  ATransaction.Database := AConnection;
end;

begin
  CreateConnection;
  CreateTransaction;
  AConnection.Transaction := ATransaction;
  AConnection.Open;
  ATransaction.StartTransaction;
  AConnection.ExecuteDirect('create table TBLNAMES (ID integer, NAME varchar(40));'); 
  
  // Some database-server types need a commit before you can use a newly created table. (Firebird)
  // With .Commit you also close the transaction
  ATransaction.Commit; 

  ATransaction.StartTransaction;
  AConnection.ExecuteDirect('insert into TBLNAMES (ID,NAME) values (1,'Name1');'); 
  AConnection.ExecuteDirect('insert into TBLNAMES (ID,NAME) values (2,'Name2');'); 
  ATransaction.Commit; 
  AConnection.Close;
  AConnection.Free;
  ATransaction.Free;
end.</delphi>

¿Cómo leer datos de una tabla?

   Utiliza el componente TSQLQuery para leer datos de una tabla. Un componente TSQLQuery debe estar enlazado a un componente TSQLConnection y a un componente TSQLTransaction para hacer su trabajo. El ajuste de TSQLConnection y de TSQLTransaction se discute en ¿Cómo conectarse a un servidor de base de datos? y en ¿Cómo ejecutar consultas directas o crear una tabla?.

   Cuando TSQLConnection, TSQLTransaction y TSQLQuery están conectados, entonces TSQLQuery necesita más ajustes. TSQLQuery tiene una propiedad SQL que contiene un objeto TStrings. La propiedad 'SQL' contiene la sentencia SQL que debe ejecutarse. Si todos los datos de una tabla debe ser leídos establece la propiedad SQL a SELECT * FROM nombretabla;. Utiliza open para leer la tabla desde el servidor y poner los datos en el conjunto de datos TSQLQuery. Los datos se pueden acceder a través de TSQLQuery hasta que la consulta se cierra con close.

   TSQLQuery es una subclase de TDataset. TDataset tiene una colección Fields que contiene todas las columnas de la tabla. El TDataset también mantiene una marca del registro actual. Utiliza First, Next, Prior y Last para cambiar el registro actual. Bof devuelve true (verdadero) si se llegó al primer registro, y Eof devuelve true si se llegó al último registro. Para leer el valor de un campo en el registro actual, en primer lugar hayamos el objeto TField adecuado y luego usar 'AsString', 'AsInteger', etc.

   A continuación se muestra un ejemplo que muestra todos los valores de la tabla como se hizo en ¿Cómo ejecutar consultas directas o crear una tabla?.

<delphi>Program ShowData;

var AConnection : TSQLConnection;
    ATransaction : TSQLTransaction;

procedure GetQuery : TSQLQuery;
var AQuery : TSQLQuery;
begin
  AQuery := TSQLQuery.Create;
  AQuery.Database := FConnection;
  AQuery.Transaction := FTransaction;
  Result := AQuery;
end;

var Query : TSQLQuery;

begin
  CreateConnection;
  CreateTransaction;
  Query := GetQuery;
  Query.SQL.Text := 'select * from tblNames';
  AConnection.Open;
  Query.Open;
  while not Query.Eof do
    begin
    Writeln('ID: ', Query.FieldByName('Name').AsInteger, 'Name: ' +
                                  Query.FieldByName('Name').AsString);
    Query.Next;
    end;
  Query.Close;
  AConnection.Close;
  Query.Free;
  ATransaction.Free;
  AConnection.Free;
end.</delphi>

   (El código anterior, por supuesto, no está terminado, se echa de menos bloques try ... finally. Sin embargo, el código anterior tiene la intención de mostrar el código para base de datos y por lo tanto los toques finales son dejados de lado.) Ten en cuenta que TSQLTransaction.StartTransaction no se utiliza. Esto no es necesario. Cuando TSQLQuery se abre, la instrucción SQL se ejecuta y si no hay ninguna transacción está disponible, esta se inicia automáticamente. El programador no necesita iniciar la transacción explícitamente. Lo mismo se aplica para la conexión gestionada por TSQLConnection. La conexión se abre según sea necesario, la línea Aconnection.Open no es realmente necesaria.

   Si se destruye un TSQLTransaction, un rollback (deshacer) automático será ejecutado. Los posibles cambios a los datos contenidos en la transacción se perderán.

¿Por qué TSQLQuery.RecordCount siempre devuelve 10?

   Para contar los registros de un dataset, usaremos RecordCount. Sin embargo, observa que RecordCount muestra el número de registros que se ha cargado desde el servidor. SQLdb no lee todos los registros al abrir un TSQLQuery de forma predeterminada, sólo los 10 primeros. Sólo cuando se accede al undécimo registro entonces el siguiente conjunto de 10 registros se carga. Usando .Last todos los registros se cargarán.

   Si desea conocer el número real de registros en el servidor hay que llamar primero '.Last' y luego llamar a .RecordCount. Hay una alternativa disponible. El número de registros devueltos por el servidor está determinado por el valor de la propiedad PacketRecords. El valor por defecto es 10, si se pone a -1 entonces todos los registros serán cargados a la vez.

Lazarus

   Lazarus tiene diversos componentes para mostrar datos de un TDataset en un formulario. En lugar de un bucle while y declaraciones Writeln usados en el ejemplo, puedes utilizar componentes para mostrar los datos de una tabla. Coloca los adecuados componentes TSQLConnection, TSQLTransaction y TSQLQuery en un formulario, a continuación, se conectan y configuran correctamente. Además se necesita un TDataSource, ajusta la propiedad TDatasource.Dataset al componente TSQLQuery utilizado. (Nota no establecer la propiedad TSQLQuery.Datasource al componente TDataSource. La propiedad TSQLQuery.Datasource se utiliza solamente en tablas maestro/detalle) A continuación puedes poner un TDBGrid en el formulario y establece la propiedad Datasource de la rejill (grid) al componente TDataSource agregado antes.

   Para ver si funciona todo, establece la propiedad Connected del TSQLConnection a True en el IDE de Lazarus. El IDE intenta conectar con el servidor de base de datos inmediatamente. Si funciona se puede establecer la propiedad TSQLQuery.Active a 'True'. Si todo está correcto, podrás ver -en el IDE- todos los datos de la tabla inmediatamente en la pantalla.

How to change data in a table?

To change the data in a record, the TDataset (from which TSQLQuery is derived) must be set to edit mode. To enter edit mode call the '.Edit', '.Insert' or '.Append' methods. Use the '.Edit' method to change the current record. Use '.Insert' to insert a new record before the current record. Use '.Append' to insert a new record at the end of the table. In edit mode you can change field values through the 'Fields' property. Use 'Post' to validate the new data, if the data is valid then the edit mode is left. If you move to another record - for example by using '.Next' - and the dataset is in edit mode, then first '.Post' is called. Use '.Cancel' to discard all changes you made since the last '.Post' call and leave the edit mode.

<delphi>Query.Edit;

Query.FieldByName('NAME').AsString := 'Edited name';
Query.Post;</delphi>

The above is not the complete story yet. TSQLQuery is derived from TBufDataset which makes use of buffered updates. Buffered update mean that after you called 'Post' the changes in the dataset are visible immediately, but they are not sent to the database server. What does happen is that the changes are maintained in a change log. When the '.ApplyUpdates' method is called, then all changes in the change log are sent to the databse. Only then the database server knows all the changes. The changes are sent to the server within a transaction of TSQLTransaction. Make sure to properly set the transaction before 'ApplyUpdates'. After applying the updates, a commit must be executed to publish or store the changes.

The below is an example of changing the data in a table, sending the changes to the server and comitting the transaction.

<delphi>Program EditData;

var AConnection : TSQLConnection;
    ATransaction : TSQLTransaction;
    Query : TSQLQuery;

begin
  CreateConnection;
  CreateTransaction;
  AConnection.Transaction := ATransaction;
  Query := GetQuery;
  Query.SQL.Text := 'select * from tblNames';
  Query.Open;
  Query.Edit;
  Query.FieldByName('NAME').AsString := 'Edited name';
  Query.Post;
  Query.UpdateMode := upWhereAll;
  Query.ApplyUpdates;
  AConnection.Transaction.Commit;
  Query.Free;
  ATransaction.Free;
  AConnection.Free;
end.</delphi>

For a discussion of 'UpdateMode' continue reading.

How does Sqldb send the changes to the database server?

In the code example in #How to change data in a table?, you will find the line <delphi> Query.UpdateMode := upWhereAll;</delphi> without explanation of what it does. The best way to find out what that line does is to leave it out. If you leave out the statement and the followed this howto precisely, then you will receive the following error message: <delphi> No update query specified and failed to generate one. (No fields for inclusion in where statement found)</delphi> To understand what went wrong, you must understand how changes are sent to the database server. The only way to get data in a SQL server is by executing SQL queries. SQL has three types of queries for three different ways of manupulating a record. To create a new record, change or delete a record insert, update and delete statements are executed respectively. An update statement may be as follows: <delphi> update TBLNAMES set NAME='Edited name' where ID=1;</delphi> To send a change to the database server, Sqldb must assemble an update query. To assemble the query, three things are needed:

The name of the table

The table name is retrieved from parsing the select query, although this doesn't always work.

UPDATE or INSERT clause

These contain the fields that must be changed.

WHERE clause

This contains the fields that determine which records should be changed.

Every field (each TField in Fields) has a ProviderFlags property. Only fields with pfInUpdate in ProviderFlags will be used in the update or insert cluase of a query. By default all fields have pfInUpdate set in their ProviderFlags property.

Which fields are used in the WHERE clause depends on the UpdateMode property of the query and the ProviderFlags property of the fields. Fields with pfInkey in their ProviderFlags are always used in the WHERE clause. A field will have the pfInKey flag set automatically if the field is part of the primary key of the table and 'TSQLQuery.UsePrimaryKeyAsKey' returns 'True'.

The default value for UpdateMode of the query is upWhereKeyOnly. In this update mode only fields with pfInkey in their ProviderFlags property are used in the WHERE clause. If none of the fields have their pfInKey flag set, then no fields are available for the WHERE clause and the error message from the beginning of this section will be returned. You can solve the issue by:

• Adding a primary key to the table and set TSQLQuery.UsePrimaryKeyAsKey to 'True', or
• Setting the pfInkey flag for one or more fields in code.

The UpdateMode property knows two more possible values. 'upWhereAll' can be used to add all fields with the 'pfInWhere' flag set to the WHERE clause. By default all fields have this flag set. 'upWhereChanged' can be used to add only those fields that have the 'pfInWhere' flag set and that are changed in the current record.

How to execute a query using TSQLQuery?

Next to statements that return a dataset (see #How to read data from a table?) SQL has statements that do not return data. For example INSERT, UPDATE and DELETE statements do not return data. These statements can be executed using TSQLConnection.ExecuteDirect, but TSQLWuery can alos be used. If you do not expect return datause TSQLQuery.ExecSQL instead of TSQLQuery.Open. Use TSQLQuery.Open to open the dataset returned by the SQL statement.

The following procedure creates a table and inserts two records using TSQLQuery.

<delphi>procedure CreateTable;

 var Query : TSQLQuery;
 
 begin
   Query := GetQuery;
   Query.SQL.Text := 'create table TBLNAMES (ID integer, NAME varchar(40));';
   Query.ExecSQL;
 
   Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (1,'Name1');';
   Query.ExecSQL;
 
   Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (2,'Name2');';
   Query.ExecSQL;
 
   Query.Close;
   Query.Free;
 end;</delphi>

How to use parameters in a query?

In the code example of #How to execute a query using TSQLQuery? the same query is used twice, only the values to be inserted differ. A better way to do this is by using parameters in the query.

The syntax of parameters in queries is different per database system, but the differences are handles by TSQLQuery. Replace the values in the query with a colon followed by the name of the parameter you want to use. For example: <delphi>Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (:ID,:NAME);';</delphi> This query will create two parameters: 'ID' and 'NAME'. To determine the parameters, the query is parsed at the moment the text of TSQLQuery.SQL is assinged or changed. All existing parameters will be removed and the new parameters will be added to the 'TSQLQuery.Params' property. Assigning a value to a parameter is similar to assigning a value to a field in teh dataset: <delphi>Query.Params.ParamByName('Name').AsString := 'Name1';</delphi> You can't tell from the query what kind of data must be stored in the parameter. The data type of the parameter is determined at the moment a value is first assigned to the parameter. By assigning a value using '.AsString', the parameter is assigned the data type 'ftString'. You can determine the data type directly by setting the 'DataType' property. If an incorrect datatype is assigned to the parameter, then problems will occur during opening or executing the query.

The following example creates th same table as the previous example, but now parameters are used:

<delphi>procedure CreateTableUsingParameters;

 var Query : TSQLQuery;
 
 begin
   Query := GetQuery;
   Query.SQL.Text := 'create table TBLNAMES (ID integer, NAME varchar(40));';
   Query.ExecSQL;
 
   Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (:ID,:NAME);';
 
   Query.Params.ParamByName('ID').AsInteger := 1;
   Query.Params.ParamByName('ID').AsString := 'Name1';
   Query.ExecSQL;
 
   Query.Params.ParamByName('ID').AsInteger := 2;
   Query.Params.ParamByName('ID').AsString := 'Name2;
   Query.ExecSQL;
 
   Query.Close;
   Query.Free;
 end;</delphi>

Notice that this example requires more code than the example without the parameters. Then what is the use of using parameters? Speed is one of the reasons. The example with parameters is faster because the query is parsed only once. TSQLQuery only parses the query once, but also the database server parses the query only once. Most database systems support parameters. Whenever a query is used more than once with different values for the parameter each time, then the database server only parses the query and plans the query only once making execution considerably faster. Use 'TSQLQuery.Prepare' to determine the moment the query is parsed and planned by the database server. Use 'TSQLQuery.UnPrepare' to make sure the query is parsed and planned every time by the data server.

Another reason to use prepared statements is prevention of SQL-injection, but in some cases it just simplifies coding.

See also

* Trabajando con TSQLQuery.