1.Dd January 24, 2024 2.Dt SQLITE3_PREPARE 3 3.Os 4.Sh NAME 5.Nm sqlite3_prepare , 6.Nm sqlite3_prepare_v2 , 7.Nm sqlite3_prepare_v3 , 8.Nm sqlite3_prepare16 , 9.Nm sqlite3_prepare16_v2 , 10.Nm sqlite3_prepare16_v3 11.Nd compiling an SQL statement 12.Sh SYNOPSIS 13.In sqlite3.h 14.Ft int 15.Fo sqlite3_prepare 16.Fa "sqlite3 *db" 17.Fa "const char *zSql" 18.Fa "int nByte" 19.Fa "sqlite3_stmt **ppStmt" 20.Fa "const char **pzTail" 21.Fc 22.Ft int 23.Fo sqlite3_prepare_v2 24.Fa "sqlite3 *db" 25.Fa "const char *zSql" 26.Fa "int nByte" 27.Fa "sqlite3_stmt **ppStmt" 28.Fa "const char **pzTail" 29.Fc 30.Ft int 31.Fo sqlite3_prepare_v3 32.Fa "sqlite3 *db" 33.Fa "const char *zSql" 34.Fa "int nByte" 35.Fa "unsigned int prepFlags" 36.Fa "sqlite3_stmt **ppStmt" 37.Fa "const char **pzTail" 38.Fc 39.Ft int 40.Fo sqlite3_prepare16 41.Fa "sqlite3 *db" 42.Fa "const void *zSql" 43.Fa "int nByte" 44.Fa "sqlite3_stmt **ppStmt" 45.Fa "const void **pzTail" 46.Fc 47.Ft int 48.Fo sqlite3_prepare16_v2 49.Fa "sqlite3 *db" 50.Fa "const void *zSql" 51.Fa "int nByte" 52.Fa "sqlite3_stmt **ppStmt" 53.Fa "const void **pzTail" 54.Fc 55.Ft int 56.Fo sqlite3_prepare16_v3 57.Fa "sqlite3 *db" 58.Fa "const void *zSql" 59.Fa "int nByte" 60.Fa "unsigned int prepFlags" 61.Fa "sqlite3_stmt **ppStmt" 62.Fa "const void **pzTail" 63.Fc 64.Sh DESCRIPTION 65To execute an SQL statement, it must first be compiled into a byte-code 66program using one of these routines. 67Or, in other words, these routines are constructors for the prepared statement 68object. 69.Pp 70The preferred routine to use is 71.Fn sqlite3_prepare_v2 . 72The 73.Fn sqlite3_prepare 74interface is legacy and should be avoided. 75.Fn sqlite3_prepare_v3 76has an extra "prepFlags" option that is used for special purposes. 77.Pp 78The use of the UTF-8 interfaces is preferred, as SQLite currently does 79all parsing using UTF-8. 80The UTF-16 interfaces are provided as a convenience. 81The UTF-16 interfaces work by converting the input text into UTF-8, 82then invoking the corresponding UTF-8 interface. 83.Pp 84The first argument, "db", is a database connection 85obtained from a prior successful call to 86.Fn sqlite3_open , 87.Fn sqlite3_open_v2 88or 89.Fn sqlite3_open16 . 90The database connection must not have been closed. 91.Pp 92The second argument, "zSql", is the statement to be compiled, encoded 93as either UTF-8 or UTF-16. 94The sqlite3_prepare(), sqlite3_prepare_v2(), and sqlite3_prepare_v3() 95interfaces use UTF-8, and sqlite3_prepare16(), sqlite3_prepare16_v2(), 96and sqlite3_prepare16_v3() use UTF-16. 97.Pp 98If the nByte argument is negative, then zSql is read up to the first 99zero terminator. 100If nByte is positive, then it is the number of bytes read from zSql. 101If nByte is zero, then no prepared statement is generated. 102If the caller knows that the supplied string is nul-terminated, then 103there is a small performance advantage to passing an nByte parameter 104that is the number of bytes in the input string \fIincluding\fP the nul-terminator. 105.Pp 106If pzTail is not NULL then *pzTail is made to point to the first byte 107past the end of the first SQL statement in zSql. 108These routines only compile the first statement in zSql, so *pzTail 109is left pointing to what remains uncompiled. 110.Pp 111*ppStmt is left pointing to a compiled prepared statement 112that can be executed using 113.Fn sqlite3_step . 114If there is an error, *ppStmt is set to NULL. 115If the input text contains no SQL (if the input is an empty string 116or a comment) then *ppStmt is set to NULL. 117The calling procedure is responsible for deleting the compiled SQL 118statement using 119.Fn sqlite3_finalize 120after it has finished with it. 121ppStmt may not be NULL. 122.Pp 123On success, the sqlite3_prepare() family of routines return SQLITE_OK; 124otherwise an error code is returned. 125.Pp 126The sqlite3_prepare_v2(), sqlite3_prepare_v3(), sqlite3_prepare16_v2(), 127and sqlite3_prepare16_v3() interfaces are recommended for all new programs. 128The older interfaces (sqlite3_prepare() and sqlite3_prepare16()) are 129retained for backwards compatibility, but their use is discouraged. 130In the "vX" interfaces, the prepared statement that is returned (the 131sqlite3_stmt object) contains a copy of the original SQL 132text. 133This causes the 134.Fn sqlite3_step 135interface to behave differently in three ways: 136.Bl -enum 137.It 138If the database schema changes, instead of returning SQLITE_SCHEMA 139as it always used to do, 140.Fn sqlite3_step 141will automatically recompile the SQL statement and try to run it again. 142As many as SQLITE_MAX_SCHEMA_RETRY retries will 143occur before sqlite3_step() gives up and returns an error. 144.It 145When an error occurs, 146.Fn sqlite3_step 147will return one of the detailed error codes or extended error codes. 148The legacy behavior was that 149.Fn sqlite3_step 150would only return a generic SQLITE_ERROR result code and 151the application would have to make a second call to 152.Fn sqlite3_reset 153in order to find the underlying cause of the problem. 154With the "v2" prepare interfaces, the underlying reason for the error 155is returned immediately. 156.It 157If the specific value bound to a host parameter in the 158WHERE clause might influence the choice of query plan for a statement, 159then the statement will be automatically recompiled, as if there had 160been a schema change, on the first 161.Fn sqlite3_step 162call following any change to the bindings of that parameter. 163The specific value of a WHERE-clause parameter might influence 164the choice of query plan if the parameter is the left-hand side of 165a LIKE or GLOB operator or if the parameter is compared to 166an indexed column and the SQLITE_ENABLE_STAT4 compile-time 167option is enabled. 168.El 169.Pp 170.Pp 171sqlite3_prepare_v3() differs from sqlite3_prepare_v2() only in having 172the extra prepFlags parameter, which is a bit array consisting of zero 173or more of the SQLITE_PREPARE_* flags. 174The sqlite3_prepare_v2() interface works exactly the same as sqlite3_prepare_v3() 175with a zero prepFlags parameter. 176.Sh IMPLEMENTATION NOTES 177These declarations were extracted from the 178interface documentation at line 4176. 179.Bd -literal 180SQLITE_API int sqlite3_prepare( 181 sqlite3 *db, /* Database handle */ 182 const char *zSql, /* SQL statement, UTF-8 encoded */ 183 int nByte, /* Maximum length of zSql in bytes. */ 184 sqlite3_stmt **ppStmt, /* OUT: Statement handle */ 185 const char **pzTail /* OUT: Pointer to unused portion of zSql */ 186); 187SQLITE_API int sqlite3_prepare_v2( 188 sqlite3 *db, /* Database handle */ 189 const char *zSql, /* SQL statement, UTF-8 encoded */ 190 int nByte, /* Maximum length of zSql in bytes. */ 191 sqlite3_stmt **ppStmt, /* OUT: Statement handle */ 192 const char **pzTail /* OUT: Pointer to unused portion of zSql */ 193); 194SQLITE_API int sqlite3_prepare_v3( 195 sqlite3 *db, /* Database handle */ 196 const char *zSql, /* SQL statement, UTF-8 encoded */ 197 int nByte, /* Maximum length of zSql in bytes. */ 198 unsigned int prepFlags, /* Zero or more SQLITE_PREPARE_ flags */ 199 sqlite3_stmt **ppStmt, /* OUT: Statement handle */ 200 const char **pzTail /* OUT: Pointer to unused portion of zSql */ 201); 202SQLITE_API int sqlite3_prepare16( 203 sqlite3 *db, /* Database handle */ 204 const void *zSql, /* SQL statement, UTF-16 encoded */ 205 int nByte, /* Maximum length of zSql in bytes. */ 206 sqlite3_stmt **ppStmt, /* OUT: Statement handle */ 207 const void **pzTail /* OUT: Pointer to unused portion of zSql */ 208); 209SQLITE_API int sqlite3_prepare16_v2( 210 sqlite3 *db, /* Database handle */ 211 const void *zSql, /* SQL statement, UTF-16 encoded */ 212 int nByte, /* Maximum length of zSql in bytes. */ 213 sqlite3_stmt **ppStmt, /* OUT: Statement handle */ 214 const void **pzTail /* OUT: Pointer to unused portion of zSql */ 215); 216SQLITE_API int sqlite3_prepare16_v3( 217 sqlite3 *db, /* Database handle */ 218 const void *zSql, /* SQL statement, UTF-16 encoded */ 219 int nByte, /* Maximum length of zSql in bytes. */ 220 unsigned int prepFlags, /* Zero or more SQLITE_PREPARE_ flags */ 221 sqlite3_stmt **ppStmt, /* OUT: Statement handle */ 222 const void **pzTail /* OUT: Pointer to unused portion of zSql */ 223); 224.Ed 225.Sh SEE ALSO 226.Xr sqlite3 3 , 227.Xr sqlite3_bind_blob 3 , 228.Xr sqlite3_finalize 3 , 229.Xr sqlite3_open 3 , 230.Xr sqlite3_reset 3 , 231.Xr sqlite3_step 3 , 232.Xr sqlite3_stmt 3 , 233.Xr SQLITE_OK 3 , 234.Xr SQLITE_PREPARE_PERSISTENT 3 235