Better-sqlite3: Eliminate RangeError: The supplied SQL string contains more than one statement

[eldargab]

Hello,

this error is pretty unexpected, since most of the drivers handle this and you sometimes just need to execute multiple statements coming from external file.

[JoshuaWise]

You can use db.exec(string) to execute an arbitrary SQL string.

[eldargab]

Ah, I missed db.exec(). Even though I would still say it's unexpected and incompatible with the rest of the field, also perhaps could be problematic in some rare cases. Why did you add this check in the first place?

[JoshuaWise]

It's not incompatible with other implementations. sqlite prepared statements can only contain a single SQL statement. This is a restriction of sqlite itself, not better-sqlite3. The default behavior is for it to compile successfully, although executing the prepared statement will silently ignore any SQL beyond the first statement in the source SQL.

The check that I wrote is only to provide a nice error message instead of it silently ignoring the rest of your SQL.

[JoshuaWise]

Closing this. Feel free to open it back up if you have more questions or concerns.

[ronburk]

This error appears to be emitted when there are any characters after the first statement, including white space. This makes the error message factually incorrect, and confusing to understand what the actual problem is, since one doesn't expect SQL to die just because of, e.g., a trailing newline.

[wchargin]

As @ronburk correctly notes, there are false positives when whitespace or comments appear after the semicolon of the unique statement. However, these false positives do not occur if you remove the semicolon.

That is, this fails:

const sql = 
  `
  CREATE TABLE foo (
    one INTEGER PRIMARY KEY,
    two TEXT NOT NULL
  );
  `;
db.prepare(sql).run();  // error!

…but this works:

const sql = 
  `
  CREATE TABLE foo (
    one INTEGER PRIMARY KEY,
    two TEXT NOT NULL
  )  -- no semicolon: whitespace and comments are fine now
  `;
db.prepare(sql).run();  // no error

This is indeed frustrating.

[wchargin]

One fix would be to call sqlite3_prepare16_v2 twice, and check that ppStmt is set to non-NULL in the first call and NULL in the second. Something like (untested):

// src/objects/statement.lzz@89579fd576e1:89
        v8::String::Value sql(source);
        const void *tail;
        sqlite3_stmt *handle, *handle_rest;

        if (sqlite3_prepare16_v2(db->GetHandle(), *sql, sql.length() * sizeof(uint16_t) + 1, &handle, &tail) != SQLITE_OK) {
            return db->ThrowDatabaseError();
        }
        if (handle == NULL) {
            return ThrowRangeError("The supplied SQL string contains no statements");
        }
        if (sqlite3_prepare16_v2(db->GetHandle(), tail, sql.length() * sizeof(uint16_t) + 1 - (tail - *sql), &handle_rest, NULL) != SQLITE_OK) {
            sqlite3_finalize(handle);
            return db->ThrowDatabaseError();
        }
        if (tail != NULL) {
            sqlite3_finalize(handle);
            sqlite3_finalize(tail);
            return ThrowRangeError("The supplied SQL string contains more than one statement");
        }

This would be slightly more expensive, but only significantly so in the case where an error is thrown anyway. In the happy path, you’re parsing a whitespace-and-comment-only string.