Archives

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 comments to A simple Delphi wrapper for Sqlite 3

  • 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

  • tim

    @Allan

    That sounds odd. Are you inadvertently rolling back the changes? Is Vista writing a second copy of the file to another location, say in your virtual store?

    Tim

  • Mike

    Hi Tim,

    I am glad to have stumbled upon your wrapper. So far I have enjoyed using it, but have encountered a minor problem. Using Delphi 2009, it seems that TSQLiteDatabase.ExecSQL() and TSQLiteDatabase.GetTable() can not use SQL queries longer than 256 characters.

    For example, if I modify line 70 of uTestSqlite.pas in the included test application to:


    sSQL := 'INSERT INTO testtable(Name,OtherID,Number,Notes) VALUES ("Some Name",4,587.6594,"Here are some notes Here are some notes Here are some notes Here are some notes Here are some notes Here are some notes Here are some notes Here are some notes Here are some notes Here are some notes Here are some notes Here are some notes Here are some notes Here are some notes Here are some notes Here are some notes");';

    (i.e. adding an arbitrary amount of extra data to so that the statement surpasses 256 characters in length)

    I get this Debugger Exception Notification:

    Project TestSqlite.exe raised exception class ESQLiteException with message ‘Could not prepare SQL statement’.

    Any ideas on what may be causing this? I’ll definitely need to be using INSERT statements longer than 256 characters in my application.

    Thanks!

  • tim

    @Mike sounds like a bug, though I’m surprised nobody else has shouted about this. I’ll check when I get a moment.

    Tim

  • Mike

    Hi Tim,

    After further investigation it looks more to be an IDE bug/configuration issue/oddity. If I break the string into two statements using concatenation, it works! Where as trying to assign a string more than 256 bytes at once results in the string being empty.

    For example, this would fix the code in my previous post:

    sSQL := 'INSERT INTO testtable(Name,OtherID,Number,Notes) VALUES ("Some Name",4,587.6594,"Here are some notes Here are some notes Here are some notes Here are some notes Here are some notes Here are some notes Here are some notes';
    sSQL := sSQL + ' Here are some notes Here are some notes Here are some notes Here are some notes Here are some notes Here are some notes Here are some notes Here are some notes Here are some notes");';

    Do you have this issue in your IDE? Any ideas on how to get around it in D2009?

    Thanks,

    Mike

  • Jan Derk

    Hi Tim,

    I am happily using your wrapper. There is a bug in TSQLiteDatabase.Create() causing it to fail if there are unicode characters in the filename on D2007 and earlier.

    This line:
    utf8FileName := UTF8String(FileName);

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

    That is because in D2007 and earlier UTF8String is just a typecast to a string. It does not convert anything.

  • Hi, an update on the vista issue I experienced. My problem was my database file was located in the c:\\program files\MYAPP\ directory beside the exe, and dll. To resolve, I had to change my files directory to the c:\users\USER\AppData\Local\MYAPP directory. The problem seemed to be restricted permissions on the program files folders and files.

    My application also involved two executables working on the same sqlite database. I found that this caused lots of ‘Could not prepare SQL statement’ errors. to resolve this, I created a copy of the dll with a different name; so one dll for each exe. This resolved the issues and allowed me to have multiple connections to the same database.

    Hope this helps someone out there who may have the same problems.

    Regards,
    Allan.

  • Murilo

    Thankkkkkkkkkkkkkkk 4 wapper, good work.

  • Sergey

    Hi!
    Please, show me example code, howto add UTF8 to base?
    For example, this string:
    ვეპხის ტყაოსანი შოთა რუსთაველი
    Insert into… does not work 🙁

  • xZise

    Hello, why you aren’t using the UTF8 features from the SQLite Database? Then you could use both Delphi “systems” and you can support the UTF8 Features with special characters.

    Like this:
    UTF8String in Database -> UTF8String in Wrapper -> The app now can convert it to Unicode/Wide/AnsiString. Or you support two functions to get the UTF8String “source” OR the converted “string”.

    Sincerely
    xZise

  • tim

    @xZise @sergey there’s no good reason, I did the minimum to get it to compile in D2009 but haven’t added Unicode support. If anyone’s done this, let me have the code and I’ll post it. Otherwise it will have to wait until I have a spare moment.

    Tim

  • xZise

    I made some changes. Now I added a “Decode”-Option. You don’t create a table with “GetTable” (because then it is unsure who is the “owner” of the object -> better: GetTable gets a Tableobject and only “loads” the data). I also added the possibility to reuse a table (execute “QuerySQL”). And there is no “need” for next. You can get a value by Table.FieldAsString[Row, Column]. It is easy to implement the old “way”: You can execute the “FAS(ActualRow, ColAsParam)”.

    Next week I want to make it full/better Delphi 2009 compatible. By the way I now parse the Text from UTF8 to Ansi/Unicode (UTF-16) (depends on the used compiler). This is much easier to implement 😀
    So it looks like:
    App (Ansi/Unicode) -> Wrapper (Ansi/Unicode) -> Database (UTF-8)

    Sincerely
    xZise

  • @xZise, can you publish you changes? how to get it work with delphi 2010? thanks!

  • tim

    @John it does work in Delphi 2010, just not with Unicode…

    Tim

  • thefearlessdog

    Hi Tim,
    I am having issues with updating a Blob as the updateBlob does not allow extra bindings to the sql query.

    I want to run the following query
    Update Sensors set last_pkt = ? where SerialNo = ?

    and have the bindings [stream,serialNo]

    Any suggestions on how I go about doing this?

    ps. I have looked at all the other wrappers and have found your wrapper the easiest one to implement so kudos to ya. 🙂

  • thefearlessdog

    Extending previous comment
    I have tried using the following
    UpdateBlob(format(‘Update Sensors set colour = “test”, last_pkt = ? where SerialNo = “%s”‘, [s.serial]),Stream);

    the where clause will work with ‘like’ but not with = so not sure what I am doing wrong.

  • thefearlessdog

    I have resolved my issue by using ExeSql with bindings passing a ? in the where clause

    I am kicking myself for such a stupid error.

    I do have another issue I am trying to store a Single value into the database but seem to be getting a invalid floating point error.

    would you know how to fix this issue?

  • [clearing bindings feature]
    Hi Tim,
    I propose you add the [function SQLite3_ClearBindings(hStmt: TSqliteStmt): integer; cdecl; external SQLiteDLL name ‘sqlite3_clear_bindings’;] to SQLite3.pas
    What do you think?
    Best regards

  • [BindSQL procedure]
    Hi Tim,
    [procedure TSQLiteDatabase.BindSQL(Query: TSQLiteQuery; const Index: Integer; const Value: String);] does not work properly when using TStringLists string pointing to ‘Value’.
    Eg:
    ..
    aSQLiteDatabase.BindSQL(aSQLiteQuery, 1, aStringList.ValueFromIndex[i]);
    ..
    aSQLiteDatabase.ExecSQL(SQLiteQuery);
    .. does not produce the expected result.

    To fix this, I’ve modified the BindSQL procedure: ptrDestructor -> Pointer(SQLITE_TRANSIENT) instead of ptrDestructor -> Pointer(SQLITE_STATIC)

    What do you think?

  • tim

    @Didier

    I’ve tried to email you though I’m not 100% sure of the address – can you email me tim(at)itwriting.com if it does not arrive?

    Thanks for the suggestions.

    Tim

  • Nicolaj Andersen

    Exactly what I was looking for… Great work… Very appreciated

    Thanks 😀

  • meehi

    Hi!
    Great wrapper!

    I’ve replaced my database from Firebird to SQLite and with this wrapper my code looks like much simpler now and it’s quarter of size then the original.
    Thanks a lot!

  • xZise

    Hi all,
    here are my changes, but there are many changes so most of the old code isn’t directly usable for this version. But it support “better” UTF Managment:
    http://xzise.xz.ohost.de/notvisible/sqlite.7z

    Added Features:
    – No Creation of tables as result
    – Decoded option
    – Working with the result as UTF8 string (So fully compatibility between SQLite and Ansi OR Unicode Versions of Delphi)
    – Value of a position (Col and Row) with no need of Next/First/Last or sth. else…
    – More features could be there but I forget them 😉

    ToDo:
    – Not urgent, but nice would be to read UTF16 strings instead of UTF8 string
    – Some function with unknown content aren’t translated to UTF8 😛

    Sincerely
    xZise

  • Domoffoy

    Very good wrapper, thank you!
    I’m working with Delphi 2009.
    My program makes ExecSQL from thread, only one thread. But sometimes program are crashing with error:
    “Error executing SQL statement.
    Error [1]: SQL error or missing database.
    “COMMIT”: cannot commit – no transaction is active”
    But my code is:
    if sldb.IsTransactionOpen = true then sldb.Commit;

    I think, that var in module SQLiteTable3.pas – self.fInTrans is true but it must to be false.

    I am using the last wrapper from this site with no changes.
    Maybe, I should not do BeginTransaction?
    Thank you.

  • Domoffoy

    Sorry for my bad english 🙂

  • tim

    @Domoffoy

    It should definitely be false by default. Can you work out how it is getting set to true?

    Tim

  • Domoffoy

    tim, that was my error, sorry.
    I was using Access (mdb) base, and there was something like this:

    ADOQuery1:=’Select * from Table1′;
    Adoquery.Recno:=492;
    // —

    Is there something like RecNo? Maybe, i must always to use?:
    key:=492;
    Select key From Table1

  • Helmut

    This is just stupid-easy to use. It’s a complete implementation, it works flawlessly, and it doesn’t bloat out your app with a ton of TDataSet-related cruft… you just saved me a lot of time. 🙂

    Thank you!

  • Alan Riaso

    I’ve been using this simple wrapper for years in my own personal projects and just wanted to say thanks. I also agree with the above commenter, the TDataSet stuff has no place in the Sqlite way of doing things so thanks again for leaving it out!

  • My company has just released a new SQLite3 wrapper and framework. It’s all Open Source, released under the “SQLite3 blessing” licence.

    http://blog.synopse.info/category/Open-Source-Projects/SQLite3-Framework

    It is not based on your work, but I have to confess that it was a starting point to find how to access the SQLite3 from Delphi.

    Our own wrapper in embedded into Pascal unit file (no dll is needed), and is does much more than just wrap it.

    The Synopse SQLite3 database Framework interfaces the SQlite3 database engine into pure Delphi code: database access, User Interface generation, security, i18n are handled in a safe and fast Client/Server AJAX/RESTful model.

  • I am about to try it with Delphi 2010 E.

    I saw the notes about Unicode, thanks for the warning.

    Regarding Blobs: According to SqLite, any sqlite(3) field can hold a blob. The size paramter will be ignored if you put more data into a field than it was set for with i.e. varchar(xx)

    I’ll let you know about my 2010 success
    (or failure)

  • I like the ease of use of the wrapper. But I encountered a performance problem.

    I tried to insert one million records by using the sample project. It took about 2 minutes! Is there any method to speed it up? Thanks!

  • tim

    @Lewis

    Yes – wrap the inserts in a transaction.

    Tim

  • Tim, I already wrap it in a transaction. Here is what I did:
    ———————–
    sldb.BeginTransaction;
    for index := 0 to 1000000 do
    begin
    sSQL := ‘INSERT INTO testtable(Name,OtherID,Number,Notes) VALUES (“‘+IntToStr(index)+'”,12,4758.3265,”More notes”);’;
    sldb.ExecSQL(sSQL);
    end;
    sldb.Commit;
    ———————–

    I also tried to change the “cache_size”, “synchronous” options but they didn’t help.

  • Geert

    Hi Tim,

    First of all, fantastic work !

    Secondly, I was trying to vacuum the sqlite db via the “framework”. Very few can be found on this (in general) and added a procedure :
    ___________

    procedure TSQLiteDatabase.Vacuum;
    var
    Stmt: TSQLiteStmt;
    Msg: PAnsiChar;
    NextSQLStatement: PAnsiChar;
    SQL : string;
    begin
    SQL := ‘VACUUM;’;
    Msg := nil;
    try

    if Sqlite3_Prepare_v2(self.fDB, PAnsiChar(SQL), -1, Stmt, NextSQLStatement)
    SQLITE_OK then
    RaiseError(‘Could not prepare SQL statement’, SQL);

    if (Stmt = nil) then
    RaiseError(‘Could not prepare SQL statement’, SQL);
    DoQuery(SQL);

    finally
    if Assigned(Stmt) then
    Sqlite3_Finalize(stmt);
    if Assigned(Msg) then
    SQLite3_Free(Msg);
    end;

    end;
    ____

    The good news is that I get no errors from the prepare and in general. The bad news is that the db is not vacuumed !

    Any ideas ? (non-sql statement via sql statement ?)

    Thanks !

    Cheers,

    Geert

  • tim

    Geert

    As far as I can tell it should work. Any clues here?

    VACUUM only works on the main database. It is not possible to VACUUM an attached database file.

    The VACUUM command will fail if there is an active transaction. The VACUUM command is a no-op for in-memory databases.

    Tim

  • Geert

    Hi Tim,

    Both conditions are OK and still no compression.

    I will have a closer look at this after the holidays.

    BTW best wishes for 2010 for all !

    Thanks !

    Cheers,

    Geert

  • Geert, about the speed of insertion, try preparing the SQL statement, and bind the field values as parameters. It should be faster, since the SQL statement will be compiled only once by the SQLite3 database engine.
    According to most benchmark and to the source code, insertion was never meant to be fast with SQLite3, even with transactions: ACID and journalized write are always expensive tasks. Dedicated code should be most valuable for such tasks (did you try memory mapped files, they are great for fast access to great amount of data from Delphi code).

  • Michael Wollert

    [Delphi 2010]
    Because SQLiteTable3.pas row 854 ->sqlite3_bind_parameter_index didn´t find parameter from list
    I changed string to ansistring of name.

    TSQliteParam = class
    public
    name: Ansistring; // changed from string to AnsiString
    valuetype: integer;
    valueinteger: int64;
    valuefloat: double;
    valuedata: string;
    end;

    Main problem seems to be in sqlite3.dll for data type REAL. After insert statement with Value = “1,25” a problem occurs. Germans write “1,25”, english is it written “1.25”. If REAL value is read, it is cut at the coma, so the returned value is 1 instead of (german) 1,25. Is there a possibility to change REAL value handling in sqlite3.dll?

    Greetings
    Michael Wollert

  • ptonev

    Hello,

    is a possible to use .import with/in execsql().

    I received error. Now I can import file into table.

    regards.

  • Lew

    I am using the Simple Delphi Wrapper for SQLite3 with Delphi 2010. I am using it for a database that is created and maintained by others and I have no ability to redefine its individual fields. I have run into a problem with trying to read a field that has been defined as SMALLINT. Is there any way to read this field with the wrapper?

  • brian

    Lew, there is a Embarcadero supported download for registered users that supports Sqlite (and a few other) databases, natively in Delphi 2010. Maybe that would solve your issue.

  • Lew

    Thanks. I’ll look into it.

  • Micha

    Hi Tim

    I try to use your wrapper with Delphi 2010.
    I modified the example to use my SQLite database. Using the SQL statement ‘Select * From Mp3Record’ works fine, but when I try to select specific records like
    ‘Select * From Mp3Record Where Genre=’ + chr(39) + ‘Disco’ + chr(39
    I get the following error:
    ‘Error executing SQL. Error[1]: SQL error or missing database
    “Select * From Mp3Record Where Genre=’Disco'”: no such collation sequence: SYSTEMNOCASE’ occured.
    But that’s a correct SQL statement.
    How do I ‘filter’ a query?

    Thanks in advance!
    Micha

  • Hey guys,

    I made few changes on the wrapper to make it compatible with Delphi 2010

    You may download the modified version from:

    http://www.wishapps.com/sqlitewrapper/sqlite-delphi-2010.rar

  • PopEye

    brian, I have looked for the download you mentioned, but I can’t find it. Have you got a link or a name for the download?

  • Andreas

    This is a very good tool.
    I have to access to a special sqlite-db with many TEXT colums, and I tryed many delphi components.
    This was the only tool that give me access to this db.
    Very fast and very simple
    Thanks

  • Micha

    Hello Tim, hello guys

    I want to come back to my question above from April 9th, because I still didn’t find out how to filter or to retrieve certain records.
    Can someone post a short sample for this? Many thanks in advance.
    At the moment I always have to retrieve all records and then filter them according their fields. That’s not the best way using large amount of records.

    Micha

  • tim

    @Micha

    What version of the Sqlite DLL are you using? I need to test it with the latest version – it is waiting for a spare moment!

    Tim

  • Micha

    Hello Tim
    Sorry for my delayed answer.
    I used the file from 2008 August 05. Now I use the latest version 3.6.23.1 from 2010 March 29.
    It would be VERY great if you could help me with the issue.

    Thanks in advance!

    Micha

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>