Difference between revisions of "Working With TSQLQuery/es"

From Lazarus wiki
Jump to navigationJump to search
m (Fixed syntax highlighting; deleted category included in page template)
 
(38 intermediate revisions by 4 users not shown)
Line 1: Line 1:
 
{{Working With TSQLQuery}}[[category:Castellano]][[category:Español]]
 
{{Working With TSQLQuery}}[[category:Castellano]][[category:Español]]
  
Para una referencia general de cómo trabajar con Bases de Datos ver [[Databases_in_Lazarus/es| Bases de Datos en Lazarus]]
+
   Para una referencia general de cómo trabajar con Bases de Datos ver [[Databases_in_Lazarus/es| Bases de Datos en Lazarus]]
 +
 
 +
   '''TSQLQuery''' es un objeto que representa un conjunto de datos procedentes de una base de datos (desde un SGBD que utiliza SQL, como Firebird, MS SQL Server, Oracle, MySQL y otros) La sentecia SQL de la propiedad '''SQL''' del '''TSQLQuery''' determina qué datos se recuperan desde la base de datos en el conjunto de datos. Cuando el conjunto de datos se modifica por el programa (o usuario), los cambios se pueden enviar a la base de datos.
 +
 
 +
   TSQLQuery también se puede utilizar para modificar directamente los datos: si se especifica el mandato SQL deseado INSERT, UPDATE, DELETE, etc. en la propiedad SQL y se llama al método execSQL de TSQLQuery, el objeto de consulta enviará la consulta SQL a la base de datos sin tener que recuperar el resultado.
 +
 
 +
   Además de poder utilizarse por código en FPC, Lazarus proporciona un componente que encapsula su funcionalidad:
 +
 
 +
<center>[[Image:sqldbcomponents.png‎]]</center>
 +
 
 +
== Documentación oficial ==
 +
&nbsp;&nbsp;&nbsp;Ver [http://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.html Documentación de TSQLQuery]
 +
 
 +
&nbsp;&nbsp;&nbsp;Una gran cantidad de documentación sensible al contexto está disponible en Lazarus. Desafortunadamente, TSQLQuery no aparece en el índice de la ayuda de Lazarus 1.0. Si colocas el cursor sobre métodos y propiedades de  TSQLQuery, pulsa F1 para ver si el código está documentado; por ejemplo esto funciona:
 +
 
 +
<syntaxhighlight lang=pascal>
 +
var
 +
Q: TSQLQuery
 +
...
 +
  Q.Open; //<--- sitúa el cursor sobre Open y pulsa F1
 +
</syntaxhighlight>
  
 
==Trabajando con TSQLQuery==
 
==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 (v.gr. 'IDENTIDAD') o una expresión que resulte en una cadena aceptable.
+
&nbsp;&nbsp;&nbsp;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 (v.gr. 'IDENTIDAD') o una expresión que resulte en una cadena aceptable.
 +
 
 +
&nbsp;&nbsp;&nbsp;Si añadimos un componente de navegación ''TDBNavigator'' [[Image:Lazarus_TDBNavigator.png]] 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 ''TDBEdit''y si se pulsa el botón de Editar [[Image:Lazarus_TDBNavigator.Editar.png]], su contenido puede ser modificado. Con el botón ''Post'' [[Image:Lazarus_TDBNavigator.Guardar.png]] podemos confirmar la edición o bien desechar los cambios con ''Cancelar'' [[Image:Lazarus_TDBNavigator.Cancelar.png]].
 +
 
 +
&nbsp;&nbsp;&nbsp;En general el proceso es el siguiente:
 +
# Sitúa un componente ''TSQLConnection'' en el formulario o módulo de datos, da el valor adecuado a la propiedad ''DatabaseName'' seleccionando el archivo que contiene la base de datos a la que se va a acceder.
 +
# Sitúa un componente ''TSQLTransaction'' en el formulario o módulo de datos, da el valor adecuado a la propiedad ''Database'' (será el componente ''TSQLConnection'' insertado en el paso 1).
 +
# Sitúa un componente ''TSQLQuery'' en el formulario o módulo de datos, da los valores adecuados a las propiedades ''Database'' (será el componente ''TSQLConnection'' insertado en el paso 1), ''Transaction''  (será el componente ''TSQLTransaction'' insertado en el paso 2) y ''SQL'' con una orden SQL (por ejemplo 'Select * from Employee').
 +
# Sitúa un componente ''TDatasource'', haz que su propiedad ''Dataset'' apunte a la instancia de ''TSQLQuery''.
 +
# Sitúa un componente ''TDBGrid'' en el formulario, selecciona en su propiedad ''Datasource'' el nombre dado al componente ''TDatasource'' anterior.
 +
# Opcionalmente sitúa un componente ''TDBNavigator'' y da valor a su propiedad ''Datasource'' para usar el ''TDatasource''.
 +
&nbsp;&nbsp;&nbsp;En el componente ''TSQLConnection''  poner la propiedad ''Connected''  a verdadero (''True'').  En el componente ''TSQLTransaction''  poner la propiedad ''Active''  a verdadero (''True'').  Finalmente, en el componente ''TSQLQuery'' poner la propiedad ''Active''  a verdadero (''True''), y podremos ver los datos  suministrados por la consulta.
 +
 
 +
== Actualización de datos ==
 +
 
 +
&nbsp;&nbsp;&nbsp;Si es necesario poder Borrar o modificar registros, la Base de datos tiene que
 +
# Tener una columna con la Clave Primaria.
 +
# Tener un conjunto de campos que individualicen cada registro, que normalmente serán parte del índice único. Este índice no es imprescindible, pero contribuirá a que las consultas sean más rápidas.
 +
 
 +
&nbsp;&nbsp;&nbsp;Si no existen ni el campo primario ni esos campos que individualizan un registro será necesario añadir un registro de clave primaria. Esto se realiza preferiblemente al diseñar la estructura de la tabla en el momento de crearla, pero podemos añadirla posteriormente.
 +
 
 +
&nbsp;&nbsp;&nbsp;El siguiente código en un cliente MySQL, por ejemplo, añadirá un índice único para su tabla.
 +
<syntaxhighlight lang="sql">  alter table ejemplo1
 +
  add column autoidentificador int
 +
  primary key auto_increment;</syntaxhighlight>
 +
&nbsp;&nbsp;&nbsp;La adición del campo no estropeará nada y permitirá a tus aplicaciones para actualizarlo.
 +
 
 +
== Cambios diferidos (''cached'') ==
 +
&nbsp;&nbsp;&nbsp;El componente ''TSQLQuery'' guarda los cambios en un espacio de memoria intermedia. Es decir, los cambios no son enviados inmediatamente a la Base de datos, si no que se mantienen en memoria hasta que llamamos al método '''''ApplyUpdates'''''. En ese momento los cambios se transforman en sentencias DML de SQL, y son aplicadas a la Base de datos. Si no se utiliza el método ''ApplyUpdates'', la Base de datos no será actualizada con los cambios locales realizados.
 +
 
 +
== Campos de Clave primaria ==
 +
 
 +
&nbsp;&nbsp;&nbsp;Cuándo se actualizan registros, ''TSQLQuery'' necesita conocer que campos constituyen la clave primaria que se utiliza para modificar un registro y cuáles son los campos que se van a modificar: basándose en esta información construirá la sentencia SQL adecuada UPDATE, INSERT o DELETE.
 +
 
 +
&nbsp;&nbsp;&nbsp;La creación de la sentencia SQL se controla  con las propiedades ''UsePrimaryKeyAsKey'' y ''ProviderFlags''.
 +
 
 +
&nbsp;&nbsp;&nbsp;La propiedad ''Providerflags'' está compuesta de 3 indicadores:
 +
; &nbsp;&nbsp;&nbsp;''pfInkey'': El campo forma parte de la clave primaria
 +
; &nbsp;&nbsp;&nbsp;''pfInWhere'': El campo puede utilizarse en la cláusula WHERE de una sentencia SQL.
 +
; &nbsp;&nbsp;&nbsp;''pfInUpdate'': LAs modificaciones o inserciones deben incluir este campo.
 +
 
 +
&nbsp;&nbsp;&nbsp;Por defecto, ''ProviderFlags'' tiene puesto el indicador de  ''pfInUpdate''.
 +
 
 +
&nbsp;&nbsp;&nbsp;Si la tabla dispone de un clave primaria (cómo se describe más arriba) es necesario poner la propiedad ''UsePrimaryKeyAsKey'' a verdadera ''True'' y  lo tendremos todo hecho. De esta forma, se establecerá el indicador ''pfInKey'' para los campos de la clave primaria.
 +
 
 +
&nbsp;&nbsp;&nbsp;Si la tabla no dispone de un clave primaria, pero existen campos que permiten individualizar los registros se deberá incluir el indicador ''pfInKey'' en la propiedad ''ProviderFlags'' de todos los registros que identifican el registro cómo único.
 +
 
 +
&nbsp;&nbsp;&nbsp;La propiedad ''UpdateMode'' determina los registros que pueden ser utilizados en la cláusula WHERE:
 +
 
 +
; &nbsp;&nbsp;&nbsp;''upWhereKeyOnly'': En el momento de construir una cláusula WHERE ''TSQLQuery'' lista todos los campos que tienen el indicador ''pfInKey'' en su propiedad ''ProviderFlags'' y utiliza los valores para crear la cláusula WHERE que determina el registro cómo único para modificar sus valores  -- normalmente esto sólo es necesario para las sentencias UPDATE y DELETE.
 +
 
 +
; &nbsp;&nbsp;&nbsp;''upWhereChanged'': Además de los campos con el indicador ''pfInKey'' en su propiedad ''ProviderFlags'',  los campos con el indicador ''pfInWhere''  [[ y que han sido modificados]], se incluyen en la cláusula WHERE.
 +
 
 +
; &nbsp;&nbsp;&nbsp;''upWhereAll'': Los campos con el indicador ''pfInWhere'' en su propiedad ''ProviderFlags'' serán también incluidos para crear las cláusula WHERE.
 +
 
 +
==Controlando los cambios==
 +
&nbsp;&nbsp;&nbsp;Es posible especificar que campos pueden ser modificados: se mencionó antes: Únicamente los campos que tienen el indicador ''pfInUpdate'' en su propiedad ''ProviderOptions'' se pueden incluir en sentencias SQL UPDATE o INSERT. Por defecto el indicador ''pfInUpdate'' está presente en la propiedad ''ProviderOptions''.
 +
 
 +
== Personalizar el SQL en TSQLQuery ==
 +
&nbsp;&nbsp;&nbsp;Normalmente TSQLQuery utiliza sentencias SQL genéricas basadas en las propiedades como se discutió anteriormente. Sin embargo, el SQL genérico creado por SQLdb puede no ser correcto para tu situación. TSQLQuery te permite personalizar las sentencias SQL utilizadas para las distintas acciones, para funcionar mejor en tu situación con tu base de datos. Para ello utiliza las propiedades ''SQL'', ''InsertSQL'', ''UpdateSQL'' y ''DeleteSQL''.
 +
 
 +
&nbsp;&nbsp;&nbsp;Todas estas propiedades son de tipo ''TStringList'', una lista de cadenas de texto, que acepta varias líneas de SQL. Las cuatro vienen con un editor de propiedades en el IDE. En el IDE, selecciona la propiedad y abre el editor haciendo clic en el botón de puntos suspensivos. En el código, utiliza, por ejemplo <tt>InsertSQL.Text</tt> o <tt>InsertSQL.Add()</tt> para ajustar o agregar líneas de instrucciones SQL. Una sentencia puede abarcar varias líneas y termina con punto y coma.
 +
 
 +
&nbsp;&nbsp;&nbsp;Además, las cuatro propiedades aceptan parámetros que se describen más adelante.
 +
 
 +
===TSQLQuery.SQL: Personalización básica de SQL===
 +
&nbsp;&nbsp;&nbsp;La propiedad SQL se utiliza normalmente para recuperar los datos de la base de datos. El SQL genérico para esta propiedad es <tt lang="sql">SELECT * FROM fpdev</tt lang="sql"> donde <tt> fpdev </tt> es la tabla que figura en la base de datos.
 +
 
 +
&nbsp;&nbsp;&nbsp;El conjunto de datos que devuelve la instrucción SQL genérica está sin pulir. Si muestras el resultado en un ''TDBGrid'', el orden de los registros puede parecer al azar, el orden de las columnas puede no ser lo que quieres y los nombres de campo pueden ser técnicamente correctos, pero no fáciles de usar. Utilizar SQL personalizado puede mejorar esta situación.
 +
<syntaxhighlight lang="sql"> SELECT id AS 'ID', NombreUsuario AS 'Usuario', InstEmail AS 'e-Correo' FROM fpdev ORDER BY id;</syntaxhighlight>
 +
 
 +
&nbsp;&nbsp;&nbsp;La tabla de la base de datos tiene las columnas id, NombreUsuario y InstEmail. El conjunto de datos que resulta de la consulta anterior utiliza los nombres de campo, tal como figura en la consulta (ID, Usuario y e-Correo), el orden de las columnas, tal como figura en la consulta y los registros son ordenados por su id.
 +
 
 +
===TSQLQuery.InsertSQL, TSQLQuery.UpdateSQL and TSQLQuery.DeleteSQL: Utilización básica de parámetros===
 +
 
 +
&nbsp;&nbsp;&nbsp;Cuando se asigna una sentencia SELECT a la propiedad '''SQL''' de  TSQLQuery, este sabe cómo y qué datos obtener de la base de datos.
 +
 
 +
&nbsp;&nbsp;&nbsp;Sin embargo, cuando se usan controles enlazados a datos como un DBGrid, TSQLQuery también tendrá que ser capaz de insertar, actualizar y eliminar filas de la base de datos según las acciones del usuario.
 +
 
 +
&nbsp;&nbsp;&nbsp;Con el fin de acelerar el desarrollo, SQLQuery puede tratar de deducir las sentencias SQL necesarias. Si existe la propiedad SQL y la propiedad '''ParseSQL''' es verdadera (true) (que lo es por defecto), TSQLQuery tratará de generar estas sentenciass del análisis de la propiedad '''SQL''', y almacenará las sentencias deducidas en las propiedades '''InsertSQL''', '''UpdateSQL''' y '''DeleteSQL'''.
 +
 
 +
&nbsp;&nbsp;&nbsp;Sin embargo, a veces las instrucciones generadas no van a funcionar (por ejemplo, cuando se inserta en en tablas con claves primarias autoincrementadas/autonumeradas) o va a ser muy lento. Si es necesario, puedes asignar manualmente las sentenciass.
 +
 
 +
&nbsp;&nbsp;&nbsp;Las sentencia ''InsertSQL'', ''UpdateSQL'' y ''DeleteSQL'' aceptan parámetros. Los nombres de campo utilizados en estas declaraciones deben ser exactamente los mismos que los nombres de campo utilizados en el conjunto de datos. Los nombres de campo en el conjunto de datos pueden ser diferentes de los nombres de columna en la tabla, en función de la sentencia SELECT (ver arriba). Los campos de valores deben ser escritos como el nombre de campo precedido por dos puntos. Por ejemplo:
 +
 
 +
<syntaxhighlight lang="sql"> INSERT INTO fpdev(id, UserName, InstEmail) VALUES(:id,:UserName,:InstEmail);</syntaxhighlight>
 +
 
 +
&nbsp;&nbsp;&nbsp;Esta sentencia insertará los valores de <tt>id</tt>, <tt>NombreUsuario</tt> y <tt>InstEmail</tt> del registro actual del conjunto de datos en los campos correspondientes de la tabla <tt>fpdev</tt>.
 +
 
 +
&nbsp;&nbsp;&nbsp;La sentencia <tt>INSERT</tt> anterior es una sentencia genérica <tt>INSERT</tt> y sería de uso limitado en TSQLQuery ya que será el mismo que el SQL generado con slqdb por sí mismo. La declaración dada puede dar lugar a errores cuando el campo <tt>ID</tt> es un campo de incremento automático con clave única. Diversas bases de datos solucionan este problema de diferentes maneras. Por ejemplo, lo siguiente funciona para MySQL.
 +
 
 +
<syntaxhighlight lang="sql"> INSERT INTO fpdev(id, UserName, InstEmail) VALUES(0,:UserName,:InstEmail) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID();</syntaxhighlight>
 +
 
 +
&nbsp;&nbsp;&nbsp;La sentencia anterior intenta insertar un nuevo registro, usando 0 (cero) para el campo <tt>ID</tt>. Si el cero ya se utiliza como clave, entonces la duplicidad es detectada y el <tt>ID</tt> se actualiza para usar el último <tt>ID</tt> insertado. Bueno, en realidad un identificador de un incremento mayor que el último utilizado.
 +
 
 +
&nbsp;&nbsp;&nbsp;Para una sentencia <tt>INSERT</tt> es posible que desee utilizar los valores del campo actual del registro seleccionado. Para una sentencia <tt>UPDATE</tt>, tendrás que usar los valores de los campos como estaban antes de la edición en la cláusula <tt>WHERE</tt>. Esto también es posible. Los valores de los campos antes de editar deben ser escritas como el nombre del campo precedidon por <tt>:old_</tt>. Por ejemplo, lo siguiente funciona para MySQL:
 +
 
 +
<syntaxhighlight lang="sql"> UPDATE fpdev SET UserName=:UserName, InstEmail=:InstEmail WHERE UserName=:Old_UserName;</syntaxhighlight>
 +
 
 +
&nbsp;&nbsp;&nbsp;La sentencia anterior actualiza los campos <tt>UserName</tt> y <tt>InstEmail</tt> de todos los registros en los que <tt>UserName</tt> es igual al valor anterior de <tt>UserName</tt>.
 +
 
 +
&nbsp;&nbsp;&nbsp;Dejamos como ejercicio para el lector que use los valores de campo actuales y los valores antiguos de los campos en una sentencia <tt>DELETE</tt>.
 +
 
 +
===Parámetros en TSQLQuery.SQL===
 +
&nbsp;&nbsp;&nbsp;En la mayoría de situaciones, la propiedad SQL de TSQLQuery contendrá la instrucción de selección que a su vez en la mayoría de situaciones no necesita parámetros. Sin embargo, incluso la propiedad SQL puede contener parámetros.
 +
 
 +
&nbsp;&nbsp;&nbsp;Los parámetros son una forma muy fácil y potente de filtrar los registros.
  
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 ''TDBEdit''y 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''.
+
&nbsp;&nbsp;&nbsp;Usar parámetros tienen las siguientes ventajas:
  
In general, the process is as follows:
+
*no hay necesidad de dar formato a los datos como texto SQL, los argumentos de tipo fecha, etc. (es decir, no es necesario recordar cómo dar formato a una fecha para MySQL, que puede diferir de la de Firebird, no hay necesidad de escapar de los datos de texto como de O'Malley "Horror SQL")
# Drop a TSQLQuery on a form/datamodule, and set the ''Database'', ''Transaction'' and ''SQL'' properties.
+
*mayor rendimiento
# Drop a TDatasource component, and set it's ''Dataset'' property to the TSQLQuery instance.
+
*protección contra ataques de inyección SQL
# Drop a TDBGrid on the form and set it's ''Datasource'' property to the TDatasource instance.
 
# 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 ==
+
&nbsp;&nbsp;&nbsp;El uso de parámetros puede ayudar al rendimiento de la base de datos. La mayoría de las bases de datos permiten utilizar procedimientos almacenados, lo que significa que la sentencia es preparada y almacenada en la base de datos. Un procedimiento almacenado se puede utilizar más de una vez y no requiere el análisis y la planificación de la consulta cada vez que se utiliza, sólo los parámetros cambian cada vez que se utiliza. En situaciones donde se utiliza la misma sentencia un gran número de veces (en el que sólo los valores de los parámetros son diferentes), los procedimientos almacenados pueden ayudar considerablemente a aumentar el rendimiento. Adicionalmente, los ataques de inyección SQL pueden ser mitigados mediante el uso de parámetros.
  
If you need to be able to DELETE or otherwise modify records, your database table should either
+
&nbsp;&nbsp;&nbsp;Las propiedades ''InsertSQL'', ''UpdateSQL'' y ''DeleteSQL'' pueden utilizar parámetros predefinidos para el valor actual y el valor previo del campo; la propiedad ''SQL'' no lo hace. Puedes crear tus propios parámetros en la propiedad ''Params''.
# contain one PRIMARY KEY column.
 
# 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.
+
====Ejemplos de consultas SELECT====
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:
+
&nbsp;&nbsp;&nbsp;En este ejemplo se muestra cómo seleccionar datos utilizando parámetros. También se muestra el uso de alias (... AS ...) en SQL.
  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 ==
+
<syntaxhighlight lang=pascal> sql_temp.sql.text := 'SELECT id AS ''ID'', UserName AS ''User'', InstEmail AS ''e-mail'' FROM fpdev WHERE InstEmail=:emailsearch;'
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
+
//Con esto creamos un parámetro llamado emailsearch.
the database. If you do not call ''ApplyUpdates'', the database will not be updated with the local changes.
+
//ahora podemos dar valor al parámetro:
 +
sql_temp.params.parambyname('emailsearch').asstring := 'mvancanneyt@freepascal.org';
 +
...
 +
//Y usamos la forma habitual de recuperar los datos,
 +
//opcionalmente podemos cambiar el valor del parámetro y ejecutar la sentencia de nuevo</syntaxhighlight>
  
== Primary key fields ==
+
====Ejemplos de consultas INSERT====
  
When updating records, ''TSQLQuery'' needs to know which fields comprise the primary key that can be used to update the record,
+
&nbsp;&nbsp;&nbsp;Este ejemplo muestra como inserta un registro nuevo en la tabla, utilizando parámetros:
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.
+
<syntaxhighlight lang=pascal>
 +
sql_temp.sql.text := 'insert into PRODUCTS (ITEMNR,DESCRIPTION) values (:OURITEMNR,:OURDESCRIPTION)'
 +
...;
 +
sql_temp.Params.ParamByName('OURITEMNR').AsString := 'XXXX';
 +
sql_temp.Params.ParamByName('OURDESCRIPTION').AsString := 'description';
 +
sql_temp.ExecSQL;
 +
SQLTransaction1.Commit; //o  CommitRetaining, dependiendo de la aplicación</syntaxhighlight>
  
The ''Providerflags'' property is a set of 3 flags:
+
&nbsp;&nbsp;&nbsp;Otra forma de hacerlo sería esta:
; pfInkey: The field is part of the primary key
 
; pfInWhere: The field should be used in the WHERE clause of SQL statements.
 
; pfInUpdate: Updates or inserts should include this field.
 
  
By default, ''ProviderFlags'' consists of ''pfInUpdate'' only.
+
<syntaxhighlight lang="delphi"> tsqlquery1.appendrecord(['XXXX', 'description'])
 +
tsqltransaction1.commit; //materializamos los datos</syntaxhighlight>
  
If your table has a primary key (as described above) then you only need to set the ''UsePrimaryKeyAsKey'' property to ''True'' and
+
&nbsp;&nbsp;&nbsp;Nota
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
+
&nbsp;&nbsp;&nbsp;[[SqlDBHowto/es|SQL y Bases de Datos, cómo...]], contiene una breve discusión en castellano sobre este tema.
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:
+
== Solución de problemas ==
 +
=== Históricos de movimientos ===
 +
Ver aquí: [[SqlDBHowto/es#Registro_de_sucesos_de_TSQLConnection|Registro de sucesos de TSQLConnection]] para más detalles.
  
; upWhereKeyOnly: 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.
+
=== Mensajes de error ===
 +
==== '''''Dataset is read-only''''' ====
 +
&nbsp;&nbsp;&nbsp;Esto ocurre cuándo especificamos una sentencia que nosotros sabemos que es actualizable, pero a FPC considera que no lo es.
  
; upWhereChanged: 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.
+
&nbsp;&nbsp;&nbsp;Ejemplo:
 +
<syntaxhighlight lang="sql"> select p.dob, p.surnam, p.sex from people p;</syntaxhighlight>
  
; upWhereAll: 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.
+
&nbsp;&nbsp;&nbsp;El analizador sintáctico de SQL del FPC es bastante simplista y cuando encuentra una coma o un espacio en la clausula FROM que considera que varias tablas están involucradas y establece el conjunto de datos de sólo lectura. En su defensa, no se utilizan habitualmente alias de tablas cuando se trabaja con una única tabla.
  
== Controlling the update ==
+
&nbsp;&nbsp;&nbsp;Solución: reescribe la consulta o especifica las clausulas  InsertSQL, UpdateSQL y DeleteSQL.
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.
 

Latest revision as of 11:37, 4 March 2020

English (en) español (es) français (fr) 日本語 (ja) polski (pl) 中文(中国大陆)‎ (zh_CN)

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

   TSQLQuery es un objeto que representa un conjunto de datos procedentes de una base de datos (desde un SGBD que utiliza SQL, como Firebird, MS SQL Server, Oracle, MySQL y otros) La sentecia SQL de la propiedad SQL del TSQLQuery determina qué datos se recuperan desde la base de datos en el conjunto de datos. Cuando el conjunto de datos se modifica por el programa (o usuario), los cambios se pueden enviar a la base de datos.

   TSQLQuery también se puede utilizar para modificar directamente los datos: si se especifica el mandato SQL deseado INSERT, UPDATE, DELETE, etc. en la propiedad SQL y se llama al método execSQL de TSQLQuery, el objeto de consulta enviará la consulta SQL a la base de datos sin tener que recuperar el resultado.

   Además de poder utilizarse por código en FPC, Lazarus proporciona un componente que encapsula su funcionalidad:

sqldbcomponents.png

Documentación oficial

   Ver Documentación de TSQLQuery

   Una gran cantidad de documentación sensible al contexto está disponible en Lazarus. Desafortunadamente, TSQLQuery no aparece en el índice de la ayuda de Lazarus 1.0. Si colocas el cursor sobre métodos y propiedades de TSQLQuery, pulsa F1 para ver si el código está documentado; por ejemplo esto funciona:

var
Q: TSQLQuery
...
  Q.Open; //<--- sitúa el cursor sobre Open y pulsa F1

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 (v.gr. 'IDENTIDAD') o una expresión que resulte en una cadena aceptable.

   Si añadimos un componente de navegación TDBNavigator Lazarus TDBNavigator.png 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 Lazarus TDBNavigator.Editar.png, su contenido puede ser modificado. Con el botón Post Lazarus TDBNavigator.Guardar.png podemos confirmar la edición o bien desechar los cambios con Cancelar Lazarus TDBNavigator.Cancelar.png.

   En general el proceso es el siguiente:

  1. Sitúa un componente TSQLConnection en el formulario o módulo de datos, da el valor adecuado a la propiedad DatabaseName seleccionando el archivo que contiene la base de datos a la que se va a acceder.
  2. Sitúa un componente TSQLTransaction en el formulario o módulo de datos, da el valor adecuado a la propiedad Database (será el componente TSQLConnection insertado en el paso 1).
  3. Sitúa un componente TSQLQuery en el formulario o módulo de datos, da los valores adecuados a las propiedades Database (será el componente TSQLConnection insertado en el paso 1), Transaction (será el componente TSQLTransaction insertado en el paso 2) y SQL con una orden SQL (por ejemplo 'Select * from Employee').
  4. Sitúa un componente TDatasource, haz que su propiedad Dataset apunte a la instancia de TSQLQuery.
  5. Sitúa un componente TDBGrid en el formulario, selecciona en su propiedad Datasource el nombre dado al componente TDatasource anterior.
  6. Opcionalmente sitúa un componente TDBNavigator y da valor a su propiedad Datasource para usar el TDatasource.

   En el componente TSQLConnection poner la propiedad Connected a verdadero (True). En el componente TSQLTransaction poner la propiedad Active a verdadero (True). Finalmente, en el componente TSQLQuery poner la propiedad Active a verdadero (True), y podremos ver los datos suministrados por la consulta.

Actualización de datos

   Si es necesario poder Borrar o modificar registros, la Base de datos tiene que

  1. Tener una columna con la Clave Primaria.
  2. Tener un conjunto de campos que individualicen cada registro, que normalmente serán parte del índice único. Este índice no es imprescindible, pero contribuirá a que las consultas sean más rápidas.

   Si no existen ni el campo primario ni esos campos que individualizan un registro será necesario añadir un registro de clave primaria. Esto se realiza preferiblemente al diseñar la estructura de la tabla en el momento de crearla, pero podemos añadirla posteriormente.

   El siguiente código en un cliente MySQL, por ejemplo, añadirá un índice único para su tabla.

  alter table ejemplo1 
  add column autoidentificador int 
  primary key auto_increment;

   La adición del campo no estropeará nada y permitirá a tus aplicaciones para actualizarlo.

Cambios diferidos (cached)

   El componente TSQLQuery guarda los cambios en un espacio de memoria intermedia. Es decir, los cambios no son enviados inmediatamente a la Base de datos, si no que se mantienen en memoria hasta que llamamos al método ApplyUpdates. En ese momento los cambios se transforman en sentencias DML de SQL, y son aplicadas a la Base de datos. Si no se utiliza el método ApplyUpdates, la Base de datos no será actualizada con los cambios locales realizados.

Campos de Clave primaria

   Cuándo se actualizan registros, TSQLQuery necesita conocer que campos constituyen la clave primaria que se utiliza para modificar un registro y cuáles son los campos que se van a modificar: basándose en esta información construirá la sentencia SQL adecuada UPDATE, INSERT o DELETE.

   La creación de la sentencia SQL se controla con las propiedades UsePrimaryKeyAsKey y ProviderFlags.

   La propiedad Providerflags está compuesta de 3 indicadores:

   pfInkey
El campo forma parte de la clave primaria
   pfInWhere
El campo puede utilizarse en la cláusula WHERE de una sentencia SQL.
   pfInUpdate
LAs modificaciones o inserciones deben incluir este campo.

   Por defecto, ProviderFlags tiene puesto el indicador de pfInUpdate.

   Si la tabla dispone de un clave primaria (cómo se describe más arriba) es necesario poner la propiedad UsePrimaryKeyAsKey a verdadera True y lo tendremos todo hecho. De esta forma, se establecerá el indicador pfInKey para los campos de la clave primaria.

   Si la tabla no dispone de un clave primaria, pero existen campos que permiten individualizar los registros se deberá incluir el indicador pfInKey en la propiedad ProviderFlags de todos los registros que identifican el registro cómo único.

   La propiedad UpdateMode determina los registros que pueden ser utilizados en la cláusula WHERE:

   upWhereKeyOnly
En el momento de construir una cláusula WHERE TSQLQuery lista todos los campos que tienen el indicador pfInKey en su propiedad ProviderFlags y utiliza los valores para crear la cláusula WHERE que determina el registro cómo único para modificar sus valores -- normalmente esto sólo es necesario para las sentencias UPDATE y DELETE.
   upWhereChanged
Además de los campos con el indicador pfInKey en su propiedad ProviderFlags, los campos con el indicador pfInWhere y que han sido modificados, se incluyen en la cláusula WHERE.
   upWhereAll
Los campos con el indicador pfInWhere en su propiedad ProviderFlags serán también incluidos para crear las cláusula WHERE.

Controlando los cambios

   Es posible especificar que campos pueden ser modificados: se mencionó antes: Únicamente los campos que tienen el indicador pfInUpdate en su propiedad ProviderOptions se pueden incluir en sentencias SQL UPDATE o INSERT. Por defecto el indicador pfInUpdate está presente en la propiedad ProviderOptions.

Personalizar el SQL en TSQLQuery

   Normalmente TSQLQuery utiliza sentencias SQL genéricas basadas en las propiedades como se discutió anteriormente. Sin embargo, el SQL genérico creado por SQLdb puede no ser correcto para tu situación. TSQLQuery te permite personalizar las sentencias SQL utilizadas para las distintas acciones, para funcionar mejor en tu situación con tu base de datos. Para ello utiliza las propiedades SQL, InsertSQL, UpdateSQL y DeleteSQL.

   Todas estas propiedades son de tipo TStringList, una lista de cadenas de texto, que acepta varias líneas de SQL. Las cuatro vienen con un editor de propiedades en el IDE. En el IDE, selecciona la propiedad y abre el editor haciendo clic en el botón de puntos suspensivos. En el código, utiliza, por ejemplo InsertSQL.Text o InsertSQL.Add() para ajustar o agregar líneas de instrucciones SQL. Una sentencia puede abarcar varias líneas y termina con punto y coma.

   Además, las cuatro propiedades aceptan parámetros que se describen más adelante.

TSQLQuery.SQL: Personalización básica de SQL

   La propiedad SQL se utiliza normalmente para recuperar los datos de la base de datos. El SQL genérico para esta propiedad es SELECT * FROM fpdev donde fpdev es la tabla que figura en la base de datos.

   El conjunto de datos que devuelve la instrucción SQL genérica está sin pulir. Si muestras el resultado en un TDBGrid, el orden de los registros puede parecer al azar, el orden de las columnas puede no ser lo que quieres y los nombres de campo pueden ser técnicamente correctos, pero no fáciles de usar. Utilizar SQL personalizado puede mejorar esta situación.

 SELECT id AS 'ID', NombreUsuario AS 'Usuario', InstEmail AS 'e-Correo' FROM fpdev ORDER BY id;

   La tabla de la base de datos tiene las columnas id, NombreUsuario y InstEmail. El conjunto de datos que resulta de la consulta anterior utiliza los nombres de campo, tal como figura en la consulta (ID, Usuario y e-Correo), el orden de las columnas, tal como figura en la consulta y los registros son ordenados por su id.

TSQLQuery.InsertSQL, TSQLQuery.UpdateSQL and TSQLQuery.DeleteSQL: Utilización básica de parámetros

   Cuando se asigna una sentencia SELECT a la propiedad SQL de TSQLQuery, este sabe cómo y qué datos obtener de la base de datos.

   Sin embargo, cuando se usan controles enlazados a datos como un DBGrid, TSQLQuery también tendrá que ser capaz de insertar, actualizar y eliminar filas de la base de datos según las acciones del usuario.

   Con el fin de acelerar el desarrollo, SQLQuery puede tratar de deducir las sentencias SQL necesarias. Si existe la propiedad SQL y la propiedad ParseSQL es verdadera (true) (que lo es por defecto), TSQLQuery tratará de generar estas sentenciass del análisis de la propiedad SQL, y almacenará las sentencias deducidas en las propiedades InsertSQL, UpdateSQL y DeleteSQL.

   Sin embargo, a veces las instrucciones generadas no van a funcionar (por ejemplo, cuando se inserta en en tablas con claves primarias autoincrementadas/autonumeradas) o va a ser muy lento. Si es necesario, puedes asignar manualmente las sentenciass.

   Las sentencia InsertSQL, UpdateSQL y DeleteSQL aceptan parámetros. Los nombres de campo utilizados en estas declaraciones deben ser exactamente los mismos que los nombres de campo utilizados en el conjunto de datos. Los nombres de campo en el conjunto de datos pueden ser diferentes de los nombres de columna en la tabla, en función de la sentencia SELECT (ver arriba). Los campos de valores deben ser escritos como el nombre de campo precedido por dos puntos. Por ejemplo:

 INSERT INTO fpdev(id, UserName, InstEmail) VALUES(:id,:UserName,:InstEmail);

   Esta sentencia insertará los valores de id, NombreUsuario y InstEmail del registro actual del conjunto de datos en los campos correspondientes de la tabla fpdev.

   La sentencia INSERT anterior es una sentencia genérica INSERT y sería de uso limitado en TSQLQuery ya que será el mismo que el SQL generado con slqdb por sí mismo. La declaración dada puede dar lugar a errores cuando el campo ID es un campo de incremento automático con clave única. Diversas bases de datos solucionan este problema de diferentes maneras. Por ejemplo, lo siguiente funciona para MySQL.

 INSERT INTO fpdev(id, UserName, InstEmail) VALUES(0,:UserName,:InstEmail) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID();

   La sentencia anterior intenta insertar un nuevo registro, usando 0 (cero) para el campo ID. Si el cero ya se utiliza como clave, entonces la duplicidad es detectada y el ID se actualiza para usar el último ID insertado. Bueno, en realidad un identificador de un incremento mayor que el último utilizado.

   Para una sentencia INSERT es posible que desee utilizar los valores del campo actual del registro seleccionado. Para una sentencia UPDATE, tendrás que usar los valores de los campos como estaban antes de la edición en la cláusula WHERE. Esto también es posible. Los valores de los campos antes de editar deben ser escritas como el nombre del campo precedidon por :old_. Por ejemplo, lo siguiente funciona para MySQL:

 UPDATE fpdev SET UserName=:UserName, InstEmail=:InstEmail WHERE UserName=:Old_UserName;

   La sentencia anterior actualiza los campos UserName y InstEmail de todos los registros en los que UserName es igual al valor anterior de UserName.

   Dejamos como ejercicio para el lector que use los valores de campo actuales y los valores antiguos de los campos en una sentencia DELETE.

Parámetros en TSQLQuery.SQL

   En la mayoría de situaciones, la propiedad SQL de TSQLQuery contendrá la instrucción de selección que a su vez en la mayoría de situaciones no necesita parámetros. Sin embargo, incluso la propiedad SQL puede contener parámetros.

   Los parámetros son una forma muy fácil y potente de filtrar los registros.

   Usar parámetros tienen las siguientes ventajas:

  • no hay necesidad de dar formato a los datos como texto SQL, los argumentos de tipo fecha, etc. (es decir, no es necesario recordar cómo dar formato a una fecha para MySQL, que puede diferir de la de Firebird, no hay necesidad de escapar de los datos de texto como de O'Malley "Horror SQL")
  • mayor rendimiento
  • protección contra ataques de inyección SQL

   El uso de parámetros puede ayudar al rendimiento de la base de datos. La mayoría de las bases de datos permiten utilizar procedimientos almacenados, lo que significa que la sentencia es preparada y almacenada en la base de datos. Un procedimiento almacenado se puede utilizar más de una vez y no requiere el análisis y la planificación de la consulta cada vez que se utiliza, sólo los parámetros cambian cada vez que se utiliza. En situaciones donde se utiliza la misma sentencia un gran número de veces (en el que sólo los valores de los parámetros son diferentes), los procedimientos almacenados pueden ayudar considerablemente a aumentar el rendimiento. Adicionalmente, los ataques de inyección SQL pueden ser mitigados mediante el uso de parámetros.

   Las propiedades InsertSQL, UpdateSQL y DeleteSQL pueden utilizar parámetros predefinidos para el valor actual y el valor previo del campo; la propiedad SQL no lo hace. Puedes crear tus propios parámetros en la propiedad Params.

Ejemplos de consultas SELECT

   En este ejemplo se muestra cómo seleccionar datos utilizando parámetros. También se muestra el uso de alias (... AS ...) en SQL.

 sql_temp.sql.text := 'SELECT id AS ''ID'', UserName AS ''User'', InstEmail AS ''e-mail'' FROM fpdev WHERE InstEmail=:emailsearch;'
 ...
 //Con esto creamos un parámetro llamado emailsearch.
 //ahora podemos dar valor al parámetro:
 sql_temp.params.parambyname('emailsearch').asstring := 'mvancanneyt@freepascal.org';
 ...
 //Y usamos la forma habitual de recuperar los datos,
 //opcionalmente podemos cambiar el valor del parámetro y ejecutar la sentencia de nuevo

Ejemplos de consultas INSERT

   Este ejemplo muestra como inserta un registro nuevo en la tabla, utilizando parámetros:

 sql_temp.sql.text := 'insert into PRODUCTS (ITEMNR,DESCRIPTION) values (:OURITEMNR,:OURDESCRIPTION)'
 ...; 
 sql_temp.Params.ParamByName('OURITEMNR').AsString := 'XXXX';
 sql_temp.Params.ParamByName('OURDESCRIPTION').AsString := 'description';
 sql_temp.ExecSQL;
 SQLTransaction1.Commit; //o  CommitRetaining, dependiendo de la aplicación

   Otra forma de hacerlo sería esta:

 tsqlquery1.appendrecord(['XXXX', 'description']) 
 tsqltransaction1.commit; //materializamos los datos

   Nota

   SQL y Bases de Datos, cómo..., contiene una breve discusión en castellano sobre este tema.

Solución de problemas

Históricos de movimientos

Ver aquí: Registro de sucesos de TSQLConnection para más detalles.

Mensajes de error

Dataset is read-only

   Esto ocurre cuándo especificamos una sentencia que nosotros sabemos que es actualizable, pero a FPC considera que no lo es.

   Ejemplo:

 select p.dob, p.surnam, p.sex from people p;

   El analizador sintáctico de SQL del FPC es bastante simplista y cuando encuentra una coma o un espacio en la clausula FROM que considera que varias tablas están involucradas y establece el conjunto de datos de sólo lectura. En su defensa, no se utilizan habitualmente alias de tablas cuando se trabaja con una única tabla.

   Solución: reescribe la consulta o especifica las clausulas InsertSQL, UpdateSQL y DeleteSQL.