SQLite Forum

Feature request: SQLITE_PREPARE_MULTISTMT
Login

Feature request: SQLITE_PREPARE_MULTISTMT

(1) By anonymous on 2021-05-21 18:15:49 [link] [source]

My idea is to add a flag to prepare multiple SQL statements into a single VDBE, by adding a flag into sqlite3_prepare_v3 to indicate that. When that flag is not used, it prepares single statements like the current implementation does. For some applications, this might be a useful convenience rather than having to do it in a more complicated way.

  • The only statements allowed are SELECT, INSERT, DELETE, and UPDATE (and their variants, e.g. REPLACE). WITH and RETURNING are also allowed. Any other statement is an error (perhaps there could be a extended error code SQLITE_ERROR_MULTISTMT for this case, so that the caller can decide whether or not to try again without the SQLITE_PREPARE_MULTISTMT flag).

  • The implicit transaction is the same for all statements in the VDBE.

  • Named and numbered host parameters are shared between all statements in the VDBE. Host parameters denoted only ? without a number should not be used in this mode.

  • The implementation might require all statements with result columns to have the same number of result columns, if that would simplify the implementation. If differing column counts are allowed, there are a few ways that it could be handled (e.g. sqlite3_column_count and sqlite3_data_count could be different, or it might just add null values for the rest of the columns).

  • Column names might not be available in this mode, depending on how it is implemented.

  • The pzTail argument to sqlite3_prepare_v3 must be 0 (since this parameter isn't meaningful for multi-statement preparation).

  • Possibly, a new variant of the Halt and HaltIfNull opcodes might be needed, which will continue with the next statement if it is not an error. (It seems all of the parameters to HaltIfNull are already used, so it cannot include a implicit Goto as well. If Halt or HaltIfNull must still be used (in order to automatically close cursors, etc), the Goto must be added as the next instruction, I suppose. The compiler could look for these variant if it is not an error, and point them to the next statement, maybe.)

I don't know when or if it would be implemented, but it is an idea. But, maybe the author(s) of SQLite have a better idea.

(2) By anonymous on 2021-05-22 14:19:08 in reply to 1 [link] [source]

What exactly happens when you call sqlite3_step? Does it just step through the statements one-by-one? What does it return after each one? SQLITE_DONE? If so, how do you know when the last statement is done? If you get an error, how do you know which particular statement failed?

Given all the constraints you mentioned (and others), this doesn't seem like a very useful feature to me. Note that you can already effectively execute multiple SELECTs with the constraints you mentioned simply by joining them together with UNION ALL.

It kind of sounds like you'd want something more akin to sqlite3_exec, just with more bells and whistles to account for multiple statements.

(3) By anonymous on 2021-05-22 16:33:07 in reply to 2 [source]

It would only return SQLITE_DONE after all of the statements are done; after each statement is done it just continues with the next one (returning SQLITE_ROW once a new result row is available, executing multiple statements at once if there are no result rows). If there is an error, you don't know which statement failed (unless the caller provided SQL statements that return data to delimit the statements, so that the caller can tell).

Also, multiple SELECTs joined together with UNION ALL can only use a single ORDER BY clause, and won't work if you want to include statements other than SELECT as well.

(This new mode might sometimes be useful with prepared user-entered SQL codes with host parameters (especially when you want to call stepping them rather than using a callback function), to avoid having to manage the statements separately.)