Summary

sqlite3: The PREPARE procedure does not make it clear when it has and has not parsed a statement from a string.

Metadata

• Id: 6659d8855a2089f1d8575be9daa941b2c2d3e3ac
• Trac id: 1553
• Type: enhancement
• Reporter: nxg
• Owner:
• Cc:
• Status: new
• Component: extensions
• Estimated difficulty: easy
• Resolution:
• Priority: major
• Milestone: someday
• Version: 4.13.0
• Changetime: 2018-10-21 13:16:01 UTC
• Created: 2018-10-21 13:10:01 UTC
• Keywords: sqlite3

Description

The PREPARE procedure does not make it clear when it has and has not parsed a statement from a string.

The calls `(prepare db "")` and `(prepare db "select 1;"))` (note no trailing whitespace) both produce a `sqlite3:statement` and `""`, and there is no way of distinguishing between these. In the former case, the statement displays as `#<sqlite3:statement zombie>`, but there is no way of detecting this in code.

The documentation for PREPARE seems to suggest that a way of parsing a sequence of SQL statements from a string is to call PREPARE recursively on PREPARE's `rest` return, until `rest` returns `""`. Thus:

 (define (execute-statements* sql-string)
   (let-values (((stmt rest) (prepare db sql-string)))
     (if (string-null? rest)
         (finalize! stmt)
         (begin
           (dynamic-wind
               values
               (lambda ()
                 (update stmt))
               (lambda ()
                 (finalize! stmt)))
           (execute-statements* rest)))))

This will work //if and only if// the SQL string ends in whitespace, but will not work if the input string has no trailing whitespace or if it ends in a comment. Spotting when `sql-string` is all whitespace, and special-casing this, helps in many cases, but doesn't help in the case where `sql-string` contains, or has through this process ended up containing, only a comment.

There //might// be a way of wrapping this up which solves the problem, and there might be a way of deducing that a statement is a 'zombie' by (mis)using other procedures, but more straightforward would be one of the following:

• Create a STATEMENT-ZOMBIE? predicate for the case where (in the implementation) `statement-ptr` is `#f`, and a statement would display as `zombie`. Advantages: straightforward to implement; quite specific. Disadvantages: introduces an extra concept which isn't really present in the underlying SQLite library, and which appears to be meaningful only for this case.
• Have STATEMENT? return `#f` for 'zombie' statements. Advantages: matches the real semantics of the situation; so is probably the least surprising thing. Disadvantages: might in principle confuse some future type reasoning, which might assume that if something is a statement, then it can be passed to UPDATE without error.
• Have PREPARE return `#f` for the statement when no statement is parsed. Advantages: also unsurprising, and easy to check for without changing the definition of STATEMENT?. Disadvantates: some complication to the type signature of PREPARE.

Of the three, I think the first is unattractive, and the second slightly more attractive than the third.

I've marked this as an enhancement request, though I think it's on the border of being a bug. I don't know what the criteria are for major vs minor priority.

Changes and comments

[2018-10-21 13:16:01 UTC] nxg wrote:

(And I appear to be using v3.7.0 of the egg -- the most current version)