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

[Norman Gray]

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.

[Norman Gray]

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