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

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

  1. It doesn’t have a “Move To” position in the opened table, so here’s the function you may add to SQLiteTable3.pas:

    function TSQLiteTable.MoveTo(position: Integer): boolean;
    begin
    Result := False;
    if (self.fRowCount > 0) and (self.fRowCount > position) then
    begin
    fRow := position;
    Result := True;
    end;
    end;

    And in the TSQLiteTable class you may add the following public function:

    function MoveTo(position:Integer): boolean;

  2. Could you possible add SQLite3_Bind… for all types, not just for BLOBs (SQLite3_BindBlob)? I’d appreciate this very much…

  3. If create a table by sql:
    “Create table testtable(id integer primary key, name string, pic blob”
    (the fields name is lowercase), then the code:
    tbl = sldb.GetTable(‘SELECT pic FROM testtable where ID = ‘ inttostr(iID));
    can’t handle correctly, it would raise ESQLiteException.

    In the SQLiteTable3.pas:
    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 := Sqlite3_ColumnDeclType(stmt, i);
    if DeclaredColType = nil then
    thisColType^ := Sqlite3_ColumnType(stmt, i) //use the actual column type instead
    //seems to be needed for last_insert_rowid
    else
    if (DeclaredColType = ‘INTEGER’) or (DeclaredColType = ‘BOOLEAN’) then
    thisColType^ := dtInt
    else
    if (DeclaredColType = ‘NUMERIC’) or
    (DeclaredColType = ‘FLOAT’) or
    (DeclaredColType = ‘DOUBLE’) or
    (DeclaredColType = ‘REAL’) then
    thisColType^ := dtNumeric
    else
    if DeclaredColType = ‘BLOB’ then
    thisColType^ := dtBlob
    else
    thisColType^ := dtStr;

    The DeclaredColType is lowercase string, but in follow codes, it be compared to uppercase string, so all fields that use lowercase name would be dtStr

  4. Hello, great job. Unfortunally i don’t found pas unit for PostgreSql and an internet search lead me to this page.

    SqlLite is a candidate to replace MySql on a future commercial project.

    What is the licence for your unit? In particular on commercial use.

    Thanks for your response.

    Best regards.

  5. Great wrapper – used in Delphi6 to generate a UTF-16 compliant SQLite file, asynch to WinCE4.2 device which uses a native program to alter the database.

    Microshaft’s attempt at a suitable database (SqlCE) had created a non-UTF-16 compliant abortion only suitable for PIM’s and not for data gathering in an industrial environment (Memo’s, Blobs, Oracle, iSeries, SAP R/3 etc…). Synchronizing with Access/SQLServer is a no-no as well – the data has to be validated and cleaned before it goes there.

  6. hello, thanks for this great wrapper…. i have piece of code give me error message

    Try
    tblmuhet := dbmuhet.GetTable(‘SELECT * FROM Luget where Word = ”’ myword ””);
    Try
    If Not tblmuhet.Count = 0 Then
    mmmuhet.Text := ‘No rows in the muhet database. ‘
    Else
    Notes := tblmuhet.FieldAsBlobText(tblmuhet.FieldIndex[‘Name’]);
    mmmuhet.Text := notes;
    except
    mmmuhet.Text := ‘No rows in the muhet database. ‘;
    End;
    except
    End;

    the reason i put secobd “mmmuhet.Text := ‘No rows in the muhet database. ‘;” inside exception section that it sometimes give me error saying there is no field, why it gives me this error if it already passed “If Not tblmuhet.Count = 0 Then” being true??

    thanks

  7. sorry, i forgot begin end ,here is correct code
    Try
    tblmuhet := dbmuhet.GetTable(‘SELECT * FROM Luget where Word = ”’ myword ””);
    Try
    If Not tblmuhet.Count = 0 Then
    mmmuhet.Text := ‘No rows in the muhet database. ‘
    Else
    begin
    Notes := tblmuhet.FieldAsBlobText(tblmuhet.FieldIndex[‘Name’]);
    mmmuhet.Text := notes;
    end;
    except
    mmmuhet.Text := ‘No rows in the muhet database. ‘;
    End;
    except
    End;

  8. Just thought I’d drop you a quick note to say thanks for the great SQLite component Tim! Have found it a pleasure and a joy to use, keep up the good work!

    Thomas

  9. Thanks for the very useful library!

    I report a bug in TSqliteTable.FieldAsInteger:
    Result := trunc(strtofloat(pString(self.fResults[(self.frow * self.fColCount) + I])^))
    should be
    Result := trunc(pDouble(self.fResults[(self.frow * self.fColCount) + I])^)

    Hope I’m making somebody happy.

    Osamu

  10. Hi,

    I found somekind of memory leak when I use this wrapper.

    I have 2 scenarios to describe the memory leak.

    SCENARIO_A:

    1. I declare the TSQLiteDatabase object as a GLOBAL variable.
    2. During FormCreate event, I opened the database:

    SQLdb := TSQLiteDatabase.Create(DatabaseFilePath);

    3. I have a function to find a data like this:

    function FindCustomer(ID: string): boolean;
    var
    SQLtb: TSQLiteTable;
    begin
    Result := FALSE;
    try
    SQLtb := SQLdb.GetTable('SELECT CustomerID FROM TableCustomer WHERE CustomerID="'+Trim(ID)+'"');
    Result := SQLtb.Count > 0;
    FreeAndNil(SQLtb);
    except
    Error('Error reading database');
    end;
    end;

    4. This way, I got memory leak ! I tested like this:

    For i := 0 to 10000 do
    if FindCustomer(RandomString(10)) then;

    If I changed to SCENARIO_B (see below), the memory leak is gone.

    SCENARIO_B:

    1. I declare the TSQLiteDatabase as a LOCAL variable inside the function.
    2. The FindCustomer function is defined like this:

    function FindCustomer(ID: string): boolean;
    var
    SQLtb: TSQLiteTable;
    SQLdb: TSQLiteDatabase;
    begin
    SQLdb := TSQLiteDatabase.Create(DatabaseFilePath);
    Result := FALSE;
    try
    SQLtb := SQLdb.GetTable('SELECT CustomerID FROM TableCustomer WHERE CustomerID="'+Trim(ID)+'"');
    Result := SQLtb.Count > 0;
    FreeAndNil(SQLtb);
    FreeAndNil(SQLdb);
    except
    Error('Error reading database');
    end;
    end;

    Now, the memory leak is gone.

    Is this a known issue? I meant, that I should define the TSQLiteDatabase as LOCAL.

    In that way, it will be very slow because it will open-and-close the database everytime.

    Thanks.

  11. Thanks for this useful wraprer
    I have added for me a public function, GetColType, that returns the type of a column as a string.
    Useful for example to automatically format when display in a stringGrid

    function TSQLiteTable.GetColType(Col : integer): string;
    begin
    case pInteger(fColTypes[Col])^ of
    1 : result := ‘INTEGER’;
    2 : result := ‘NUMERIC’;
    3 : result := ‘STRING’;
    4 : result := ‘BLOB’;
    5 : result := ‘NUL’;
    end;
    end;

  12. I’m using your Sqlite wrapper for Delphi to convert some tables in DBIsam to Sqlite, then to be used in Objective-C on the Mac but the framework I’m using (Quicklite) is done for Sqlite 3.2.2 so I naturally get problems using the db created with your wrapper which targets 3.4.2.

    So if you could get me your wrapper’s older version wich uses Sqlite 3.2.2 that would be great! Or at least the sqlite3.dll version 3.2.2 which I couldn’t find on the web…

  13. Hello as SQLite lets me insert a string into a database column of type integer, when I try to recover this column with the wrapper, it always returns me 0. I know this is not efficient but I have an old database with a table PERSONS with 2 fields:

    Id -> Integer Counter -> Integer

    And records are (example)

    P52-522141 12
    P52-522142 10
    P52-522143 10

    the statement -> Select Id from TEST

    Id is always 0. 🙁 And should be, 522141, etc.

    Any idea? I can’t change the type of the column because it’s not my database, so I can’t change, just read.

    Thank you

  14. Well I have found a non polite solution…

    constructor TSQLiteTable.Create(DB: TSQLiteDatabase; const SQL: string);

    I’ve added this line…

    if fCols[i] = ‘ID’ then thisColType^ := dtStr;

    so when a column field ID is found, then is declared dtStr;

  15. I’ve updated the code to get a measure of Delphi 2009 compatibility – at least, the code should work now. Needs more work to support Unicode properly though. If you don’t need Unicode, try it.

    Tim

  16. Hi Tim,
    1. congratulations for your wrapper
    2. my concern is the following: being able to use SQLite +wrapper for all of my win32 Delphi applications. Considering this, how do you handle all the capacities offered by the TClientDataset (filtering, sorting, master-detail relationship, etc ..)? Would you use your wrapper classes with it and how? Or would you use your wrapper classes directly and how?
    Thanks for your support
    Didier

Leave a Reply

Your email address will not be published. Required fields are marked *

Tech Writing