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.

Recorrer un DataSet y actualizar registros

Si queremos actualizar un campo de una fila o registro mientras recorremos el TDataSet por ejemplo mediante un while, obtendremos un error en tiempo de ejecución. Con un ejemplo será más sencillo:

WHILE NOT ZQuery1.EOF DO
begin
  ZQuery1.Edit;
  ZQuery1.FieldByName('alguncampo').AsInteger:=0;   
  ZQuery1.Post
  ZQuery1..Next;
end;

El código precendente nos dará el error y es correcto que lo haga, porque no situamos el puntero al comienzo del DataSet que sería con ZQuery1.First.

ZQuery1.First;
WHILE NOT ZQuery1.EOF DO
begin
  ZQuery1.Edit;
  ZQuery1.FieldByName('alguncampo').AsInteger:=0;   
  ZQuery1.Post
  ZQuery1..Next;
end;

Otra manera de resolver esto es mediante el uso de ExecuteDirect (y más rápido si se trata de un gran volumen), si tenemos un identificador único, mejor:

ZQuery1.First;
WHILE NOT ZQuery1.EOF DO
begin
  ZConnection1.ExecuteDirect('UPDATE tabla SET alguncampo=0 WHERE    identificaorunico='+ZQuery1.FieldByName('id').AsString'+' ;');
  ZQuery1..Next;
end;

Se recomienda hacer esto cuando no podamos hacerlo mediante una sentencia SQL UPDATE, ya que puede ser lento dependiendo del volumen de datos.

Insert masivo y rápido en SQLite

El que sabe, sabe; y el que no, se pasa horas buscando, por eso este simple post para algo tan simple, para los que estamos eternamente aprendiendo.

No hay ningún problema con la ejecución directa de sentencias SQL cuando se trata de pocos registros a insertar, haríamos algo así: (pseudo código)

While not Eof do
  ExecuteDitect('lo que sea');

Si nuestro conector con la base de datos tiene la propiedad autocommit en True y son pocos registros, el usuario no lo notará. El problema es que cada vez que se completa una transacción, SQLite requiere dos completas rotaciones del plato del disco, tendiendo en cuenta unas 7.200 rotaciones por minuto, con suerte, viento a favor y sin usar progressbar, podríamos insertar 60 registros por segundo, es decir, 1.200 registros tomaría 20 segundos, entonces debemos incluir una barra de progreso para que el usuario no piense que el programa dejó de funcionar, la querida barra de progreso relentizará aún más el proceso. Hasta aquí la explicación de por qué demora tanto.

Solución: BEGIN …. COMMIT es decir, encerrar las transacciones entre un BEGIN y un COMMIT.

Ejemplo con el componente ZConnection de ZeosLib:

if ZConnection1.Connected then ZConnection1.Disconnect;
ZConnection1.AutoCommit:=False;
Zconnection1.Connect;
ZConnection1.ExecuteDirect('BEGIN; ');
while not EOF(f) do
begin
  ReadLn(f,s);
  ZConnection1.ExecuteDirect(s);
end;
CloseFile(f);
ZConnection1.ExecuteDirect('COMMIT; ');
ZConnection1.Disconnect;

En este caso, f es un archivo de texto plano que contiene lenguaje SQL. La velocidad es increíble, un archivo de 7,7 MB  en menos de 2 segundos, para más de 45.000 registros de 12 campos.

La propiedad autocommit de ZConnection1 debe estar en False, podemos hacerlo en el inspector de objetos de Lazarus o por código.

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.