TZQuery.ExecSQL

Actualización 26-12-2020: downgrade de Zeos 7.2.6 (también probé la 7.2.8) a Zeos 7.1.3.a stable del año del jopo.

No hace mucho actualicé tanto el IDE como el compilador, algunos componentes que forman parte de Lazarus se actualizan y otros, como ZeosLib no. Años usando la versión estable 7.1.3 (si no me equivoco) ya que las primeras versiones de la 7.2 me tiraba errores por todos lados. Finalmente decidí ir por la 7.2.6 y al principio iba todo bien, pero un sistema de los primeros que hice, hace ya más de 3 años, y sin las mejores técnicas de programación precisamente, me pidieron una modificación, nada del otro mundo y ahí empezaron los problemas con el querido Zeos. La documentación de los cambios de Zeos no es la mejor del mundo.

En dicho programa, tenía mucho código que actualizaba la base de datos con TZQuery en lugar de TZConnection.ExecuteDirect. Motivo: se pueden utilizar parámetros, me resulta más cómodo. Por ejemplo tenía códigos de este tipo:

ZQTHab.SQL.Text:='DELETE FROM tashaber;';
ZQTHab.Open;
ZQTHab.Close; 

Funcionaba sin problemas.

Ahora arroja un error: "Can not open a Resultset." 

 

Se soluciona con TZQuery.ExecSQL que la verdad no sé si es un método nuevo o si siempre existió.

ZQTHab.SQL.Text:='DELETE FROM tashaber;';
ZQTHab.ExecSQL; 

Lo bueno es que no se necesita ni abrir ni cerrar la consulta, más legible.

También me saltaron errores de base de datos bloqueada y tuve problemas con la edición e inserción de registros. Pues bien, hoy salió una nueva versión de ZeosLib, la 7.2.8 que no corrige esos bugs, pero anuncia que los mismos serán corregidos en la versión 8.0.

"When using Cached Updates, it is not possible to add a row and then edit
that row before posting to the database. This bug cannot be fixed in Zeos 7.2.
It has been fixed in the upcoming Zeos 8.0. Please use database transactions
instead.".

Respecto de la base de datos bloqueada, era cuando usaba una segunda conexión a la base de datos, tuve que eliminar esa segunda conexión. El tema sin solución es que el programa se usa en red y cuando dos o más usuarios se conectan a la base de datos se bloquea. Además era una de las gracias de utilizar el conector de Zeos con la propiedad Autocommit en true y funcionaba de maravilla.

Deberé, otra vez, regresar a la versión 7.1.3 donde todo funcionaba bien.

TZConnection.ExecuteDirect y TZQuery.

Estos dos componentes pertenecen a Zeos Lib.
TZConnection se utiliza primariamente para establecer una conexión a una base de datos. Entre sus métodos está ExecuteDirect, una función sobrecargada (overloaded). Nada mejor que ver el código fuente:

function ExecuteDirect(SQL:string):boolean;overload;
function ExecuteDirect(SQL:string; var RowsAffected:integer):boolean;overload;

Si únicamente enviamos un parámetro, el string con la sentencia SQL, entonces la primera será llamada. Si usamos los dos parámetros, entonces se llamará a la segunda. Breve aclaración de overload.

Generalmente ExecuteDirect se utiliza para todo lo referido a actualizar la base de datos, por ejemplo: UPDATE, CREATE, DELETE, INSERT, VACUUM.

Como vemos, esta función siempre retorna un boolean en ambas versiones, que será True si hubo éxito o False si hubo un error.
Si además usamos el segundo parámetro, que como observamos es por referencia, el mismo se actualizará con el número de filas afectadas.

Por ejemplo, si mandamos VACUUM, siempre devolverá 0 (cero); en cambio si utilizamos un UPDATE nos devolverá la cantidad de registros actualizados.

Una vez más, veamos el código fuente de la implementación de las funciones:

{**
Executes the SQL statement immediately without the need of a TZQuery component
@param SQL the statement to be executed.
Returns an indication if execution was succesfull.
}

function TZAbstractConnection.ExecuteDirect(SQL : String) : boolean;
var
  dummy : Integer;
begin
  result:= ExecuteDirect(SQL,dummy);
end;

{**
Executes the SQL statement immediately without the need of a TZQuery component
@param SQL the statement to be executed.
@param RowsAffected the number of rows that were affected by the statement.
Returns an indication if execution was succesfull.
}

function TZAbstractConnection.ExecuteDirect(SQL: string; var RowsAffected: integer):boolean;
var
  stmt : IZStatement;
begin
  try
    try
      CheckConnected;
      stmt := DbcConnection.CreateStatement;
      RowsAffected:= stmt.ExecuteUpdate(SQL);
      result := (RowsAffected <> -1);
    except
      RowsAffected := -1;
      result := False;
      raise; {------ added by Henk 09-10-2012 --------}
    end;
  finally
    stmt:=nil;
  end;
end;

Ahora veamos la siguiente línea de código:

if ZConnection1.ExecuteDirect(Memo1.Text, n) then Memo1.Lines.Add('OK! '+IntToStr(n)+' filas.');

Sí bien es casi rídiculo hacer esto, un SELECT con ExecuteDirect, se puede, claro que siempre retornará cero, aunque la tabla tenga 500 filas.
En realidad también se puede, por ejemplo usar VACUUM desde un consulta TZQuery. El tema es saber cual de los dos métodos utilizar según lo que necesitemos, desde ya, una consulta será con TZQuery. Un INSERT puede ser tanto con ExecuteDirect o con los métodos de TZQuery, nuevamente, según lo que necesitemos y el estilo propio de cada programador.

Este UPDATE .. SET en realidad no hace nada, pero es válido como ejemplo, ExecuteDirect devuelve 51 en la variable n pasada por referencia, que desde ya, coincide con la cantidad total de filas de la tabla.

TZQuery:

De entrada conviene aclarar una especie de mito que hay de que siempre debe estar asociada con componente TDataSource, esto es falso, así de simple. Solo necesitaremos un TDataSource si los datos de la consulta deben ser mostrados en otros componentes, como ser un TDBGrid, TDBEdit, TDBLookUpComboBox, etc. Muchas veces veo ejemplos de LazReport donde se incluye innecesariamente un TDataSource.

Otra cosa elemental que no se debe intentar hacer, es editar una consulta que contiene JOIN, el error será inevitable. Es un error muy común tener una consulta con JOIN en un TDBGrid y querer actualizar una fila.

Para insertar un registro en una consulta del tipo SELECT campos FROM tabla (y puede también contener WHERE pero nunca JOIN), primero se debe invocar al método INSERT de TZQUery:

ZQuery1.Insert;

Luego, lo más común es utlizar el método FieldByName:

ZQuery1.FieldByName('nombre').asString:=edNombre.Text;
ZQuery1.FieldByName('edad').asInteger:=nEdad;

Y finalmente se usa el método Post para concretar la transacción. Si la propiedad Autocommit de TZConnection es True, entonces la transacción será inmediata:

ZQuery1.Post;

Un campo INTEGER con AUTOINCREMENT: al hacer un INSERT debe ignorarse siempre, ya sea utilizando ExecuteDirect o TZquery.

También puede utilizarse un TZQuery para un UPDATE, SET, etc.:


DataM.ZQa.Close;
DataM.ZQa.SQL.Text:='UPDATE reg SET saldo=saldo-:importe WHERE banco=:elbanco AND fecha>:regfecha;';
DataM.ZQa.Params.ParamByName('elbanco').AsInteger:=elbanco;
DataM.ZQa.Params.ParamByName('regfecha').AsString:=regfecha;
DataM.ZQa.Params.ParamByName('importe').AsCurrency:=importe;
DataM.ZQa.Open;
DataM.ZQa.Close;

Ventaja: se puede usar Params, algo que no se puede con ExecuteDirect.

¿Cómo usar dos tablas de distintas bases de datos en la misma consulta?

Para adjuntar una tabla a un DataSet, en este caso TZQuery, y que no produzca un error, la solución que no encontré en ningún lado, la descubrí mediante el sistema de prueba y error hasta que salga. Y como suele suceder cuando no se encuentra algo en toda la web, es porque ese algo, es muy obvio y este caso no fue la excepción.

Primero establecemos la conexión con una de las dos bases de datos mediante el componente TZConnection. Luego hacemos una consulta para adjuntar la base de datos, la abrimos y la cerramos, listo, ya está adjuntada. Luego escribimos la consulta que necesitemos.

Ejemplo:

ZQ.Close;
ZQ.SQL.Text:='ATTACH DATABASE '+QuotedStr(strDB)+' AS realgestdb;';
ZQ.Open;
ZQ.Close;
ZQ.SQL.Text:='SELECT cfecha, cprovid, nombre, ccomp, realgestdb.comprob.ccomprobalias, cletracomp, '+
'cnrocomp, ccaeocai, ccai, cuit, cnetogravado, cnogravado, cimpinternos, cpercib, cperciva, ctasaiva, '+
'civa, cnetogravado1, ctasaiva1, civa1, cnetogravado2, ctasaiva2, civa2, ctotal '+
'FROM ccompras '+
'INNER JOIN cprov ON cprovid=provid '+
'INNER JOIN realgestdb.comprob ON ccomp=realgestdb.comprob.id '+
'WHERE cfecha BETWEEN '+QuotedStr(desde)+' AND '+QuotedStr(hasta)+
'ORDER BY cfecha, cprovid ;';
ZQ.Open;

Las tres primera lineas realizan el ATTACH DATABE y ya queda disponible para cualquier consulta que se realice en el mismo dataset, hasta que se des adjunte, para ello:

ZQ.Close;
ZQ.SQL.Text:='DETACH DATABASE '+QuotedStr('realgestdb')+';';
ZQ.Open;
ZQ.Close;

Es importante para adjuntar, enviar el path completo de la base de datos y entre comillas simples, para eso nada más cómodo que la función QuotedStr. En este caso strDB es una variable del tipo string que contiene el path completo de la base de datos a adjuntar. Luego con AS le establecemos un alias para luego referenciarla en las consultas SQL. El alias puede ser cualquier nombre.
Pero ojo, que para realizar el DETACH DATABASE se utiliza el Alias, no el path completo de la base de datos. Esto se debe que, al menos SQLite, permite adjuntar variar veces una misma base de datos bajo distintos Alias (AS).

La cuarta línea cierra la consulta. La quinta, cambia la consulta y para acceder al campo ccomprobalias de la tabla comprob de la base de datos adjuntada bajo el alias de realgestdb lo hacemos de la forma Alias.tabla.campo.

SQL: Insertar registros en una tabla con campo auto increment.

¿Cómo ejecutar correctamente el comando SQL para insertar filas que contienen una columna auto incremental?

Por ejemplo, una tabla (tabla1) con 4 campos: id, nombre, apellido y edad.

CREATE TABLE tabla1 (id INTEGER UNIQUE NOT NULL PRIMARY KEY AUTOINCREMENT, nombre VARCHAR(50), apellido VARCHAR(50), edad INTEGER);

Error común:

INSERT INTO tabla1 VALUES ('Juan', 'Pérez', 25);

Esto arrojará un error del tipo "la tabla tiene 4 columnas pero solo se proporcionan 3 valores" y es cierto, pero claro, no se puede pasar el valor id porque el mismo debe establecerlo SQL.
La solución es simplemente especificar los campos:

INSERT INTO tabla1 (nombre, apellido, edad) VALUES ('Juan', 'Pérez', 25);

En caso de utilizar Zeos esto se realiza mediante ZConnection1.ExecuteDirect o también puede hacerse mediante el dataset ZQuery que sería algo así: (con la tabla ya creada)

ZQuery1.SQL.Text('SELECT * FROM tabla1;');
ZQuery1.Insert;
ZQuery1.FieldByName('nombre').AsString:='Juan';
ZQuery1.FieldByName('apellido').AsString:='Pérez';
ZQuery1.FieldByName('age').AsInteger:=25;
ZQuery1.Post

ZQuery y el bug en valores del tipo boolean en SQLite.

TZQuery es un componente de ZeosLib que tiene un error, al guardar un valor del tipo boolean, lo hace como Y/N en lugar 0/1.

Por ejemplo:

ZQuery1.FieldByName('puntual').asBoolean:=True;

Almacena en la columna puntual el valor Y en lugar de un 1.

Luego si se realiza una consulta ... WHERE puntual ... o WHERE NOT(puntual) no se obtendrán los resultados deseados, habrá que cambiar la consulta por WHERE puntual='Y' o WHERE puntual='N'.

Por qué SQLite lo acepta? Porque SQLite tiene la famosa particularidad de trabajar los tipos de datos por afinidad, para SQLite en definitiva es un caracter, ni siquiera le importa que no sea númerico, acepta Y/N y también una M. Error de SQLite? No, guste o no, con ventajas y desventajas, SQLite es así.

Error de ZeosLib? Sí, de hecho el mismo fue reportado (ver ticket) y solucionado para versiones 7.2 y obviamente, posteriores.

Soluciones, varias:

Conviviendo con el bug, podría usarse asInteger, por ejemplo:

ZQuery1.FieldByName('puntual').asInteger:=1;

No lo he chequeado pero debería funcionar.

Actualizar ZeosLib a una versión 7.2 o posterior.

Poner esta opción en el componente, comunmente ZQuery:

ZQuery.Properties.Values['BindOrdinalBoolValues'] := 'True';

Esto se puede hacer también desde el inspector de objetos, en Properties. 
Solución que obtuve desde el ticket del bug, aportada por uno de los desarrolladores de Zeos. Y teniendo en cuenta que hoy, la última versión estable es la 7.1.4 es una buena alternativa para quienes optamos siempre por versiones estables.

Crear tablas en SQLite con código Free Pascal

Como casi todo en programación, hay varias formas de hacer una misma tarea y ésta no es la excepción. Crearemos una base de datos y una tabla. También le agregaremos un registro a la tabla, todo con código Free Pascal desde Lazarus y utilizando un solo componente de Zeos Lib que no facilita las tareas con las bases de datos.

Ya he escrito que para crear una base de datos en SQLite solo hay que establecer la conexión, si la base de datos no existe, entonces SQLite la crea.
Solo necesitamos un TZConnection que lo soltaremos en el Form o en el Data Module.

Nota: al ZConnection1 en la propiedad protocol desde el inspector de objetos le seleccionamos sqlite3.

Este ejemplo consiste en una función que crea una base de datos, una tabla y un registro en la misma. Si todo salió bien se devuelve True, caso contrario, False.

function CrearEmpresas: Boolean;
var
  ret:Boolean;
begin
  ret:=True;
  if not FileExists('/home/programa1/empresas.db') then
  begin
    if ZConnection1.Connected then ZConnection1.Disconnect;
    try
      ZConnection1.Database:='/home/programa1/empresas.db';
      ZConnection1.Connect;
      ZConnection1.ExecuteDirect('CREATE TABLE IF NOT EXISTS empre ( '+
      'idempre INTEGER      PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, '+
      'nombre  VARCHAR (40) NOT NULL, '+
      'cuit    VARCHAR (13) NOT NULL, '+
      'db      VARCHAR (40) NOT NULL, '+
      'dir     VARCHAR (80) NOT NULL );');
      ZConnection1.ExecuteDirect('INSERT INTO empre VALUES '+
      '(1,''DEMO EMPRESA S.A.'',''20-12345678-9'',''demodb.db'','+''''+CurrentDirectory+''');');
      ZConnection1.Disconnect;
    Except
      ret:=False;
    end;
    ZConnection1.Disconnect;
  end;
  CrearEmpresas:=ret;
end;

Para evitar errores, primero verificamos que el archivo no exista y luego también chequeamos que ZConnection1 no esté conectado, pues si lo está no nos sirve, dado que para estar conectado lo está a una base de datos, y necesitamos crear la base de datos lo cual sucede al momento de efectuar la conexión. Por eso, si Connected devuelve True, lo desconectamos. Lo que sigue lo “envolvemos” en un try / Except y si salta algún error la función retornará False.

Ya asegurándonos de que ZConnection1 está desconectado, le establecemos la base de datos con path completo aunque sin path creará la DB en el directorio de ejecución, por lo tanto puede obviarse el path y poner ‘empresas.db’ por ejemplo. Conectamos y en ese momento es cuando la DB es creada. Acto seguido, valiéndonos del método ExecuteDirect incluiremos el mismo código que escribiríamos en una terminal o consola para SQLite con la diferencia de que necesitamos el uso de comillas dobles en la inserción de datos. Con el primer ExecuteDirect creamos la tabla empresas.db si no existe, algo que sobra porque acabamos de crear la DB. Con el segundo, agregamos un registro, ahora llamado fila, lo cual es correcto pero no me gusta. Y ahí debemos utilizar las comillas dobles, pulsando dos veces la comilla simple, no utilizar nunca la doble. El 1 al tratase de un entero, no necesita comillas, es un valor numérico, el resto de los campo (ahora columnas) al ser de texto (VARCHAR) si necesita estar entre comillas simples, pero al estar ya dentro de un texto entre comillas, para indicarle al compilador que la comilla no finaliza el texto, en lugar de una comilla simple escribimos dos comillas simples, el dato, y nuevamente dos comillas simples. También se puede usar la función QuotedStr para evitar tantos apóstrofes.


Ahora pasemos a explicar esto:

‘ ‘demodb.db’ ‘,  ‘+ ‘ ‘ ‘ ‘ + CurrentDirectory + ‘ ‘ ‘ ) ; ‘ ) ;

corto la cadena de caracteres luego de la coma, la finalizo con la comilla simple y le concateno el directorio actual:

‘ ‘ ‘ ‘  la primer comilla inicia el texto y la última lo cierra, en medio dos comillas imprimen una comilla en la cadena.

+ para agregar el string que me devuelve CurrentDirectory, nuevamente + ‘ ‘ ‘  tres comillas, la primera para iniciar nuevamente un texto, la segunda y tercera para imprimir una comilla, luego ) ; ‘ esa última comilla cierra.

Lo que sigue es un Disconnect para cerrar la conexión con la base de datos.