Small. Fast. Reliable.
Choose any three.
Search for:

SQLite C Interface

Compiling An SQL Statement

int sqlite3_prepare(
  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 */
);
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 */
);
int sqlite3_prepare16(
  sqlite3 *db,            /* Database handle */
  const void *zSql,       /* SQL statement, UTF-16 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const void **pzTail     /* OUT: Pointer to unused portion of zSql */
);
int sqlite3_prepare16_v2(
  sqlite3 *db,            /* Database handle */
  const void *zSql,       /* SQL statement, UTF-16 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const void **pzTail     /* OUT: Pointer to unused portion of zSql */
);

To execute an SQL query, it must first be compiled into a byte-code program using one of these routines.

The first argument, "db", is a database connection obtained from a prior successful call to sqlite3_open(), sqlite3_open_v2() or sqlite3_open16(). The database connection must not have been closed.

The second argument, "zSql", is the statement to be compiled, encoded as either UTF-8 or UTF-16. The sqlite3_prepare() and sqlite3_prepare_v2() interfaces use UTF-8, and sqlite3_prepare16() and sqlite3_prepare16_v2() use UTF-16.

R-58838-55911:[If the nByte argument is negative, then zSql is read up to the first zero terminator. ] R-28279-44669:[If nByte is positive, then it is the number of bytes read from zSql. ] R-48563-15053:[If nByte is zero, then no prepared statement is generated. ] If the caller knows that the supplied string is nul-terminated, then there is a small performance advantage to passing an nByte parameter that is the number of bytes in the input string including the nul-terminator.

R-24543-02373:[If pzTail is not NULL then *pzTail is made to point to the first byte past the end of the first SQL statement in zSql. ] These routines only compile the first statement in zSql, so *pzTail is left pointing to what remains uncompiled.

R-44830-52899:[*ppStmt is left pointing to a compiled prepared statement that can be executed using sqlite3_step(). ] R-11127-09633:[If there is an error, *ppStmt is set to NULL. ] R-03880-38961:[If the input text contains no SQL (if the input is an empty string or a comment) then *ppStmt is set to NULL. ] The calling procedure is responsible for deleting the compiled SQL statement using sqlite3_finalize() after it has finished with it. ppStmt may not be NULL.

R-60355-64447:[On success, the sqlite3_prepare() family of routines return SQLITE_OK; otherwise an error code is returned. ]

The sqlite3_prepare_v2() and sqlite3_prepare16_v2() interfaces are recommended for all new programs. The two older interfaces are retained for backwards compatibility, but their use is discouraged. R-60254-42800:[In the "v2" interfaces, the prepared statement that is returned (the sqlite3_stmt object) contains a copy of the original SQL text. ] This causes the sqlite3_step() interface to behave differently in three ways:

  1. R-43237-50879:[If the database schema changes, instead of returning SQLITE_SCHEMA as it always used to do, sqlite3_step() will automatically recompile the SQL statement and try to run it again. ] As many as SQLITE_MAX_SCHEMA_RETRY retries will occur before sqlite3_step() gives up and returns an error.

  2. R-46616-26780:[When an error occurs, sqlite3_step() will return one of the detailed error codes or extended error codes. ] R-30964-04756:[The legacy behavior was that sqlite3_step() would only return a generic SQLITE_ERROR result code and the application would have to make a second call to sqlite3_reset() in order to find the underlying cause of the problem. ] With the "v2" prepare interfaces, the underlying reason for the error is returned immediately.

  3. R-48440-37595:[If the specific value bound to host parameter in the WHERE clause might influence the choice of query plan for a statement, then the statement will be automatically recompiled, as if there had been a schema change, on the first sqlite3_step() call following any change to the bindings of that parameter. ] R-31526-56213:[The specific value of WHERE-clause parameter might influence the choice of query plan if the parameter is the left-hand side of a LIKE or GLOB operator or if the parameter is compared to an indexed column and the SQLITE_ENABLE_STAT3 compile-time option is enabled. ]

See also lists of Objects, Constants, and Functions.