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.

Editor simple de SQLite.

Son tan solo 200 líneas de código y desde ya, la librería de SQLite. Desde ya hay programas potentes y también portables como SQLite Studio, el cual uso y recomiendo. Volviendo al editor simple, lo hice para un uso muy específico y limitado, conecta a la base de datos que le indiquemos, muestra las tablas, permite ejecutar consultar cuyo resultado se muestra en un TDBGrid el cual cuenta con un par de opciones y ejecutar sentencias de actualizaciones que realiza mediante TZConnection.ExecuteDirect.
También tiene una opción para leer de un archivo un script SQL y luego ejecutarlo.
Creo que puede ser útil para quienes comiencen con SQLite, Lazarus y ZeosLib.

El código: descargar

unit principal;

{$mode objfpc}{$H+}

interface

uses
Classes, SysUtils, db, FileUtil, Forms, Controls, Graphics, Dialogs,
StdCtrls, Buttons, DBGrids, DbCtrls, ZConnection, ZDataset;

type
  TConexion=(Conectado, NoConectado);

type

{ TForm1 }

TForm1 = class(TForm)
  BBorrarMemo: TBitBtn;
  BCerrarConsulta: TBitBtn;
  btnSelBD: TBitBtn;
  btnConectar: TBitBtn;
  BCerrar: TBitBtn;
  btnDesconectar: TBitBtn;
  BExecute: TBitBtn;
  BConsulta: TBitBtn;
  btnLeerArchivo: TBitBtn;
  cbAutoCommit: TCheckBox;
  cbAutoSizeCol: TCheckBox;
  DataSource1: TDataSource;
  DBGrid1: TDBGrid;
  DBNavigator1: TDBNavigator;
  edBaseDeDatos: TEdit;
  edConexion: TEdit;
  Label1: TLabel;
  lbTablas: TListBox;
  Memo1: TMemo;
  OpenDialog1: TOpenDialog;
  ZConnection1: TZConnection;
  ZQuery1: TZQuery;
  procedure BBorrarMemoClick(Sender: TObject);
  procedure BCerrarConsultaClick(Sender: TObject);
  procedure BConsultaClick(Sender: TObject);
  procedure BExecuteClick(Sender: TObject);
  procedure btnLeerArchivoClick(Sender: TObject);
  procedure btnSelBDClick(Sender: TObject);
  procedure btnConectarClick(Sender: TObject);
  procedure BCerrarClick(Sender: TObject);
  procedure btnDesconectarClick(Sender: TObject);
  procedure cbAutoSizeColChange(Sender: TObject);
  procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
  procedure FormCreate(Sender: TObject);
  procedure lbTablasDblClick(Sender: TObject);
private
  Conexion:TConexion;
  procedure MuestroTablas;
  function HayConexion:Boolean;
{ private declarations }
public
{ public declarations }
end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.FormCreate(Sender: TObject);
begin
  Conexion:=NoConectado;
end;

procedure TForm1.btnSelBDClick(Sender: TObject);
begin
  if OpenDialog1.Execute then edBaseDeDatos.Text:= OpenDialog1.FileName;
end;

procedure TForm1.btnLeerArchivoClick(Sender: TObject);
begin
 if OpenDialog1.Execute then Memo1.Lines.LoadFromFile(OpenDialog1.FileName);
end;

procedure TForm1.BBorrarMemoClick(Sender: TObject);
begin
  Memo1.Clear;
end;

procedure TForm1.BCerrarConsultaClick(Sender: TObject);
begin
  ZQuery1.Close;
end;

procedure TForm1.BConsultaClick(Sender: TObject);
begin
  if not(HayConexion) then Exit;
  ZQuery1.Close;
  ZQuery1.SQL.Text:=Memo1.Text;
  ZQuery1.Open;
end;

procedure TForm1.BExecuteClick(Sender: TObject);
var
  n:Integer;
begin
  if not(HayConexion) then Exit;
  if ZConnection1.ExecuteDirect(Memo1.Text, n) then Memo1.Lines.Add('OK! '+IntToStr(n)+'      filas.');
end;

procedure TForm1.btnConectarClick(Sender: TObject);
begin
  if Conexion=Conectado then
  begin
    ShowMessage('Hay una conexión establecida, primero desconecte dicha conexión.');
    Exit;
  end;
  if not (FileExists(edBaseDeDatos.Text)) then exit;
  ZConnection1.Database:=edBaseDeDatos.Text;
  if not(cbAutoCommit.Checked) then ZConnection1.AutoCommit:=False;
  ZConnection1.Connect;
  if ZConnection1.Connected then
  begin
    edConexion.Text:='Conectado';
    edConexion.Font.Color:=clGreen;
    Conexion:=Conectado;
    MuestroTablas;
  end;
end;

procedure TForm1.BCerrarClick(Sender: TObject);
begin
  Close;
end;

procedure TForm1.btnDesconectarClick(Sender: TObject);
begin
  ZConnection1.Disconnect;
  edConexion.Text:='Desconectado';
  edConexion.Font.Color:=clRed;
  Conexion:=NoConectado;
  lbTablas.Clear;
end;

procedure TForm1.cbAutoSizeColChange(Sender: TObject);
begin
  if cbAutoSizeCol.Checked then DBGrid1.AutoFillColumns:=False else DBGrid1.AutoFillColumns:=True;
end;

procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
  ZConnection1.Disconnect;
  CloseAction:=caFree;
end;

procedure TForm1.lbTablasDblClick(Sender: TObject);
begin
  if not(HayConexion) then Exit;
  Memo1.Clear;
  Memo1.Lines.Add('SELECT * FROM '+lbTablas.Items[lbTablas.ItemIndex]+';');
  ZQuery1.Close;
  ZQuery1.SQL.Text:=Memo1.Text;
  ZQuery1.Open;
end;

procedure TForm1.MuestroTablas;
var
  ZQTablas:TZQuery;
begin
  lbTablas.Clear;
  ZQTablas:=TZQuery.Create(nil);
  ZQTablas.Connection:=ZConnection1;
  ZQTablas.SQL.Text:='SELECT name FROM sqlite_master WHERE type='+
  QuotedStr('table')+' and name <>'+QuotedStr('sqlite_sequence');
  ZQTablas.Open;
  if ZQTablas.RecordCount <1 then Exit;
  ZQTablas.First;
  while not(ZQTablas.EOF) do
  begin
    lbTablas.AddItem(ZQTablas.FieldByName('name').AsString,lbTablas);
    ZQTablas.Next;
  end;
  ZQTablas.Close;
  FreeAndNil(ZQTablas);
end;

function TForm1.HayConexion: Boolean;
begin
  if not(ZConnection1.Connected) then
  begin
    Memo1.Lines.Add('No hay establecida ninguna conexión.');
    Exit(False);
  end;
  Result:=True;
end;

end.

Formato de fecha y hora en SQLite.

En SQLite el formato de la fecha/hora es YYYY-MM-DD HH:MM.SS.MMM por ejemplo: 2019-06-22 01:00:27.123

Si bien Free Pascal tiene muchas funciones para el tratamiento de fecha y hora, a veces por la configuración regional hay que hacer conversiones.

Usando TFormatSettings:

aFormatSettings.LongDateFormat:='yyyy-mm-dd hh:nn:ss';
aFormatSettings.DateSeparator := '-';
aFormatSettings.TimeSeparator := ':';

Donde aFormatSettings es una variable del tipo TFormatSettings y luego utilizando la función FormatDateTime que devuelve un string:

function FormatDateTime(

const FormatStr: string;

DateTime: TDateTime;

Options: TFormatDateTimeOptions = []

):string;

function FormatDateTime(

const FormatStr: string;

DateTime: TDateTime;

const FormatSettings: TFormatSettings;

Options: TFormatDateTimeOptions = []

):string;

Por ejemplo en una sentencia SQL en WHERE:

sqlWhere:='WHERE regfecha BETWEEN '+QuotedStr(FormatDateTime('YYYY-MM-DD',edFechaDesde.Date))+
' AND '+QuotedStr(FormatDateTime('YYYY-MM-DD',edFechaHasta.Date))';

En este caso no fue necesario TFormatSettings y solo fue fecha, sin hora.

Con TZquery.FieldByName y utilizando componentes TDateTimePicker esto funciona:

ZQReg.FieldByName('regfechahora').AsDateTime:=dtpFecha.Date+dtpHora.Time;

En cuanto los campos del tipo TIME en SQLite; StrToTime('00:00:00') evita el valor nulo que muchas veces conviene evitar.

En SQLite la fecha debe ir entre comillas simples.

Y ante cualquier complicación siempre podemos hacer una función como la siguiente:

function FormatoFechaHoraSQLite(lafechahora: TDateTime): String;
var
  d,m,a,h,mi,s,ms:Word;
  dd,mm,hh,mmi,ss,mms:String;
  lfecha, lhora:String;
begin
  DecodeDate(lafechahora,a,m,d);
  if d < 10 then dd:='0'+IntToStr(d) else dd:=IntToStr(d);
  if m < 10 then mm:='0'+IntToStr(m) else mm:=IntToStr(m);
  lfecha:=IntToStr(a)+'-'+mm+'-'+dd;
  DecodeTime(lafechahora,h,mi,s,ms);
  if h < 10 then hh:='0'+IntToStr(h) else hh:=IntToStr(h);
  if mi < 10 then mmi:='0'+IntToStr(mi) else mmi:=IntToStr(mi);
  if s < 10 then ss:='0'+IntToStr(s) else ss:=IntToStr(s);
  if ms < 10 then mms:='00'+IntToStr(ms) else
    if ms < 100 then mms:='0'+IntToStr(ms) else
      mms:=IntToStr(ms);
  lhora:=hh+':'+mmi+':'+ss+'.'+mms;
  Result:=lfecha+' '+lhora;
end;

O más sencillo usando la función AddChar que se encuentra en la unidad strutils.

function FormatoFechaHoraSQLite2(lafechahora: TDateTime): String;
var
  d,m,a,h,mi,s,ms:Word;
  dd,mm,hh,mmi,ss,mms:String;
  lfecha, lhora:String;
begin
  DecodeDate(lafechahora,a,m,d);
  dd:=AddChar('0',IntToStr(d),2);
  mm:=AddChar('0',IntToStr(m),2);
  lfecha:=IntToStr(a)+'-'+mm+'-'+dd;
  DecodeTime(lafechahora,h,mi,s,ms);
  hh:=AddChar('0',IntToStr(h),2);
  mmi:=AddChar('0',IntToStr(mi),2);
  ss:=AddChar('0',IntToStr(s),2);
  mms:=AddChar('0',IntToStr(ms),3);
  lhora:=hh+':'+mmi+':'+ss+'.'+mms;
  Result:=lfecha+' '+lhora;
end;

DecodeDate y DecodeTime son procedimientos y utiliza variables del tipo Word que son enteros sin signo entre 0 y 65535. Le enviamos un TDateTime y las variables del tipo Word donde se escribirán el año, mes y día (en DecodeDate). No es necesario inicializar las variables, el procedimiento utiliza out:

procedure DecodeDate(

Date: TDateTime;

out Year: Word;

out Month: Word;

out Day: Word

);

SQLite: Limpiar base de datos con VACUUM

El comando VACUUM copia todo el contenido de la base de datos a una base de datos temporal y luego sobre escribe la original, quedando de este modo, "limpia" u optimizada. Como cualquier comando SQLite lo podemos ejecutar ya sea desde consola, cómodamente utilizando la IDE SQLite Studio o desde código Free Pascal mediante una conexión Zeos con ExecuteDirect, por ejemplo:

ZConnection1.ExecuteDirect('VACUUM;');

Desde ya este comando necesita acceso exclusivo, no debe haber ninguna consulta activa ni transacción.

Desde la versión 3.15.0 (14 de octubre de 2016) se puede utilizar este comando en bases de datos adjuntas. Es importante saber que si se intentase ejecutar VACUUM a una base de datos adjunta desde una versión anterior, la adjunta (attached) será ignorada y VACUUM se ejecutará sobre la base de datos principal.

Tablas sin una clave primaria entera: VACUUM puede alterar los ROWIDs. Las tablas que tienen definida una INTEGER PRIMARY KEY no se modifican, solo aquellas que no lo posean. Es decir, si se utilizan los ROWIDs, algo poco recomendable, no se debe utilizar VACUUM ya que muy probablemente modifique sus valores.

Existe el pragma auto_vacuun que se puede habilitar, aunque según indican en el sitio oficial de SQLite "puede generar una fragmentación adicional de archivos de base de datos. Y auto_vacuum no compacta las páginas parcialmente rellenas de la base de datos como sí lo hace VACUUM.".

Este comando resulta extremadamente útil cuando se están realizando pruebas en la base de datos, generalmente en la etapa de diseño, una vez finalizas las mismas, usar VACUUM para una limpieza que además, reducirá el tamaño del archivo.

Como experiencia propia, estuve realizando pruebas con tipos de datos BLOB para almacenar imágenes, finalizada esta etapa, la base de datos estaba cerca de los 7 MB, luego de VACUUM su tamaño se redujo a 700 KB.

SQLiteStudio: mostrar más de 1.000 filas

El valor predeterminado (por default) de filas o registros a mostrar en una consulta (query) en SQLiteStudio es 1.000 (mil). Por eso cuando hacemos una consulta que arroja un resultado de más de mil registros, solo se muestran los primeros mil únicamente, como si se hiciera un LIMIT 1000;

Para modificar esto y cambiar ese valor por otro, ya sea mayor o menor, solo debemos acceder a la configuración a través del menú Tools, ítem Open configuration dialog. O presionando F2.

Ahora en la barra izquierda seleccionamos Data browsing.

En Number of data rows per page establecemos el número deseado de filas a mostrar y finalmente presionamos OK.

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.

SQLite – Triggers: explicación y ejemplos

Un trigger es un evento que se lanza cuando ocurre algo determinado en una base de datos y se ejecuta una sentencia SQL. Ese “algo” que ocurre puede ser que se borre un registro (fila) de una determinada tabla, que se agregue un registro o que se modifique uno o más campos de una tabla, es decir, un INSERT, DELETE o UPDATE.

¿Cuándo se declara el trigger? Puede declararse en cualquier momento y siempre de acuerdo a las necesidades del caso, siendo lo normal hacerlo al momento de crear las tablas, como los índices y constraints.

No es necesario que las tablas actuantes en el trigger estén relacionadas mediante una Foreign Key.

Existe una IDE para SQLite muy práctica para este tipo de cosas, especialmente hasta que le tomemos la mano, nos es de gran ayuda, que es SQLiteStudio (está en inglés) y es de código abierto.

Uno puede, no obstante, prescindir de los triggers de SQLite y hacerlo a mano, es decir, mediante programación, en mi caso, con Free Pascal desde Lazarus, de hecho lo estuve haciendo hasta ahora, por “seguridad”, por preferir tener el control total especialmente en cuanto a la validación de por ejemplo valores duplicados, todavía lo hago, no me llevo bien con el manejo de excepciones, además SQLite carece de Stored Procedures, de momento son excusas válidas. Pero para borrar un registro en otra tabla, actualizar un dato, o agregar, casos simples, empecé a utilizar triggers, más precisamente hace dos días y por suerte además de funcionar correctamente, no es algo difícil de implementar.

Los ejemplos son casos reales de un programa de control de proveedores e insumos.


INSERT:

Un trigger que cree un registro en otra tabla cada vez que el usuario da de alta un insumo.

CREATE TRIGGER agregoinsu
         AFTER INSERT
            ON prod
BEGIN
    INSERT INTO movinsu (
                            idcompra,
                            idprod,
                            fecha,
                            cantidad,
                            precio,
                            total,
                            saldo
                        )
                        VALUES (
                            0,
                            new.id,
                            '2016-01-01',
                            new.ini,
                            0,
                            0,
                            0
                        );
END;

Al trigger lo nombré “agregoinsu”, luego hay que indicar cuando se dispara el trigger, antes o después y antes o después de ¿qué?, en este caso después (AFTER) de que se produjo el INSERT. ¿en qué tabla?, ON prod (en la tabla prod). Nota: la tabla prod debería llamarse insumos, pero se llama prod por motivos que no vale la pena aclarar. Hasta acá definimos cuando se lanza el trigger, ahora debemos indicar que se hace y eso lo hacemos mediante sentencias SQL comprendidas entre BEGIN y END; En este caso se indica que se inserte un registro en la tabla “movinsu” que posee un campo autoincremental “id” que se omite porque se encarga SQLite, el resto de las columnas o campos los especifico. Ahora debo definir los valores de esa nueva fila en “movinsu” y acá aparece la palabra new. Para triggers de inserción, se utiliza new, para triggers de eliminación se utiliza old y para triggers de actualización se pueden utilizar ambos (new y old). “new.id” significa que el nuevo registro en movinsu, en la columna idprod, irá el valor del nuevo (new) id (prod.id) y en la columna cantidad ira el valor “new.ini” que viene de prod.ini. Resumiendo, cada vez que se agregue, o dé de alta, un insumo (prod) se insertará una fila en la tabla movinsu con los valores especificados.

Este trigger debe definirse sobre la tabla que lanza el evento, en este caso, la tabla prod.

DELETE:

Lo contrario al ejemplo anterior, un trigger que elimine un registro en la tabla movinsu cuando se elimina un insumo de la tabla prod.

CREATE TRIGGER borroinsu
         AFTER DELETE
            ON prod
BEGIN
    DELETE FROM movinsu
          WHERE (idprod = old.id) AND
                (idcompra = 0);
END;

Este trigger es muy simple, AFTER (después) de DELETE (borrar) un registro ON (en) la tabla prod se ejecuta la sentencia SQL comprendida entre BEGIN y END, borrar de la tabla movinsu donde “old.id” sea igual a idprod (de la tabla movinsu). La otra condición idcompra=0 es porque con ese ID identifico el stock inicial; en realidad podría obviar esto, ya que antes de permitir borrar un insumos chequeo primero que no tenga movimientos registrados, si los tiene no permito su eliminación, pero por las dudas prefiero comprobarlo dos veces y no dejar que un trigger se dispare y borre alegremente todos los movimientos de un insumo, no nos olvidemos que cualquiera con un poco de conocimientos puede abrir la tabla y borrar un insumo y el trigger se dispara igual, por ejemplo, un usuario intenta borrar un insumo y el programa no lo deja porque tiene 20 registraciones, el usuario debe primero borrar todas las registraciones y luego borrar el insumo, puede tentarse de meter mano a la base de datos y borrarlo, pues bien, en este caso, el trigger con el condicional (idcompra=0) solo borrará el registro correspondiente al stock inicial dejando “vivas” las otras registraciones. Claro que si lo que se pretende es que el trigger arrase con todo quedaría así:

CREATE TRIGGER borroinsu
         AFTER DELETE
            ON prod
BEGIN
    DELETE FROM movinsu
          WHERE (idprod = old.id);
END;


“old.id” es el campo “id” del registro que se borró. Como es un trigger del tipo delete, solo se puede utilizar old para referenciar un campo.

UPDATE:

En update se puede usar tanto old como new para referenciar campos. Este trigger se lanza cuando se modifica el stock inicial de un insumo (campo “ini”) en la tabla prod y modifica el campo “cantidad” en la tabla movinsu.

CREATE TRIGGER modificoinsu
         AFTER UPDATE OF ini
            ON prod
BEGIN
    UPDATE movinsu
       SET cantidad = new.ini
     WHERE (idprod = new.id) AND
           (idcompra = 0);
END;

Traduciendo un poco, se lanza después (AFTER) de una modificación (UPDATE) del campo “ini” de la tabla prod (ON prod) y se ejecuta la actualización (UPDATE) de la tabla movinsu, se establece (SET) el campo “cantidad” con el valor de “new.ini” (el nuevo stock inicial) y la claúsula WHERE es la misma que el ejemplo anterior. Si bien el campo “id” no cambia, se debe referenciarlo con new o con old.

Son tres ejemplos sencillos para iniciarse en el tema triggers.

Documentación consultada: (en inglés)

https://sqlite.org/lang_createtrigger.html

https://www.tutorialspoint.com/sqlite/sqlite_triggers.htm

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.

Volver a cero el rowid de SQLite

Como reiniciar o “resetear” el rowid de una tabla en SQLite? Muchas veces definimos el famoso campo ID como integer, primary key, autoincrement, unique para que SQLite se encargue de él y lo hace, guardando el valor del último registro y cada vez que agregamos uno toma esa valor y lo incrementa en uno. Es por eso que si tenemos 10 registros con ID del 1 al 10 y borramos 5 y luego insertamos uno, el valor de este último será 11 y no 6. Si tememos una tabla con 500 filas y las borramos todas, el próximo registro, el ID será 501, si lo que buscamos es que sea 1 y no 501, hay una solución, luego de borrar toda la tabla ejecutamos el siguiente comando:

DELETE FROM SQLITE_SEQUENCE WHERE name='tutabla';

Ejemplo: borrar la tabla cuentas

DELETE FROM cuentas; DELETE FROM SQLITE_SEQUENCE WHERE name='cuentas';

Si devuelve el siguiente error: No such table ‘SQLITE_SEQUENCE’ se debe a que en el schema no está definido, sqlite_sequence se crea al definir una columna como integer, primary key, autoincremental, unique.

Es muy útil para tablas que usamos como temporales, no del tipo temporal que es distinto.

Otro ejemplo con código Free Pascal:

dmrc.ZQtemp.SQL.Text:='DELETE FROM tasiento; DELETE FROM SQLITE_SEQUENCE WHERE name=''tasiento'';';



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.

SQLite crear una base de datos con código

A diferencia de otras bases de dato SQL, SQLite carece del famoso CREATE DATABASE porque si la base de datos no existe, la crea automáticamente.

En tiempo de diseño solo debemos completar la propiedad Database de un componente Zconnection y activar la propiedad Connected, con eso ya se creo la base de datos.

Con código sería así:
   
ZConnection1.Database:='prueba.db';
ZConnection1.Connect;

Aclaración: Zconnection es un componente de ZeosLib, un conjunto de componentes para manejar bases de datos.

Conectar SQLite y editar una tabla

En este video vemos como hacer un ABM de una tabla en 2 minutos sin escribir código. Desde ya un ABM es mucho más que lo que se muestra, pero que sirva como punto de partida. Ver en pantalla completa.



La conexión se realiza mediante Zconnection, componente del paquete Zeos Lib, ZQuery y un DataSource.

Soltamos los 3 componentes en el Form, indicamos la base de datos en ZConnection1 y el protocolo sqlite-3, conectamos. No es necesario nada más como se puede ver.

ZQuery1: indicamos que se conecta via ZConnection1, escribimos la consulta en SQL. Antes de activar, vamos a DataSource1 y le indicamos que el DataSet vendrá de ZQuery1. Activamos ZQuery1.

Agreamos un DBGrid y le indicamos que el DataSource será DataSource1.

Agregamos un DBNavigator y de igual forma que con el DBGrid le establecemos el DataSource1.

Por ningún motivo usar ZTable nunca, no tiene sentido y creo que no eliminan el componente por razones de compatibilidad con versiones anteriores.

Todo esto se puede hacer en tiempo de ejecución, escribiendo el código, que no es mucho por cierto y es lo más normal cuando se está desarrollando un programa y no un ejemplo. También se utilizan Data Modules para soltar ahí todos los componentes no visuales y trabajar más cómodo.