How do I use parameters in SQLite?
Ikke
I'm trying to do some basic select/insert/update stuff with an SQLite
database, and I'm using the SQLite 3 unit by Tim Anderson, which I found
on the internet.
Everything works just fine when I pass complete sql statements, but I
cannot seem to get parameters to work. I've tried just about every
possible combination, but nothing seems to work.
Here's a bit of code:
---
function FindByName(name : String) : integer;
var
db : TSQLiteDatabase;
tb : TSQLiteTable;
seq : integer;
query : TSQLiteQuery;
begin
seq := -1;
db := OpenDatabase; // function which returns an TSQLiteDatabase
try
query := db.PrepareSQL('SELECT seq FROM thing WHERE LOWER(name) =
:Name');
db.AddParamText(':Name', name);
tb := db.GetTable(query.SQL);
if (tb.Count > 0) then
begin
seq := tb.FieldAsInteger(0);
end;
except on e : Exception do
seq := -1;
end;
CloseDatabase(db); // basically just FreeAndNil(db);
result := seq;
end;
---
Using db.GetTable (without preparing the SQL) doesn't work either, and I
have no idea what I'm doing wrong :(
Another attempt:
---
query := db.PrepareSQL('INSERT INTO thing (seq, name) VALUES (:Seq,
:Name);');
db.BindSQL(query, 1, seq); // also tried index 0
db.BindSQL(query, 2, name); // also tried index 1
db.ExecSQL(query);
db.ReleaseSQL(query);
---
didn't work either - I've got one insert statement working, it inserted
the seq (an integer value), and the first letter of the name (no idea
why).
Does anybody else use these SQLite 3 units? If so, could you please have
a look at my code and point out the errors? I would be ever so grateful!
Thanks in advance,
Ikke
Maarten Wiltink
news:Xns9E3C161078...@69.16.176.253...
> I'm trying to do some basic select/insert/update stuff with an SQLite
> database, and I'm using the SQLite 3 unit by Tim Anderson, which I
> found on the internet.
>
> Everything works just fine when I pass complete sql statements, but I
> cannot seem to get parameters to work. I've tried just about every
> possible combination, but nothing seems to work.
I'm afraid I can't be much help, never having worked with this library.
But there are some things that jump out anyway.
> Here's a bit of code:
> ---
> function FindByName(name : String) : integer;
> var
> db : TSQLiteDatabase;
> tb : TSQLiteTable;
> seq : integer;
> query : TSQLiteQuery;
> begin
> seq := -1;
> db := OpenDatabase; // function which returns an TSQLiteDatabase
> try
> query := db.PrepareSQL('SELECT seq FROM thing WHERE LOWER(name) =
> :Name');
> db.AddParamText(':Name', name);
I'd expect the parameters to be on the query object.
Incidentally, if you had an index on the Name column, checking the
lower-cased value of it will force you back to a table scan.
> tb := db.GetTable(query.SQL);
> if (tb.Count > 0) then
> begin
> seq := tb.FieldAsInteger(0);
> end;
> except on e : Exception do
> seq := -1;
> end;
> CloseDatabase(db); // basically just FreeAndNil(db);
So where's your try-finally?
> result := seq;
> end;
> ---
>
> Using db.GetTable (without preparing the SQL) doesn't work either,
> and I have no idea what I'm doing wrong :(
>
> Another attempt:
> ---
> query := db.PrepareSQL('INSERT INTO thing (seq, name) VALUES (:Seq,
> :Name);');
> db.BindSQL(query, 1, seq); // also tried index 0
> db.BindSQL(query, 2, name); // also tried index 1
> db.ExecSQL(query);
> db.ReleaseSQL(query);
> ---
> didn't work either - I've got one insert statement working, it inserted
> the seq (an integer value), and the first letter of the name (no idea
> why).
That's usually an indication that you've passed a 16-bit Unicode
string which is parsed as an 8-bit NUL-terminated string.
Groetjes,
Maarten Wiltink
Ikke
<snip>
> I'm afraid I can't be much help, never having worked with this library.
> But there are some things that jump out anyway.
Thanks anyway for your reply - I've worked with this library before, but
only as a very quick "test". Parameters were just added to the query by
concatenating strings and data, no additional checks were made.
Now, however, I'd like to use the library for a decent project, so I'd
like to get it right :)
<snipped some code>
>> try
>> query := db.PrepareSQL('SELECT seq FROM thing WHERE LOWER(name) =
>> :Name');
>> db.AddParamText(':Name', name);
>
> I'd expect the parameters to be on the query object.
That's what I expected as well. I've worked with other libraries in the
past (for other DBs), and to my surprise the query object only holds the
SQL string, and a statement pointer. No parameters though.
> Incidentally, if you had an index on the Name column, checking the
> lower-cased value of it will force you back to a table scan.
I realise - the table is (and will remain) quite small, so a full table
scan in this case is not a big problem. Thanks for pointing it out,
though.
<snipped some more code>
>> except on e : Exception do
>> seq := -1;
>> end;
>> CloseDatabase(db); // basically just FreeAndNil(db);
>
> So where's your try-finally?
Code in progress :)
Well, actually this is one of my pet peeves of Delphi. Coming from a Java
background, I've learned to handle exceptions in a single try-catch-
finally, whereas in Delphi I always need to add two try-s.
But you're right, the finally should have been added to handle the
CloseDatabase call.
<snip>
>> I've got one insert statement working, it inserted
>> the seq (an integer value), and the first letter of the name (no idea
>> why).
>
> That's usually an indication that you've passed a 16-bit Unicode
> string which is parsed as an 8-bit NUL-terminated string.
Ah, I see... Good to know, I'll keep it in mind!
Thanks for your help, Maarten!
Ikke
Maarten Wiltink
>> I'm afraid I can't be much help, never having worked with this
>> library. But there are some things that jump out anyway.
>
> Thanks anyway for your reply - I've worked with this library before,
> but only as a very quick "test". Parameters were just added to the
> query by concatenating strings and data, no additional checks were
> made.
That always remains an option, and I've done it myself in generating
complete queries as text. If you're careful with the types of your
variants, and handle string escaping correctly, there should be no
problems. I don't suppose a library such as this will support prepared
queries anyway.
Groetjes,
Maarten Wiltink
rw11
wrote:
> "Ikke" <i...@hier.be> wrote in message
Może tak :
function FindByName(name : String) : integer;
var
db : TSQLiteDatabase;
tb : TSQLiteTable;
seq : integer;
query : TSQLiteQuery;
begin
seq := -1;
db := OpenDatabase; // function which returns an TSQLiteDatabase
try
query := db.PrepareSQL('SELECT seq FROM thing WHERE LOWER(name) =
:Name');
db.AddParamText(':Name', ansitoutf8(name));
tb := db.GetTable(query.SQL);
if (tb.Count > 0) then
begin
seq := tb.FieldAsInteger(0);
end;
except on e : Exception do
seq := -1;
end;
CloseDatabase(db); // basically just FreeAndNil(db);
result := seq;
end;