172 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. @Didier

   For the wrapper, I just mean having a data access module that returns custom objects. Then you can write code to display these in a grid or any way you like. Sorry, I can’t go into more detail than that.

   See here for info on appropriate uses of Sqlite:

   http://www.sqlite.org/whentouse.html

   Tim

5. Thanks for all Tim!

6. Awesome work, Tim.
   So what the trouble with Unicoding the wrapper?

7. 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()?

8. @Pa look here:

   http://www.sqlite.org/c3ref/column_name.html

   Tim

9. @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.

10. 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?

11. 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!

12. 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)

13. 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.

14. 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! 🙂

15. Is there support for Database AES encryption or another encryption system?
    If no, is it possible add it?

16. @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

17. 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?

18. Hello Tim,
    have you received my last mail with detailled bug information?

    Mao

    1. Yes I have, hope to get to it soon! Will try and look at it today.

       Tim

19. 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!

20. 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;

21. 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 🙂

22. 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! 🙂

23. 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???

24. Koen

    This wrapper does not support TDataSet but there are others which do. See http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

    Tim

25. 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

Comments are closed.