Attempt to re-use closed prepared statement

[LiberalArtist]

The following program triggers the error "query-exec: prepared statement is closed":

#lang racket

(require db sql)

(define pth
  (make-temporary-file))

(define db
  (virtual-connection ;; seems to be necessary
    (λ ()
      (sqlite3-connect
       #:database pth
       #:use-place #t ;; seems to be necessary
       #:mode 'create))))

(call-with-transaction ;; seems to be necessary
 db
 (λ () 
   (query-exec
    db
    (create-table things
                  #:columns [thing text #:not-null]))
   (for ([str '("a" "b" "c" "d" "e" "f" "g")]
         [i (in-naturals)])
     (displayln i)
     (query-exec
      db
      (insert #:into things
              #:set [thing ,str]))
     ;; some delay seems to be needed to trigger the error
     (sleep 5))))

The iteration on which the error happens doesn't seem to be consistent: it can happen as soon as when (= i 1), and I have seen it as late as when (= i 3). It's possible that the virtual-connection, the use of a place, etc. may just be contributing to the delay, but I haven't managed to reproduce the error without all of these elements.

I was able to work around the error by changing the call to the prepare method in: https://github.com/rmculpepper/sql/blob/fdba33511e0feb5692b4c164f72a8ed2274ace46/private/syntax.rkt#L114-L121 Changing the close-on-exec? argument to #f produced a different error message, "sql-statement: cannot prepare statement with virtual connection." The change that ultimately worked for me was (define pst (send (send c get-base) prepare 'sql-statement sql #f)). I doubt that's really the right solution (I assume there's a good reason for the close-on-exec? argument), but hopefully it helps to narrow down the problem.

[rmculpepper]

I think the problem has to do with connection proxying across places, prepared statements, and the statement cache. I was able to remove the dependency on the sql library, and I was also able to get the error without virtual-connection by explicitly calling collect-garbage at the end of the loop:

(define db
  (sqlite3-connect
   #:database pth
   #:use-place #t ;; seems to be necessary
   #:mode 'create))

(call-with-transaction db ;; seems to be necessary
  (λ () 
    (query-exec db "create table things (thing text not null)")
    (for ([str '("a" "b" "c" "d" "e" "f" "g")]
          [i (in-naturals)])
      (displayln i)
      (query-exec db "insert into things (thing) values (?)" str)
      ;; GC seems to be needed to trigger the error
      (collect-garbage))))

[LiberalArtist]

Thanks for the quick repair! Do you see a good way to work around this from client code, or do I need to get the patched version of db?

[rmculpepper]

Sorry, I can't think of a way to work around it.