[Chicken-users] Re: Problem with sqlite3 egg

[Thomas Christian Chust]

Alejandro Forero Cuervo wrote:

> [...]
> I've been having some problems getting the sqlite3:for-each-row
> procedure from the sqlite3 egg to play along nicely with the
> iterator->stream procedure from the stream-ext egg.
> [...]

Hello Alejandro,

if you pass an SQL string to sqlite3:for-each-row as the statement, the
code compiles this string creating a temporary statement handle which is
then finalized upon exit from the dynamic scope of sqlite3:for-each-row
using a dynamic-wind construct. Of course it is impossible to apply
iterator->stream in that situation, because reentry into the dynamic
scope doesn't recompile the now invalidated statement.

I actually realized this problem when I coded the sqlite3:for-each
procedure, but I think it is not possible to avoid it both automatically
and elegantly: Finalization of the temporary statement has to occur upon
scope exit and not upon end of iteration in order to allow for example
error exits from the iteration without leaking statement handles.
Shifting finalization of the handle into a garbage collector controlled
finalizer is not acceptable as statement finalization can be time
critical (it is linked to transaction locks) and could required error
checking. Thus the only fully automatic solution here would be to
recompile the statement upon reentry into the dynamic scope of the
iteration, but that would be terribly inefficient and error prone as the
database schema may have changed in between and then some strange and
badly debuggable errors could follow. Thus I decided not to automate
this process.

However, you can always pass your own precompiled statement handle to
sqlite3:for-each and iterate over it. In this case you are responsible
for finalization of the handle yourself and you can make sure that it is
valid long enough for the operation required on it to complete. For
example the following would work:

  (use sqlite3 stream-ext)

  ;; create a database and fill it with junk
  (define db
    (sqlite3:open ":memory:"))
  (sqlite3:exec
    db "CREATE TABLE foo(id INTEGER, text TEXT, PRIMARY KEY(id));")

  (sqlite3:call-with-temporary-statements
    (lambda (stmt)
      (for-each
        (cut sqlite3:exec stmt <>)
        '("bla" "blubb" "didubb")))
    db "INSERT INTO foo(text) VALUES(?);")

  ;; create a stream of values in the foo table and print its second
  ;; element
  (print
    (sqlite3:call-with-temporary-statements
      (lambda (stmt)
        (let ((str (iterator->stream
                     (lambda (it _)
                       (sqlite3:for-each-row it stmt)))))
          (stream-cadr str)))
      db "SELECT text FROM foo;"))

I hope this helps,
Thomas