How do I use parameters in SQLite?

[Ikke]

Hi everybody,

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]

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

I'm afraid I can't be much help, never having worked with this library.
But there are some things that jump out anyway.

Post by Ikke
---
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.

Post by Ikke
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?

Post by Ikke
result := seq;
end;
---
Using db.GetTable (without preparing the SQL) doesn't work either,
and I have no idea what I'm doing wrong :(
---
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]

Hi Maarten,

<snip>

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

Now, however, I'd like to use the library for a decent project, so I'd
like to get it right :)

<snipped some code>

Post by Maarten Wiltink

Post by Ikke
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.

Post by Maarten Wiltink
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>

Post by Maarten Wiltink

Post by Ikke
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>

Post by Maarten Wiltink

Post by Ikke
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]

Post by Ikke

Post by 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]

Post by Maarten Wiltink

Post by Ikke

Post by 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

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;