DISQLite3: Wiki

Here is a demo which shows how to write any TStream to a database BLOB and read it back. It also describes the advantages of the different methods. An up to date version of this demo is distributed with the DISQLite3 package.

A simple example project which shows how to use sqlite3_get_table() is available here. An updated version of this demo is distributed with the DISQLite3 package!

Need to compress database text and BLOBs? You can easily add ZLib compression – just like MySQL – to DISQLite3 by registering the custom SQL functions provided in the DISQLite3ZLib.pas source code unit:

• COMPRESS(text_or_blob_to_compress) compresses text or BLOB. The compressed string or BLOB can be uncompressed with UNCOMPRESS(). The argument's type is maintained, which can result in text with #0 characters.¹⁾ If the argument can not be compressed, an error is returned. Any other argument type but text or BLOB is returned unchanged. Zero-length arguments are never compressed, the plain zlib compressed output is returned for all others.

• UNCOMPRESS(compressed_text_or_blob) uncompresses text or BLOB compresses by the COMPRESS() function. If the argument is not text or BLOB, it is returned unchanged. If the argument can not be uncompressed, an error is returned. Any other argument type but text or BLOB is returned unchanged.

The DISQLite3ZLib.pas source code is distributed with the DISQLite3 package!

It is not required, but you can optionally CAST the COMPRESS()ed text to a blob like this:

SELECT CAST(COMPRESS('Text to compress') AS BLOB);

An UNCOMPRESS()ed BLOB can of course also be CAST to text:

SELECT CAST(UNCOMPRESS(Compressed_Blob) AS TEXT);

A simple view allows you to access compressed data as if it was uncompressed:

/* Table to store compressed data. */
CREATE TABLE IF NOT EXISTS Compressed (t TEXT);
 
/* View to uncompress data on the fly. */
CREATE VIEW IF NOT EXISTS Uncompressed AS SELECT UNCOMPRESS(t) AS t FROM Compressed;

If you also want to handle uncompressed inserts and updates via the view, then also create the following triggers:

/* Trigger on view to insert compressed data */
CREATE TRIGGER Uncompressed_Insert INSTEAD OF INSERT ON Uncompressed
  BEGIN
    INSERT INTO Compressed (t) VALUES (COMPRESS(NEW.t));
  END;
 
/* Trigger on view to update compressed data */
CREATE TRIGGER Uncompressed_Update INSTEAD OF UPDATE ON Uncompressed
  BEGIN
    UPDATE Compressed SET t = COMPRESS(NEW.t) WHERE Compressed.RowID = OLD.RowID;
  END;

Delphi exceptions are an easy way to handle transactions. This is the basic structure:

{ Using the TDISQLite3Database component. }
uses
  DISQLite3Database;
 
procedure TDISQLite3Database_Transaction_Usage;
var
  DB: TDISQLite3Database;
begin
  { ... assume database is already opened ... }
  DB.StartTransaction;
  try
    { ...a series of sql commands to update or insert ... }
    DB.Commit;
  except
    DB.Rollback;
    raise;
  end;
end;

{ Directly using the DISQLite3 API. }
uses
  DISQLite3Api;
 
procedure DISQLite3_API_Transaction_Usage;
var
  DB: sqlite3;
begin
  { ... assume database is already connected ... }
  sqlite3_exec_fast(DB, 'BEGIN TRANSACTION;');
  try
    { ...a series of sql commands to update or insert ... }
    sqlite3_exec_fast(DB, 'COMMIT TRANSACTION;');
  except
    sqlite3_exec_fast(DB, 'ROLLBACK TRANSACTION;');
    raise;
  end;
end;

Please take care that your code raises an exception wherever something can go wrong. TDISQLite3Database and TDISQLite3Statement automatically do so. Direct DISQLite3 API calls do not, but you can wrap them with sqlite3_check() to turn any error result into an exception.

There is no difference between the two – END [TRANSACTION] is the same as COMMIT [TRANSACTION].

How can you use sqlite3_create_collation() with the TDISQLite3Database component? It is simple: Just pass the TDISQLite3Database.Handle property to sqlite3_create_collation():

sqlite3_create_collation(
  DB.Handle, // Handle to database.
  'SYSTEM', // Name of the new collation.
  SQLITE_UTF16LE, // String encoding for function callback.
  nil, // User data.
  SQLite3_Compare_User_UTF16LE); // Function callback.

sqlite3_create_collation() requires DISQLite3 Pro, it is not available with DISQLite3 Personal.

There are some new Delphi design-time icons available here which are based on the official “feather” logo of SQLite. To use the new icon replace the file “DISQLite3Reg.dcr” in the source folder of the DISQLite3 distribution with the new file in the archive and recompile the Delphi package.

The GIMP source file that was used to generate the icons is included in the ZIP archive.

• Great and handy product with excellent support.
• Without exaggeration I can say that DISQLite3 is one of the most useful Delphi DB component and useful introduction to SQLite DB.
• Considering that there is DISQLite3 I seriously contemplate to use SQLite (with DISQLite3 of course) as embedded DB because of amazing performance. Branko Burdian
• We are using DISQLite3 in several projects, and are very happy with it's performance and stability. Thanks! Chris Kuske, Solid Oak Software, Inc.
• Software works like a dream! I have run your database under a service so that multiple applications can access the data though sockets, lightning fast. Glen