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:

http://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

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

  1. @Didier

    If you want TClientDataset, I’d suggest looking for another wrapper. That said, there is no problem doing something like a master-detail relationship with this wrapper. Let’s say you have master records in a grid. When the user clicks a row, just generate SQL to retrieve the detail rows. Alternatively, you can make your own class wrapper and populate collections of custom objects.

    Note that this is really for local databases. If you are going over the network, you probably want a database other than sqlite.

    Tim

  2. Tim,

    >>That said, there is no problem doing something like a master-detail relationship with this wrapper. Let’s say you have master records in a grid. When the user clicks a row, just generate SQL to retrieve the detail rows.<>Alternatively, you can make your own class wrapper and populate collections of custom objects.<>Note that this is really for local databases. If you are going over the network, you probably want a database other than sqlite<<

    For you, what prevents SQLite from going over the network like we can do with Firebird or other RDB.
    Does SQLite not support TCP/IP?

    Didier

  3. Tim,

    you said: That said, there is no problem doing something like a master-detail relationship with this wrapper. Let’s say you have master records in a grid. When the user clicks a row, just generate SQL to retrieve the detail rows.

    You are perfectly right

    you said: Alternatively, you can make your own class wrapper and populate collections of custom objects

    Do you mean create a detail-table wrapper?

    you said: Note that this is really for local databases. If you are going over the network, you probably want a database other than sqlite

    For you, what prevents SQLite from going over the network like we can do with Firebird.or other RDB
    Does SQLite not support TCP/IP?

    Didier

  4. And… How can I easily get column name? I haven’t found any way.

    table := db.GetUniTable(‘select * from test;’);

    I can’t use standart Utf8ToAnsi(sqlite3_column_name(stmt, col)); after it, because I have not a statement.

    I have to get table via sqlite3_prepare()?

  5. @tim

    Where can I get a sqlite statement then?!
    You use statements in your methods only. And I use methods. That is the reason why there is no statement in my code.

    Help, if you can.

  6. How can I easily get column name?

    I’ve solve that. It is table.Columns[col] property.

    But GetColumns() returns wrong Name value. I get it with some letters corrupted when column named in Russian (I select from “views” in my project). How to fix? Or it is “not properly unicode-enabled though” issue?

  7. I changed column creation in .Create() method

    //fCols.Add(AnsiUpperCase(Sqlite3_ColumnName(stmt, i)));
    fCols.Add(Sqlite3_ColumnName(stmt, i));

    and output result of .GetFieldIndex() function

    //Result := fCols.IndexOf(AnsiUpperCase(FieldName));
    Result := fCols.IndexOf(FieldName);

    Now I can view my russian column names. I have to use most of SQL in project to get it extensible with no or little source code modification.

    Though thank you. Very useful wrapper!

  8. Hi,

    I really appreciate the work you did with your little library!

    So how “unicode-enabled” is your code yet? It says in the description that it’s not yet properly unicode-enabled.

    What does that mean? I encounter a lot of string-conversion warnings when I compile your Unit and there are several pieces where I’m not quite sure if there’s Unicode-Support yet, f.e.:
    FieldAsString returns a string but after a little research I learned that it actually returns an AnsiString (which leads to string-conversion warnings in my code). I guess it does return an AnsiString and not a string (which is an UnicodeString in Delphi 2009).

    So is there any chance to get a fully Unicode-Enabled library (either every function works just with AnsiStrings to avoid the conversion-warnings or it really supports Unicode and works with UnicodeStrings (and does the UTF8 conversion on its own), i’d prefer the latter of course 😉 ) anytime soon?

    Thanks,
    Max

    (Excuse my English, I hope I made my point clear)

  9. Many thanks for your wrapper, I develop a freeware cookie manager and since in Firefox 3 cookies and places are stored in SQLite databases, I needed a quick way to manage that databases (I develop my apps in my free time, which is scarce nowadays).

    Your wrapper is small and with a good structure of the classes in my opinion. I only have to add the BindSQL() method for Int64 (modifiying the method you already have for Integer), since it was the type used in the tables for row ID:


    procedure TSQLiteDatabase.BindSQL(Query: TSQLiteQuery; const Index: Integer; const Value: Int64);
    begin
    if Assigned(Query.Statement) then
    sqlite3_Bind_Int64(Query.Statement, Index, Value)
    else
    RaiseError('Could not bind integer to prepared SQL statement', Query.SQL);
    end;

    Thanks again.

  10. Hello,
    lot’s of thanks for your wrapper. Exactly what I’ve looked for, since with my Delphi 6 Personal I’m not able to install the ZeosLib. 🙂
    Just one problem: When I’m executing your examplee ‘TestSqlite.exe’ all works fine until I press the “Load Image” button – Access Violation (“Write of Adress xxxxx”). In the Debugger it highlights the line “sldb := TSQLiteDatabase.Create(slDBPath);” in the btnLoadImageClick procedure.

    Just wanted to tell you, keep on going! 🙂

  11. @Mao

    Works OK on D2006 and D2009 – I don’t have D6 installed unfortunately. If you can identify the problem I’ll amend the code.

    Tim

  12. I don’t have problem with it because I don’t understand like I can use your component+SQLite+encryption(with password).

    Is there a demo example?

  13. First of all, thanks a lot for the great wrapper.

    I have a question about thread safety:
    Thread safety is implemented in SQLLite3, it is implemented in your wrapper or programmer should take care of it?

    Thanks!

  14. Hi,

    There was an error binding unicode utf-8 strings, I’ve fixed the piece of code and I’ve pasted it below.

    procedure TSQLiteDatabase.BindData(Stmt: TSQLiteStmt; const Bindings: array of const);
    var
    BlobMemStream: TCustomMemoryStream;
    BlobStdStream: TStream;
    DataPtr: Pointer;
    DataSize: integer;
    AnsiStr: AnsiString;
    AnsiStrPtr: PAnsiString;
    I: integer;
    begin
    for I := 0 to High(Bindings) do
    begin
    case Bindings[I].VType of
    vtString, vtUnicodeString,
    vtAnsiString, vtPChar,
    vtWideString, vtPWideChar,
    vtChar, vtWideChar:
    begin
    case Bindings[I].VType of
    vtString: begin // ShortString
    AnsiStr := Bindings[I].VString^;
    DataPtr := PAnsiChar(AnsiStr);
    DataSize := Length(AnsiStr)+1;
    end;
    vtPChar: begin
    DataPtr := Bindings[I].VPChar;
    DataSize := -1;
    end;
    vtAnsiString, vtUnicodeString: begin
    AnsiStrPtr := PAnsiString(@Bindings[I].VAnsiString);
    DataPtr := PAnsiChar(AnsiStrPtr^);
    DataSize := Length(AnsiStrPtr^)+1;
    end;
    vtPWideChar: begin
    DataPtr := PAnsiChar(UTF8Encode(WideString(Bindings[I].VPWideChar)));
    DataSize := -1;
    end;
    vtWideString: begin
    DataPtr := PAnsiChar(UTF8Encode(PWideString(@Bindings[I].VWideString)^));
    DataSize := -1;
    end;
    vtChar: begin
    DataPtr := PAnsiChar(String(Bindings[I].VChar));
    DataSize := 2;
    end;
    vtWideChar: begin
    DataPtr := PAnsiChar(UTF8Encode(WideString(Bindings[I].VWideChar)));
    DataSize := -1;
    end;
    else
    raise ESqliteException.Create(‘Unknown string-type’);
    end;
    if (sqlite3_bind_text(Stmt, I+1, DataPtr, DataSize, SQLITE_STATIC) SQLITE_OK) then
    RaiseError(‘Could not bind text’, ‘BindData’);
    end;
    vtInteger:
    if (sqlite3_bind_int(Stmt, I+1, Bindings[I].VInteger) SQLITE_OK) then
    RaiseError(‘Could not bind integer’, ‘BindData’);
    vtInt64:
    if (sqlite3_bind_int64(Stmt, I+1, Bindings[I].VInt64^) SQLITE_OK) then
    RaiseError(‘Could not bind int64’, ‘BindData’);
    vtExtended:
    if (sqlite3_bind_double(Stmt, I+1, Bindings[I].VExtended^) SQLITE_OK) then
    RaiseError(‘Could not bind extended’, ‘BindData’);
    vtBoolean:
    if (sqlite3_bind_int(Stmt, I+1, Integer(Bindings[I].VBoolean)) SQLITE_OK) then
    RaiseError(‘Could not bind boolean’, ‘BindData’);
    vtPointer:
    begin
    if (Bindings[I].VPointer = nil) then
    begin
    if (sqlite3_bind_null(Stmt, I+1) SQLITE_OK) then
    RaiseError(‘Could not bind null’, ‘BindData’);
    end
    else
    raise ESqliteException.Create(‘Unhandled pointer ( nil)’);
    end;
    vtObject:
    begin
    if (Bindings[I].VObject is TCustomMemoryStream) then
    begin
    BlobMemStream := TCustomMemoryStream(Bindings[I].VObject);
    if (sqlite3_bind_blob(Stmt, I+1, @PAnsiChar(BlobMemStream.Memory)[BlobMemStream.Position],
    BlobMemStream.Size-BlobMemStream.Position, SQLITE_STATIC) SQLITE_OK) then
    begin
    RaiseError(‘Could not bind BLOB’, ‘BindData’);
    end;
    end
    else if (Bindings[I].VObject is TStream) then
    begin
    BlobStdStream := TStream(Bindings[I].VObject);
    DataSize := BlobStdStream.Size;

    GetMem(DataPtr, DataSize);
    if (DataPtr = nil) then
    raise ESqliteException.Create(‘Error getting memory to save blob’);

    BlobStdStream.Position := 0;
    BlobStdStream.Read(DataPtr^, DataSize);

    if (sqlite3_bind_blob(stmt, I+1, DataPtr, DataSize, @DisposePointer) SQLITE_OK) then
    RaiseError(‘Could not bind BLOB’, ‘BindData’);
    end
    else
    raise ESqliteException.Create(‘Unhandled object-type in binding’);
    end
    else
    begin
    raise ESqliteException.Create(‘Unhandled binding’);
    end;
    end;
    end;
    end;

  15. Will you be porting this wrapper to support further versions of SQLite? (like v3.6.9, which is out :))

    Anyway, thanks for the great wrapper, it really came in handy 🙂

  16. Hmm… sorry for the comment above 🙂
    I tested the wrapper with the latest sqlite.dll and it worked
    I didn’t test that much (just creating db, loading stuff), but it seemed ok

    You can delete the comment if you want :] (since it is still awaiting moderation).. that way you won’t have a comment with a question whose answer is obvious (and answered on the post itself lol)

    many thanks, again! 🙂

  17. I heared good things about SQLite, so I tried the demo program, which worked, but now I would like to see some data in a grid…
    I tried this:

    sldb:=TSQLiteDatabase.Create(ExtractFilepath(application.exename) + 'test.db');
    sltb:=TSQLIteTable.Create(sldb, 'select * from testtable');
    DataSource1.DataSet:=sltb;

    … but apparently a TDataSource doesn’t like a TSQLIteTable!
    What should I do???

  18. Hi, fantastic bit of work here. I’m having a small issue and was wondering if you have any pointers. I’ve written a small app that uses this to update and insert rows in an sqlite database. but when run on vista, none of the queries actually update or insert anything, and no errors are being thrown.

    Im totally new to vista, and think its a permissions thing, but i just cant seem to get it to make the changes. the database files modified date is updating, but its as if it cant commit the statements. also tried doing qry=’begin transaction;’+qry+’;commit;’
    no difference.

    Using sqliteman I can use the db as normal and see that the updates and inserts are not being applied.

    any ideas anyone?

    Regards,
    Allan

Leave a Reply

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

Tech Writing