Re: [Chicken-users] Strange sqlite3 egg error

[Thomas Christian Chust]

Alex Queiroz wrote:

> [...]
>     I'm getting strange errors with sqlite3:exec.
> [...]
>     Is there any problem with passing #f to sqlite3:exec?
> [...]

Hello Alex,

yes, there is a problem passing #f to sqlite3:exec. The problem is (at
least implicitly) documented and I have (at least twice) described it on
this list ;-)

But never mind, I'll explain it again: sqlite3:exec internally calls
upon sqlite3:bind! to bind the free parameters of the compiled
statement. sqlite3:bind! is a TinyCLOS generic procedure which usually
takes three arguments: A statement, a parameter index and a parameter.
The generic procedure is specialized to do the right thing for different
types of its third argument, but it does *not* have a specialization for
booleans. Thus passing #t or #f of course results in an error
(unfortunately the error message isn't necessarily very clear about
where the problem lies, but that's not SQLite3's fault).

Now before you ask me to fix this behavior, let me explain *why* there
is no specialization on booleans: As a return value from SQLite3
procedures, #f represents the SQL NULL value, so one may wish to use it
in this sense as an input parameter as well. But one cannot specialize
the sqlite3:bind! procedure on #f as the third argument alone, one has
to specialize it on booleans. So one has to decide on the SQL
representation of #t and #f at the same time. I felt that there were
equally many cases where one would want to encode #t and #f as the
integers 1 and 0 on the SQL side, or as some other specific values like
'y' and 'n', as there were cases where the encoding of #t didn't really
matter and #f should be represented as NULL. Thus I decided not to force
any particular encoding upon the user of the sqlite3 egg and to simply
omit the method specialization of sqlite3:bind! on a boolean parameter.

You can, of course, add your own specialization which suits your needs.
For example, the following (untested) variants should work:

  ;; encode #t as 1, #f as 0
  (define-generic (sqlite3:bind! (stmt <sqlite3:statement>) (i <exact>)
                                 (v <boolean>))
    (if v
        (sqlite3:bind! stmt i 1)
        (sqlite3:bind! stmt i 0)))

  ;; encode #t as 'TRUE', #f as NULL
  (define-generic (sqlite3:bind! (stmt <sqlite3:statement>) (i <exact>)
                                 (v <boolean>))
    (if v
        (sqlite3:bind! stmt i "TRUE")
        (sqlite3:bind! stmt i)))

As adding the method to your own code instead of the sqlite3 egg only
costs you about five lines of code but offers more flexibility, I think
my design decision was right -- but I'm open for suggestions of a more
clever solution which automatically does the right thing in many cases.

I hope that helps,
Thomas


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