172 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. Re. commercial license: you can use the code as you like.

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

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

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

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

10. @Christian

    I’ve added the MoveTo function.

    Tim

11. Thanks for the great SQLite component Tim.

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

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

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

15. hi..

    how can use this for Delphi 2009?

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

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

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

19. For Delphi 2009 some Strings must be converted to AnsiStrings, same for PChars wich must be PAnsiChars…

20. Can someone explain, how to do these changes to get it working with Delphi 2009?

21. @Marco

    It’s on my to-do list; I’ll do an update soon.

    Tim

22. Great Tim,

    thanks a lot. I´m new to Delphi and couldn´t
    figure out myself.

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

24. Great Job Tim,

    works for now. Thank you so much for starting me up. 🙂

    Cheers
    Marco

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

Comments are closed.