Working With TSQLQuery/es

From Lazarus wiki
Revision as of 11:42, 7 July 2008 by Iskraelectrica (talk | contribs) (Trabajando con TSQLQuery)

English (en) español (es) français (fr) 中文(中国大陆)‎ (zh_CN)

   Para una referencia general de cómo trabajar con Bases de Datos ver Bases de Datos en Lazarus

Trabajando con TSQLQuery

   El conjunto de datos (DataSet) que devuelve TSQLQuery se puede ver con una instancia de TDBGrid, pero no es muy recomendable para editar los campos de los registros individuales. Para realizar esto en conveniente poner varios componentes ligados a datos para registros individuales, cómo TDBEdit en el formulario, y poner su propiedad DataSource con el valor de nombre de la fuente de datos utilizada. En la propiedad DataField seleccionaremos el nombre del campo ( 'IDENTIDAD') o una expresión que resulte en una cadena aceptable.

Lazarus TDBNavigator.png

   Si añadimos un componente de navegación TDBNavigator resultará muy fácil moverse por los registros y seleccionarlos para editarlos. Cuándo un registro es seleccionado mediante un TDBNavigator o moviendo el cursor en el DBGrid los datos correspondientes del registro se mostrarán en el TDBEdity si se pulsa el botón de Editar, su contenido puede ser modificado. Con el botón Post podemos confirmar la edición o bien desechar los cambios con Cancelar.

   En general el proceso es el siguiente:

  1. Drop a TSQLQuery on a form/datamodule, and set the Database, Transaction and SQL properties.
  2. Drop a TDatasource component, and set it's Dataset property to the TSQLQuery instance.
  3. Drop a TDBGrid on the form and set it's Datasource property to the TDatasource instance.
  4. Optionally, drop a TDBNavigator instance on the form, and set it's Datasource property to the TDatasource instance.

After this, the Active property can be set to True, and it should be possible to see the data retrieved by the query. (provided both the TSQLConnection and TSQLTransaction components are active)

Updating data

If you need to be able to DELETE or otherwise modify records, your database table should either

  1. contain one PRIMARY KEY column.
  2. have a set of fields that uniquely determine the record. Normally, they should be part of a unique index. This is not required, but will speed up the queries quite a lot

If there is no primary field, or no fields that uniquely determine your record, then a primary key field should be added. This is done preferably when the table structure is designed, at CREATE time, but can be added at a later time.

For instance the following example code in your MySQL client will add a unique index to your table:

 alter table testrig 
 add column autoid int 
 primary key auto_increment;

Adding this field will not hurt, and will allow your applications to update the field.

Cached Updates

The TSQLQuery component caches all updates. That is, the updates are not sent immediately to the database, but are kept in memory till the APPLYUPDATES method is called. At that point, the updates will be transformed to SQL update statements, and will be applied to the database. If you do not call ApplyUpdates, the database will not be updated with the local changes.

Primary key fields

When updating records, TSQLQuery needs to know which fields comprise the primary key that can be used to update the record, and which fields should be updated: based on that information, it constructs an SQL UPDATE, INSERT or DELETE command.

The construction of the SQL statement is controlled by the UsePrimaryKeyAsKey property and the ProviderFlags properties.

The Providerflags property is a set of 3 flags:

The field is part of the primary key
The field should be used in the WHERE clause of SQL statements.
Updates or inserts should include this field.

By default, ProviderFlags consists of pfInUpdate only.

If your table has a primary key (as described above) then you only need to set the UsePrimaryKeyAsKey property to True and everything will be done for you. This will set the pfInKey flag for the primary key fields.

If the table doesn't have a primary key index, but does have some fields that can be used to uniquely identify the record, then you can include the pfInKey option in the ProviderFlags property all the fields that uniquely determine the record.

The UpdateMode property will then determine which fields exactly will be used in the WHERE clause:

When TSQLQuery needs to construct a WHERE clause for the update, it will collect all fields that have the pfInKey flag in their ProviderFlags property set, and will use the values to construct a WHERE clause which uniquely determines the record to update -- normally this is only needed for an UPDATE or DELETE statement.
In addition to the fields that have pfInKey in the ProviderFlags property, all fields that have pfInWhere in their ProviderFlags and that have changed, will also be included in the WHERE clause.
In addition to the fields that have pfInKey in the ProviderFlags property, all fields that have pfInWhere in their ProviderFlags, will also be included in the WHERE clause.

Controlling the update

It is possible to specify which fields should be updated: As mentioned above: Only fields that have pfInUpdate in their ProviderOptions property will be included in the SQL UPDATE or INSERT statements. By default, pfInUpdate is always included in the ProviderOptions property.