Re: [Chicken-users] Strange format sqlite3 interaction

[Thomas Christian Chust]

John Gillespie schrieb:

> The problem illustrated below is driving me crazy(er).  When
> sqlite3:prepare is called with a literal string, it works as
> advertised.  When called using format to generate the string, it fails!
> [...]

Hello John,

this problem may be related to the fact that at least most versions of
SQLite3's SQL parser require each statement to be terminated with ';'.
You should try whether (sqlite3:prepare *tdb* (format "select * from
nodes where id=~a;" 1)) works in your example.

However, what you are doing here is very bad SQL programming practice.
Instead of constructing a query with some string operations, which may
introduce nasty quoting issues, you should consider using placeholders
in your SQL statement. Your example could be rewritten as
(sqlite3:prepare *tdb* "select * from nodes where id=?;") and the
resulting statement could then be called with an additional parameter
filling the place of the '?' in the query string: (sqlite3:map-row list
*stmt* 1). The two steps of compiling and executing the statement can of
course be combined as usual: (sqlite3:map-row list *tdb* "select * from
nodes where id=?;" 1)

I hope that helps.

cu,
Thomas