SQLite Forum

sqlite3_prepare_v2 & pzTail
Login

sqlite3_prepare_v2 & pzTail

(1) By anonymous on 2020-12-19 22:43:32 [link] [source]

int sqlite3_prepare_v2(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);

Pointer to unused portion of zSql

I expected this to mean everything except the 'current' statement from a multi-query statement with each query being terminated by semi-colon. 'current' being everything up to the first semi-colon.

My experience suggests that that is not the case.

This means that I cannot proceed with the next statement when sqlite3_step returns a non-zero value.

Is this by design or a bug?

(2.1) By Keith Medcalf (kmedcalf) on 2020-12-20 07:39:01 edited from 2.0 in reply to 1 [link] [source]

This is precisely what the documentation says.

pzTail points to the byte following the one at which the "parser" stopped parsing the input text pointed to by zSql. So if sqlite3_prepare_v2 returned an error, then pzTail will point to the location immediately following where the parser stopped parsing due to the error -- that is, the "unused portion of zSql".

Only in the event that sqlite_prepare_v2 returns SQLITE_OK is there the implication that pzTail points to "the next statement", if there is one.

The parser is like you reading a sentence.

It does not parse it as a whole out of a paragraph, but one character at a time from the beginning.

If you hit a character that completes a word that is beyond your ken you immediately go "what does that mean" and go look it up in a lexicon of the English language. You have not yet reached the end of the sentence or the paragraph and you leave a "pointer" to where you were so that you can carry on after getting off the bus coming back from the library (the nearest lexicon of the English language is located in the Public Library which is a bus ride away from your house, you see).

This "pointer" is the parameter pzTail.

On the gripping hand, if you reached the end of a sentence such as "Put the kettle on for tea." and you execute that sentence before continuing, then your "pointer" will naturally point at the beginning of the next sentence when you are done putting the kettle on and return to where you left off.

(3) By anonymous on 2020-12-20 10:21:12 in reply to 2.1 [link] [source]

but one character at a time from the beginning

I think it is one word at a time.

So if sqlite3_prepare_v2 returned an error, then pzTail will point to the location immediately following where the parser stopped parsing due to the error -- that is, the "unused portion of zSql".

Understood.

That also means that if sqlite3_step returns a value other than SQLITE_OK the only option is to abort the prepare/step/finalize loop if controlled by pzTail.

(4) By Stephan Beal (stephan) on 2020-12-20 11:43:10 in reply to 3 [source]

That also means that if sqlite3_step returns a value other than SQLITE_OK the only option is to abort the prepare/step/finalize loop if controlled by pzTail.

sqlite3_step() never returns SQLITE_OK:

https://sqlite.org/c3ref/step.html

In the legacy interface, the return value will be either SQLITE_BUSY, SQLITE_DONE, SQLITE_ROW, SQLITE_ERROR, or SQLITE_MISUSE. With the "v2" interface, any of the other result codes or extended result codes might be returned as well.

In short, for your case, if it returns any value other than SQLITE_BUSY, SQLITE_DONE, or SQLITE_ROW, something went wrong and your only viable option is to stop looping. In the case of SQLITE_BUSY, you'll need to handle it as described in the above docs.

(5) By Keith Medcalf (kmedcalf) on 2020-12-20 17:59:24 in reply to 4 [link] [source]

sqlite3_step cannot return an error, because you cannot get there.

sqlite3_prepare_v2 returned an error indicating that it could not compile the passed SQL to a VDBE program.

So why are you ignoring the error return from sqlite3_prepare_v2 and proceeding to try to step that which does not exist?

However, if sqlite3_prepare_v2 did return SQLITE_OK and the sqlite3_step produced an error, then depending on what that error is there is nothing which says that the pzTail does not still point to what it pointed at before the error was returned -- ie, the next statement.

How you handle catastrophic errors is your business. You are, after all, the one in charge.

(6) By Keith Medcalf (kmedcalf) on 2020-12-20 18:03:06 in reply to 1 [link] [source]

Why are you calling sqlite3_step when sqlite3_prepare_v2 returned an error? You are mixing your fish and crocodiles. When talking about fish, stick to the fish. When talking about crocodiles, stick to crocodiles.

That is to say that your preposition has absolutely nothing to do with your assertion -- it is just nonsense.