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

VN:F [1.9.18_1163]
Rate this post
Rating: 8.7/10 (29 votes cast)
A simple Delphi wrapper for Sqlite 3, 8.7 out of 10 based on 29 ratings

154 comments to A simple Delphi wrapper for Sqlite 3

  • Ken

    @Micha
    try ‘Select * From Mp3Record Where Genre=”Disco”;’

    Hi,
    I add a dynamic load library version of SQLite3d for C++ Builder,
    and made some changes of SQLiteTable3 to support Unicode.
    http://kensoft.byethost24.com/Sqlite3wrapper.7z

  • Paul

    Hi!

    I need to insert a duplicate record into a table. But how to get index of a added record?
    I add the duplicate using this code:
    ExecSQL(‘INSERT INTO Table(*) VALUES (*);’);
    Thank you!

  • Paul

    Hi!
    Please tell how to resolve this problem. I can not insert this text:
    ——————-
    Title:=’Some “Text”‘;
    Sqlite.ExecSQL(‘INSERT INTO TableName(title) VALUES (“‘+ Title +’”);’);
    ——————-
    The problem is in this symbol – “

  • Yury

    Please take a look at the complete SQLite3 API translation for Delphi/C++Builder/Lazarus. Freeware, Unicode-enabled and includes a simple object wrapper.

  • Hi Tim, my name is Pasquale and I have been using your wrapper in my software for several months. I have to say that I really love SQLITE. It’s extremely easy to use and powerful at the same time.

    Unfortunately, I have encountered a problem that is driving me crazy and I really hope you can help me.

    I have developed a piece of software to manage a warehouse. I create the SQLite database within the Form_ Show procedure using the following code:

    ======== START OF CODE ========
    procedure TfrmWarehouse.FormShow(Sender: TObject);
    var
    sSQL: String;
    begin

    DBFile := Trim(ExtractFilePath(application.exename) + ‘Warehouse.db’);
    sl3db.Free;
    sl3db := TSQLiteDatabase.Create(DBFile);

    if not sl3db.TableExists(‘warehouse’) then
    begin

    sSQL := ‘CREATE TABLE warehouse (ID INTEGER PRIMARY KEY,varCode BLOB,’;
    sSQL := sSQL + ‘varCategory BLOB,’;
    sSQL := sSQL + ‘varTrademark BLOB,’;
    sSQL := sSQL + ‘varDescription BLOB,’;
    sSQL := sSQL + ‘varPrice BLOB,’;
    sSQL := sSQL + ‘varLocation BLOB,Picture BLOB COLLATE NOCASE,Ext TEXT)’;

    sl3db.execsql(sSQL);

    end else
    begin
    sl3tb.Free;
    sl3tb := sl3db.GetTable(‘SELECT * FROM warehouse’);
    sl3tb.MoveFirst;
    if sl3tb.Count > 0 then
    DisplayRecord;
    end;

    end;
    ======== END OF CODE ========

    Now, to search for a record in the database, I use a new form (frmSearchArticle) in which I insert exactly the same code as above within the TfrmSearchArticle.FormShow procedure.

    The problem is, now that my database contains more than 2000 records, when I load the search form, I get an error message telling me that the memory is insufficient and, consequently, the records are not loaded.

    I think this depends on the fact that, when I load the search form, I create the database a second time, but if I deleted the line:

    sl3db := TSQLiteDatabase.Create(DBFile);

    the search form could not have access to the database.

    Do you know how I could solve my problem? I will appreciate any help you may want to give me.

    Thanks in advance.

    Pasquale

  • I have noticed that the problem happens when the code I posted in my previous mail is repeated in a DIFFERENT form. In fact, when I duplicated the search code in a button contained in the main form (so, whithout opening a new form), the program worked fine.

    How is it possible that the memory available becomes insufficient only when the database and table are loaded again in a new form and not in the main form?

    I’m probably missing something elementary. Can anyone help me?

    Thanks again.

    Pasquale

  • alex

    How to retrieve all table names in the database?

  • Deali

    I am new to Delphi, learning it for 2 month now.
    This wrapper is genius, i got it directly to work, without any problems i am able to add very gig data to a db. Also retriving the data is easy. The only problem i have is to fill a list like “Name=Miller” if there are 8 “Miller” in the Database i can only get the first. I can see that the fRowCount is 8 but i cont get that into a list, i cant increase fRow.
    Has anyone a solution?

    Regards

    Deali

  • Hi All,

    Is this sqlite wrapper built for multi-threading? I’ve read on the sqlite website that either sqlite is compiled for multi-threading or it can be changed at runtime; but I belive it requires either sql_open_v2 or the sql_config interfaces, neither of which appear to be available from this component.

    An excellent component!

    Thanks,

    Paul

  • Marcus

    Hi!

    Thank you very much for your SQLite wrapper! It’s simple and runs very good regarding my few tests.
    I’m quite new to SQL and SQLite so I’m still playing around and learning. So as I understand it’s “good taste” to use UPPERCASE for SQL identifiers, but it’s not required. Is this correct?
    At least any SQL shell I tested had no problems with lowercase statements.

    The column type detection of this wrapper is case sensitive.
    A column declared as “ID INTEGER NOT NULL” is an integer column, but “ID integer not null” is reported as NOT being integer and FieldAsInteger() fails.

    Should this be fixed inside the wrapper or am I wrong?

    As I’m using some “old” tables I created by hand in lowercase, I’m going to recreate them in uppercase as a workaround.

    Thanks anyway for your nice work!

    /\/\arcus

  • tim

    Marcus

    Probably all you need to do is to amend this line in TSQLiteTable.Create, in SQLiteTable3.pas:

    DeclaredColType := Sqlite3_ColumnDeclType16(stmt, i);

    to

    DeclaredColType := UpperCase(Sqlite3_ColumnDeclType16(stmt, i));

    If you are not using the Unicode version, amend accordingly.

    SQL is normally not case-senstive.

    Tim

  • Dmitry

    Code:
    slDBPath := ‘c:\pas\diplom\diplom.db’;
    sldb := TSQLiteDatabase.Create(slDBPath);
    sldb.ExecSQL(‘update D_PROF set NAME = :NAME where ID = :ID;’,['soft',767]);

    or

    sldb.AddParamText(‘NAME’,’soft’));
    sldb.AddParamInt(‘ID’,767);
    sldb.ExecSQL(‘update D_PROF set NAME = :NAME where ID = :ID;’);

    Error executing SQL statement
    Error[1]: SQL error or missing database.

    Help me.

  • daoudzd

    Hi thank’s for this great sqlite wrapper,

    i hade somme errors while freeing tables and databases in loops:

    using
    sltb.free;
    SLdb.free;

    so i use
    FreeAndNil(sltb);
    FreeAndNil(sldb);

  • Len

    Hi there,

    Just getting started with SQLite using this wrapper, and it seems to me that a couple of the ’16′ suffixes have been left off the DLL references in the Unicode version, these being

    sqlite3_bind_text16 => external SQLiteDLL name ‘sqlite3_bind_text’;
    SQLite3_create_collation16 => external SQLiteDLL name ‘sqlite3_create_collation’;

    Thanks for the wrapper, Len.

  • Steven

    Hi Tim,

    I’m checking out your wrapper (the unicode version), thanks for posting it.

    Re:
    “TSqliteTable represents a resultset. It maintains no link to the source database”

    Is there anyway that a linked table can be created?
    If I update the database, I have to rerun the query and then locate the record I was editing to make the updates visible.

    thanks, Steven

  • tim

    Steven

    That’s not within the scope of this wrapper unfortunately – hence the “simple”!

    Tim

  • Steven

    >That’s not within the scope of this wrapper unfortunately – hence the “simple”!
    Thanks for the reply, No problem, I’ll work around it.

    A note about the Unicode version (http://www.itwriting.com/blog/3822-using-sqlite-3-with-unicode-in-delphi.html)

    I’m using it with Delphi2010 and I’m having the what appears to be same memory leak issues as reported by John
    on August 18, 2008
    http://www.itwriting.com/blog/articles/a-simple-delphi-wrapper-for-sqlite-3/comment-page-1#comment-105702

    When I had all the code in a single procedure or function – no reported leaks.
    Now that I declared the TSQLiteDatabase object as a global variable it looks like I’m having a leak whenever I access the database object – for example calling TableExists(‘sometable’).

    Eurekalog reports the leaks at:
    SQLiteTable3.pas Class: TSQLiteDatbase Method: GetTable Line: 531
    SQLiteTable3.pas Class: TSQLiteTable Method: Create Line: 809
    SQLiteTable3.pas Class: TSQLiteDatbase Method: TableExists Line: 622
    etc.
    Be happy to send you the report if you wish.

    Best regards,
    …Steven

  • tim

    Can you send me a simple app that demonstrates the leak?

    Thanks

    Tim

  • Steven

    Email sent to tim(at)itwriting.com gets returned as undeliverable.
    If you wish you can use the email I logged this comment with to send me your email.

  • tim

    You need to replace the (at) with @ – but I will send you an email anyway.

    Tim

  • Steven

    Looks like the email was being rejected because of the attachment.
    I put a zip containing the demo file package on one my websites and sent you an email with a link so that you can download it.

    Best regards,
    …Steven

  • tim

    Steven

    Whenever you create a TSqliteTable you have to free it. I found one instance in your code where this is not done. Then the project runs without leaks for me (I don’t have Eureka but using ReportMemoryLeaksOnShutdown).

    Tim

  • Steven

    Hi Tim,

    Appreciate you checking it out.

    Frankly I’m stumped.
    There are 4 procedures in the demo where I’m creating a TSqliteTable table and I’m freeing the table on each of those 4 areas before leaving the procedure.
    It must be in front of my face and I’m just not seeing it.

    procedure TmmCAT_SQLiteDB.BackupCats;
    var
    sl3tbl: TSqliteTable;

    begin
    fsldb.execsql(‘DROP TABLE IF EXISTS ‘ + cTblCatBU);
    CreateTable_CATBU;

    try
    sl3tbl := fsldb.GetTable(‘SELECT * FROM ‘ + cTblCategories);
    ….
    ….
    finally
    sl3tbl.Free;
    end;
    end;

    Sorry to be such a bother.

  • tim

    Did you get my email? It’s in the routine LoadDB

    procedure TmmCAT_SQLiteDB.LoadDB();
    var
    sl3tbl: TSqliteTable;
    sTmp :string;
    begin
    if fDBsLoaded then
    Exit;

    fsldb := TSQLiteDatabase.Create(fDBFilePath);
    CreateTables;

    try
    sl3tbl := fsldb.GetTable(‘SELECT * FROM ‘ + cTblCategories);
    fNumCatsDefined := sl3tbl.Count;

    //tim added to fix leak
    sl3tbl.Free;

    sl3tbl := fsldb.GetTable(‘SELECT * FROM ‘ + cTblStats + ‘ WHERE SType = “PRIMARY”‘);

    fLUPDate := 0;
    if sl3tbl.Count = 0 then
    Exit;

    sl3tbl.MoveFirst;
    try
    sTmp := sl3tbl.FieldAsString(sl3tbl.FieldIndex['LUPDate']);
    fLUPDate := StrToFloat(sTmp);
    except
    Exit;
    end;

    finally
    sl3tbl.Free;
    end;
    fDBsLoaded := True;
    end;

  • Steven

    Tim,

    Thanks so much for your help.
    Your fix solved the problem.

    I was making a wrong assumption about reusing the TSqliteTable object.
    Now that you’ve pointed it out it seem pretty obvious.

    Apologies about missing your email.

    Best regards,

    Steven Brenner

  • Mops

    Hi,
    I’m using this wrapper and I like it. But what about multiuser access?
    What’s happens if I will use one DB file by 2 threads?

  • tim

    @Mops see:

    http://www.sqlite.org/faq.html#q6

    That said, I really have not thought about this issue on the Delphi side.

    Tim

  • Olle

    Hi,
    I’m using the wrapper and I think I have spotted an error.
    When the database is locked and I call TSqliteTable.Create the database returns SQLITE_BUSY.
    But the code here raises en exception with the text ‘Could not prepare SQL statement’. I have tried to call TSQLiteDatabase.RaiseError instead and that gives a proper message including the error code indicating that the database is in fact locked!
    Can you confirm my view (or tell me what I don’t understand!)

    Olle

  • I encountered an unexpected “constraint failed” error when using a parameterized INSERT command. Here is a minimal code example:


    procedure Test(const F: TFilename);
    var
    db: TSQLiteDatabase;
    sql: string;
    begin
    db := TSQLiteDatabase.Create(F);
    try
    sql := 'CREATE TABLE test ( foo TEXT NOT NULL, CHECK (foo = ''bar'') )';
    db.ExecSQL(sql);
    sql := 'INSERT INTO test (foo) VALUES (@param)';
    db.AddParamText('@param', 'bar');
    db.ExecSQL(sql);
    finally
    db.Free;
    end;
    end;

    I would expect that the INSERT command is successful, but I recieve an error message stating a violation of the CHECK constraint. A direct INSERT command without a parameter works as expected:


    sql := 'INSERT INTO test (foo) VALUES (''bar'')';

    I am using Delphi XE 2 and Sqlite 3.7.10.

    Dieter

  • tim

    Dieter

    This is an error in SQLite3.pas. The declaration of sqlite3_bind_text16 is wrong. Should be:


    function sqlite3_bind_text16(hStmt: TSqliteStmt; ParamNum: integer;
    Text: PChar; numBytes: integer; ptrDestructor: TSQLite3Destructor): integer;
    cdecl; external SQLiteDLL name 'sqlite3_bind_text16';

    I have updated the unicode zip.

    Tim

  • Andre

    Hi,

    How is it possible to bind two databases at the same time with the sqlite wrapper? I found something with an attach statement on the sqlite website. But I cannot call it from TSQLiteDatabase with the wrapper. Later I would like to use union over these two databases.

    Thank you very much

    André

  • tim

    @Andre I think I’ve done this using the ATTACH statement, what doesn’t work?

    Tim

  • EricL

    Hi,

    Anyone can show some codes how to STORE and RETRIEVE TStrings type of data?

    For example, I have a TStrings variable “SOMETHING”.

    Thanks.

  • Erik

    hey Tim,

    your wrapper works perfect! Thanks for that! :)

    My problem is value binding as described in the readme.txt.
    First I tried this:

    sSQL := ‘INSERT INTO addressbook( prename,lastname,street,’ +
    ‘housenr,plz,city,telhome,’ +
    ‘handyprivate,telwork,handywork) ‘ +
    ‘VALUES(?,?,?,?,?,?,?,?,?,?) ‘ +
    [edtPrename.Text+','+edtLastname.Text+','+edtStreet.Text+','
    +edtHousenr.Text+','+edtPLZ.Text+','+edtCity.Text+','
    +edtTelHome.Text+','+edtHandyPrivate.Text+','
    +edtTelwork.Text+','+edtHandyWork.Text];
    db.ExcecSQL(sSQL);

    This hasn’t worked, there were a syntax failure.

    Than I tried this, following the example in readme.txt:

    db.ExecSQL(‘INSERT INTO addressbook( prename,lastname,street,’ +
    ‘housenr,plz,city,telhome,’ +
    ‘handyprivate,telwork,handywork) ‘ +
    ‘VALUES(?,?,?,?,?,?,?,?,?,?) ‘ +
    [edtPrename.Text, edtLastname.Text, edtStreet.Text,
    edtHousenr.Text, edtPLZ.Text, edtCity.Text,
    edtTelHome.Text, edtHandyPrivate.Text,
    edtTelwork.Text,edtHandyWork.Text]);
    Unfortunatly this way doesn’t work either! So can you help me? Probably it’s just a little failure… Thanks a lot!

    Greetings!

  • tim

    Unicode version? Did you apply the fix mentioned a few comments back?

    Tim

  • Erik

    It can’t be Unicode because I still use Turbo Delphi. :D

    No, not really I think. Maybe that’s the mistake? I will search in the comments for the fix mentioned thing… Maybe you also have another idea?

    Erik

  • kom

    Hi,all.
    How to use Full-text search with Sqlite 3 in delphi?

  • kom

    How use FTS3 in Delphi?

  • I try to compile in lazarus/fpc and get thses errors:

    /usr/bin/ld: warning: link.res contains output sections; did you forget -T?
    /usr/bin/ld: cannot find -lsqlite3
    project1.lpr(19,1) Error: Error while linking
    project1.lpr(19,1) Fatal: There were 1 errors compiling module, stopping

    I then copy the sqlite3.so to folder of the project and then retry to compile and get the same error, How I can solve this?

    thank’S

  • Thank you for your wrapper!

  • Very nice tool. I’m testing it under Delphi 7.

    Question: How to get a list of all tablenames in a given DB?

    Thanks in advance,
    Werner

  • Andreas

    List of Tablenames:

    tab:=db.GetTable(‘SELECT name from SQLITE_MASTER where type=”table” order by name’);
    while not tab.EOF do begin
    ListBox1.Items.Add(tab.Fields[0]);
    tab.Next;
    end;
    tab.Free;

  • mica

    @Armando
    you are missing the sqlite devel packages
    copying the sqlite3.so to project folder helps nothing.Linux is not windows

    Simple Delphi Wrapper works well with Lazarus 1.1,the Unicode Version not
    because differences between Delphi and FPC.

  • Len

    Hi Tim,

    I have been fiddling with this wrapper for a while now, and it has been very useful.
    However, I see that you have updated the Unicode version to add the 16 suffix to the DLL mapping for sqlite3_bind_text16 but you have missed the mapping for SQLite3_create_collation16 which I also pointed out on August 29, 2011.
    Recently I discovered a bug in SQLiteTable3.pas in SetParams() on line 719 where the expression
    “length(par.valuedata)” should be “length(par.valuedata) * SizeOf(char)” as my strings were only half the length they should have been :-)

    Len.

  • Bill

    Hey Tim,

    I found a bug as demonstrated in the following code. Basically, the ColType that is being set is preventing me from accessing the data if the first row of the table has a NULL value in that column.

    unit uMain;

    interface

    uses
    Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
    Dialogs, StdCtrls, Grids,

    // For this demo, we need access to the private fColTypes in TSQLiteTable.
    // Added the following property for this purpose:
    // property ColTypes: TList read fColTypes;
    SQLiteTable3;

    const
    g_DATABASE = ‘Sample.db’;

    type
    TForm1 = class(TForm)
    StringGrid1: TStringGrid; // Set Align to alTop
    Button1: TButton;
    Button2: TButton;
    procedure FormCreate(Sender: TObject);
    procedure FormResize(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    private
    procedure LoadDataInGrid;
    procedure SetColWidths;
    end;

    var
    Form1: TForm1;
    SLDB: TSQLiteDatabase;

    implementation

    {$R *.dfm}

    procedure TForm1.FormCreate(Sender: TObject);
    begin
    SetColWidths;

    SLDB := TSQLiteDatabase.Create(g_DataBase);
    try
    SLDB.ExecSql(‘DROP TABLE IF EXISTS “Test”‘);
    SLDB.ExecSql(‘CREATE TABLE “Test” ‘ +
    ‘(“IDKey” INTEGER NOT NULL, ‘ +
    ‘ “Name” Text, ‘ +
    ‘ “Date1″ Integer,’ +
    ‘ “Date2″ Integer,’ +
    ‘ CONSTRAINT “PK_Test” PRIMARY KEY (“IDKey”) );’);

    SLDB.ExecSql(‘Insert into test (Name, Date1, Date2) ‘ +
    ‘values (“Adam”, DateTime(”Now”), null);’);

    SLDB.ExecSql(‘Insert into test (Name, Date1, Date2) ‘ +
    ‘values (“Bart”, DateTime(”Now”), null);’);

    // Has a date in the third column
    SLDB.ExecSql(‘Insert into test (Name, Date1, Date2) ‘ +
    ‘values (“Carl”, DateTime(”Now”), Date(”Now”));’);

    SLDB.ExecSql(‘Insert into test (Name, Date1, Date2) ‘ +
    ‘values (“Dave”, null, null);’);

    SLDB.ExecSql(‘Insert into test (Name, Date1, Date2) ‘ +
    ‘values (“Eric”, DateTime(”Now”), null);’);

    SLDB.ExecSql(‘Insert into test (Name, Date1, Date2) ‘ +
    ‘values (“Fred”, null, null);’);

    LoadDataInGrid;
    finally
    SLDB.Destroy;
    end;
    end;

    procedure TForm1.FormResize(Sender: TObject);
    begin
    SetColWidths;
    end;

    procedure TForm1.SetColWidths;
    var
    I: integer;
    begin
    for I := 0 to StringGrid1.ColCount – 1 do
    StringGrid1.ColWidths[i] := (Self.ClientWidth – 7) div StringGrid1.ColCount;
    end;

    procedure TForm1.LoadDataInGrid;
    var
    sltb: TSQLiteTable;
    ARow : integer;
    ACol : integer;
    begin
    SLTB := SLDB.GetTable(
    ‘ SELECT ‘ +
    ‘ IDKey, ‘ +
    ‘ Name, ‘ +
    ‘ DateTime(Date1, “LocalTime”) as DateA, ‘ +
    ‘ DateTime(Date2, “LocalTime”) as DateB, ‘ + // ColTypes = dtNull when first row contains NULL
    ‘ coalesce(DateTime(Date1, “LocalTime”), “”) as DateC, ‘ +
    ‘ coalesce(DateTime(Date2, “LocalTime”), “”) as DateD ‘ + // ColTypes = dtStr
    ‘ FROM Test; ‘);

    try
    StringGrid1.Cells[0, 0] := format(‘IDKey (%d)’,[pInteger(sltb.ColTypes[0])^]);
    StringGrid1.Cells[1, 0] := format(‘Name (%d)’,[pInteger(sltb.ColTypes[1])^]);
    StringGrid1.Cells[2, 0] := format(‘DateA (%d)’,[pInteger(sltb.ColTypes[2])^]);
    StringGrid1.Cells[3, 0] := format(‘DateB (%d)’,[pInteger(sltb.ColTypes[3])^]);
    StringGrid1.Cells[4, 0] := format(‘DateC (%d)’,[pInteger(sltb.ColTypes[4])^]);
    StringGrid1.Cells[5, 0] := format(‘DateD (%d)’,[pInteger(sltb.ColTypes[5])^]);

    while not sltb.EOF do
    begin
    ARow := sltb.FieldAsInteger(sltb.FieldIndex['IDKey']);
    for ACol := 0 to sltb.ColCount – 1 do
    StringGrid1.Cells[ACol, ARow] := sltb.FieldAsString(ACol);
    sltb.Next;
    end
    finally
    sltb.Free;
    end;
    end;

    procedure TForm1.Button1Click(Sender: TObject);
    begin
    SLDB := TSQLiteDatabase.Create(g_DataBase);
    try
    SLDB.ExecSql(‘Update Test Set Date2 = DateTime(”Now”) Where IDKey = 1;’);
    LoadDataInGrid;
    finally
    SLDB.Destroy;
    end;
    end;

    procedure TForm1.Button2Click(Sender: TObject);
    begin
    SLDB := TSQLiteDatabase.Create(g_DataBase);
    try
    SLDB.ExecSql(‘Update Test Set Date2 = NULL Where IDKey = 1;’);
    LoadDataInGrid;
    finally
    SLDB.Destroy;
    end;
    end;

    end.

  • Thanks a lot! This wrapper helped me a lot in my development!

  • Hi Tim and all,

    I have found that the following line in SQLiteTable3.pas, TSQLiteDatabase.Create constructor does not work for Cyrillic filenames:

    utf8FileName := UTF8String(FileName); // does not work

    My fix is:

    var
    WideStringFileName : WideString;

    WideStringFileName:=FileName;
    utf8FileName:=UTF8Encode(WideStringFileName);

    Not thoroughly tested yet, but now I am able to open a database with Cyrillic filename.

    Great wrapper for the great database system. Thank you.
    Best regards,
    Dmitry.

  • I was able to get inserts to work, but I am unable to get delete or updates to work

    example:

    procedure TfrmMain.actDeleteExecute(Sender: TObject);
    begin
    DB.AddParamText(‘AItem’, lvwMain.Selected.Caption);
    DB.ExecSql(‘DELETE FROM Inventory WHERE Item = :AItem;’);
    end;

    I even tried hard coding it

    DB.AddParamText(‘AItem’, ‘ab’);
    DB.ExecSql(‘DELETE FROM Inventory WHERE Item = :AItem;’);

    Shane

  • OK, I got them to work! Get seem to get Dates inserted though and Blobs. Can I see an example of a Date and Blob insert? – Thanx!

  • Stefan Hahne

    Ich habe da eine Fehlermeldung. In der Unit SQLiteTable3 ist angeblich die erste Zeile zu land (>1023 Zeichen). Das ganze unter Delphi 5 standard. Woran kann das liegen?
    Danke.

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=""> <strike> <strong>