A simple Delphi wrapper for Sqlite 3

Most applications use a database, and there are many excellent database engines to choose from, both free and commercial. SQLite is a small C library that has several advantages. It is open source, free, cross-platform, fast, reliable, and well supported. I had a Delphi 7 application using Sqlite 2.0. There are various wrappers available for Delphi, and around 18 months ago I tried all the ones I could get my hands on. Although several of them were of good quality, I found myself running into bugs caused by the complexity of implementing Borland’s TDataset and related database components. Since I didn’t require databinding, I chose a wrapper that implemented very simple access to Sqlite – it was written by Ben Hochstrasser and amended by Pablo Pissanetzky. An advantage for me was that I could easily see what the wrapper did and make my own amendments. I actually made rather a lot of changes, adding basic transaction support and implementing a crude dataset based on a TList. Despite its simplicity, I found it effective and reliable.

The main author of Sqlite, Dr D Richard Hipp, has since released Sqlite 3.0. This adds some useful features, including BLOB support and the ability to create tables that support case-insensitive comparisons. I decided to update my wrapper for Sqlite 3.0. This meant changing the code from using SQLite’s callback interface to use Sqlite3_Prepare and Sqlite3_Step instead (see the description of the Sqlite C interface). Most of the code written by Ben and Pablo has now gone, which I say not to demean their efforts, but to emphasise their innocence. I’ve also had a go at adding BLOB support. The new wrapper is not yet extensively tested, but so far it is working well.

I’m now offering the wrapper for download. You’re welcome to use it, although naturally it comes with no warranty. I’d be grateful for any comments, bug reports or improvements, though I’d like to keep the wrapper simple. Note this is for Delphi 7, not Delphi .NET (though I’ve also used Sqlite with .NET – see here).

More about the wrapper

This wrapper has two units and three main classes. Sqlite3.pas has the external declarations for sqlite3.dll. I’ve included a binary build of sqlite3.dll, made with Visual C++ 2003. It should work with other builds, so you can upgrade to later versions of the DLL without making changes to the wrapper (unless the Sqlite API itself changes).

Sqlitetable3.pas implements three classes, ESqliteException, TSqliteDatabase and TSqliteTable. Currently TSqliteDatabase has the following methods:

GetTable: execute an SQL query and return a resultset as a TSqliteTable.

ExecSQL: execute an SQL query that does not return data.

UpdateBlob: Update a blob field with data from a TStream object.

BeginTransaction, Commit, Rollback: sends SQL statements for transaction support.

TableExists: Returns true if the specified table exists in the database.

There is also an IsTransactionOpen property.

TSqliteTable represents a resultset. It maintains no link to the source database, so it is disconnected: you can keep a TSqliteTable in memory after freeing the source TSqliteDatabase. When created it is set to the first row. Navigate the resultset using Next and Previous, until EOF is True. At BOF the resultset is on the first row, but at EOF there is no valid row. RowCount retrieves the number of rows, which may be zero. To retrieve data, first use FieldIndex to get the index number of a particular field. Then use the appropriate Field… method to get the value: FieldAsString, FieldAsInteger, FieldAsDouble, FieldAsBlob or FieldAsBlobText. For other datatypes such as Currency or TDateTime, you currently need to convert to String or one of the other types – I’m planning to add some more types soon. I’ve not tested the Blob functionality extensively. Since the entire resultset must fit in memory, be cautious about retrieving large resultsets or resultsets with large amounts of Blob data.

Currently the only way to determine if a field contains a null value is with the FieldIsNull method. The other methods return zero, false or empty strings for null values.

I will be publising a basic tutorial on using the wrapper in the UK magazine PC Plus. I will also keep this page up-to-date with the latest version.

Update 19 February 2005: I’ve updated the wrapper for Sqlite 3.1.2. This changes the way column names are returned, so I’ve added a call to set the Pragma full_column_names on. I’ve also amended the field type detection to use the actual type when the declared type is not available, and added the utility function TableExists. The test application now shows a possible way to load, save and display images in a Sqlite database.

Update 15 August 2005: Thanks to Lukas Gebauer who has made the wrapper compatible with Delphi 4+ and added some new methods. See the readme for details. I’ve also followed Lukas’s suggestion in removing FieldAsBool – he points out that it is not a natural sqlite3 type. If this causes problems for anyone, let me know. I’ve left the previous version available for download just in case. Finally, I’ve included a Visual C++ 2003 release build of Sqlite3 version 3.2.2.

Update 27 August 2007: Thanks to Marek Janáč who emailed me to say that the wrapper did not work with the latest Sqlite3 dll (3.4.2). The problem was that Sqlite now requires pathnames to be in UTF8 format when the path contains accented characters. I’ve made a small change to fix this. I’ve also included a new MSVC 6.0 build of the DLL. Finally, I’ve created a repository for the wrapper here:

https://www.itwriting.com/repos/sqlitewrapper/trunk

Update 16 October 2008: Quick update to get Delphi 2009 compatibility – not properly Unicode-enabled though, yet.

Update 4 February 2011: Added support for SQLite backup API. Updated DLL to Sqlite 3.7.5. Compiled with VC++ 10 but with static linking to avoid runtime dependencies.

Update 10 February 2011: Created new Unicode version. This has not been extensively tested, and requires Delphi 2009 or higher on Windows. Need to make this a single code base across all versions. Removed BindData method pending review for Unicode. Modified demo project to add simple navigation. You can download the Unicode version here.

image

Helpful project? Sponsor ITWriting.com for ad-free access to the site

Links

Download the Simple Delphi Wrapper

Download the Unicode version

Sqlite home page

Other Sqlite wrappers including some for Delphi

My notes on using Sqlite 2 with Delphi, .NET and Java

My interview with the main author of Sqlite, Dr D Richard Hipp

172 thoughts on “A simple Delphi wrapper for Sqlite 3”

  1. Hi Tim,
    Many thanks for this excellent tool. I’m currently working on a SQLite 3 database and your wrapper has undoubtedly saved me a lot of work.
    However, I’m having a problem reading Integer fields in another SQLite3 database and I can’t figure out what’s going on. I’ve checked both databases in SQLiteStudio and all seems fine.
    I’m using the test application (uTestSQLite.pas) provided with the download. When I click Test SQLite 3, the application created and read the Integer (and other) fields in Testtable.db without a problem. I then added a button and borrowed (and amended!) the first few lines of code from your Test SQLite 3 button to the OnClick event of the new button – changing only the Database and Table names.
    When I run the amended SQL on the other database, the Integer fields are causing the program to crash with the following error: ‘Not an integer or numeric field’. I’m probably doing something stupid!
    I had thought it may have been caused by the fact that the ‘ID’ field (in the new database) was defined as Integer (Autoincrement). However, there’s another Integer field in the table and I’m having similar problems with that field. Could it have something to do with Integer ‘bitness’ (32 v 64)?
    Can you please give me any suggestions as to how I might move past this problem?
    Thanks again for this great resource.
    Best regards,
    Eamon

    1. I’ve had the same problem with a sqlite database created by Xojo and then tried to extract the integer field in Delphi 10.3 with no success. However, I do know that integers on the Mac versus Windows the high and low bytes are reversed so that an integer created on the Mac gives a ridiculous result on Windows and vice versa. I am wondering if is something similar is happening here with the sqlite database format? I don’t have a solution except that maybe using the integer field is not the best idea–switch to a string field then convert to an integer in the program if necessary for any calculations.

  3. I’ve had a website that monitors the trunk for changes to the source code. Today, I got a notice that the readme.txt I was monitoring was 404. Have you removed that code permanently?

    I was linking to http://www.itwriting.com/repos/sqlitewrapper/trunk/readme.txt which is the same link you have in this doc.

    I love the code, use it every time I link up with SQLite. Even made sure it runs with FPC as well as Delphi in my own version (Also added a few tweaks for some logging of commands, errors being dumped to a text file, etc).

    1. Hi, the repository is down temporarily after an Apache upgrade, will be back soon! Glad it is useful.

  5. > should be changed in:
    > utf8FileName := UTF8Encode(FileName);

    THANK YOU !

    I have spent probably 1 week searching for reason why SQLite would always fail with hard access violation.

    This did the trick! I am using D6.

  6. Hi, I have a problem with INSERT and variables. The easy programme functions with string, but does not function with variables.

    procedure TForm9.Transponder_Neu;
    var
    Transponder : String[10];
    Bezeichnung : String;
    begin
    Transponder := Edit2.Text;
    Bezeichnung := Edit3.Text;
    FzDBPath := ExtractFilePath(application.exename) + ‘Datenbanken’ + ‘ModellDatenbanken.s3db’;
    FzDB := TSQLiteDatabase.Create(FzDBPath);
    IF FzDB.TableExists(‘Fahrzeugliste’) then
    begin
    FzDB.BeginTransaction;
    //sSQL := ‘INSERT INTO Fahrzeugliste (TransponderCode,Fahrzeug) VALUES (“034af657e12″,”Test”);’; // is OK
    sSQL := ‘INSERT INTO Fahrzeugliste (TransponderCode,Fahrzeug) VALUES (Transponder,Bezeichnung);’; // SQL Error[1] … no such column: Transponder
    FzDB.ExecSQL(sSQL);
    FzDB.Commit;
    end;
    end;

    What do I make wrong?
    Thanks Gert

  7. Hi
    You must use QuotedStr() method for char values.
    sSQL := ‘INSERT INTO Fahrzeugliste (TransponderCode,Fahrzeug) VALUES (‘+QuotedStr(Transponder)’+’,’+QuotedStr(Bezeichnung)+’)’;’;

  8. Thanks for a great wrapper for Sqlite 🙂
    one problem I noted was that trying to do:
    ‘SELECT MIN(Date) FROM Episodes’ where Date field was a DATE (format is CCYY-MM-DD)
    this causes a failure in TSQLiteTable.GetCountResult since it tries to convert to integer.
    I eventually added a (Get)StringResult function to get round this but am wondering if there is a better option?
    TIA

  9. Hi, thanks for sharing this useful information with the community!
    I have a question: did someone already manage to link a sqlite3.obj file (generated by gcc or vs) into Delphi source, to avoid using a separate DLL (this is a license requirement for the use of the encryption extension)?
    Thanks Michael

  10. Hello,
    I have ported your source code to Delphi 5(added some utf8 functions) and running into a bug in SQLiteTable3. When you compare the datatype while using FieldAsInteger you doesn’t check the uppercase datatype. This leads to an error where all fields are interpreted as text.

    //get data types
    fCols := TStringList.Create;
    fColTypes := TList.Create;
    fColCount := SQLite3_ColumnCount(stmt);
    for i := 0 to Pred(fColCount) do
    fCols.Add(AnsiUpperCase(Sqlite3_ColumnName(stmt, i)));
    for i := 0 to Pred(fColCount) do
    begin
    new(thisColType);
    DeclaredColType := AnsiStrUpper(Sqlite3_ColumnDeclType(stmt, i));

  11. I’m trying to insert data (pictures) into a Blob field and cannot figure out how to pass the Stream into the field. I can’t add it as a Parameter (or can I – not sure what AddParam function I would use). I’ve tried using the generic parameter list, Insert into table (field1, field2,…) Values (?, ?,…) and then the SQLExec, passing an array of variables ([Value1, Value2]) but that seems to skip fields in the table when I’ve added variables in the list. What is the proper approach here?

  12. Works perfect with my XE5. Haven’t tested any integer issue some other comments are mentioning. I was looking for the unicode support If you change the varchar type to text type too it works perfectly since that is a valid SQLite3 type.

Comments are closed.

Tech Writing