Re: [Chicken-users] sqlite3 NULL parameters

[Thomas Christian Chust]

Joshua Griffith wrote:

> [...]
> Is it possible to pass a NULL parameter to a prepared sqlite3
> statement?  Attempts execute the sqlite3:exec procedure with symbols
> 'NULL or #f as parameters fail with "Error: bad argument count".
> [...]

Hello,

well, of course it's possible.

The problem is the following: It makes sense to *return* #f from query
statements where a NULL value is encountered in the database. But the
reverse mapping is *not* canonical.

All parameter binding happens by calls to the TinyCLOS generic
(sqlite3:bind! statement parameter-index some-data) and the only way to
allow a bind parameter of #f or 'null is to specialize this method's
third parameter on <boolean> or <symbol> -- but it is not at all clear,
what sqlite3:bind! should do to such data. You may want to map #f and #t
to 0 and 1 truth values or to NULL and 1 or to NULL and signal an error
for #t because it has no canonical representation or whatever. And for
symbols you may want to store their names as strings or do other fancy
stuff.

So, when writing the SQLite3 egg, I decided rather not to provide
default implementations of sqlite3:bind! for boolean or symbol data
instead of implementations that are useful for a small number of cases
but are annoying in other cases. But I provided a two argument version
(sqlite3:bind! statement parameter-index) which binds the given
parameter to NULL.

This allows you to bind NULL values to statement parameters when using
the lower-level statement interface. But of course it also allows you to
implement your favorite additional specializations for sqlite3:bind!.
For example the following snippet may achieve what you want:

  (define-method (sqlite3:bind! (stmt <sqlite3:statement>) (i <exact>)
                                (b <boolean>))
    (if b
      (sqlite3:bind! stmt i 1)
      (sqlite3:bind! stmt i)))

I hope that helps :-)

cu,
Thomas


-- 
Murphy's Law is recursive.  Washing your car to make it rain doesn't work.