Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Update the documentation to talk about the new sqlite3_prepare_v2() API. (CVS 3507) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
d9e14b6121a7b6786cccafd6e1e83474 |
User & Date: | drh 2006-11-09 15:18:00.000 |
Context
2006-11-11
| ||
01:31 | The uninitialized file descriptor from the unixFile structure is passed to sqlite3DetectLockingStyle in allocateUnixFile rather than the file descriptor passed in. This was causing the locking detection on NFS file systems to behave somewhat randomly and the result was locks were not respected and data loss could occur. (CVS 3508) (check-in: b9dd16ef3d user: aswift tags: trunk) | |
2006-11-09
| ||
15:18 | Update the documentation to talk about the new sqlite3_prepare_v2() API. (CVS 3507) (check-in: d9e14b6121 user: drh tags: trunk) | |
00:24 | First cut at adding the sqlite3_prepare_v2() API. Test cases added, but more testing would be useful. Still need to update the documentation. (CVS 3506) (check-in: f1efae9224 user: drh tags: trunk) | |
Changes
Changes to www/capi3ref.tcl.
|
| | | 1 2 3 4 5 6 7 8 | set rcsid {$Id: capi3ref.tcl,v 1.46 2006/11/09 15:18:00 drh Exp $} source common.tcl header {C/C++ Interface For SQLite Version 3} puts { <h2>C/C++ Interface For SQLite Version 3</h2> } proc api {name prototype desc {notused x}} { |
︙ | ︙ | |||
153 154 155 156 157 158 159 | int sqlite3_bind_int64(sqlite3_stmt*, int, long long int); int sqlite3_bind_null(sqlite3_stmt*, int); int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*)); int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, void(*)(void*)); #define SQLITE_STATIC ((void(*)(void *))0) #define SQLITE_TRANSIENT ((void(*)(void *))-1) } { | | | | 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 | int sqlite3_bind_int64(sqlite3_stmt*, int, long long int); int sqlite3_bind_null(sqlite3_stmt*, int); int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*)); int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, void(*)(void*)); #define SQLITE_STATIC ((void(*)(void *))0) #define SQLITE_TRANSIENT ((void(*)(void *))-1) } { In the SQL strings input to sqlite3_prepare_v2() and sqlite3_prepare16_v2(), one or more literals can be replace by a parameter "?" or ":AAA" or "@AAA" or "\$VVV" where AAA is an alphanumeric identifier and VVV is a variable name according to the syntax rules of the TCL programming language. The values of these parameters (also called "host parameter names") can be set using the sqlite3_bind_*() routines. The first argument to the sqlite3_bind_*() routines always is a pointer to the sqlite3_stmt structure returned from sqlite3_prepare_v2(). The second argument is the index of the parameter to be set. The first parameter has an index of 1. When the same named parameter is used more than once, second and subsequent occurrences have the same index as the first occurrence. The index for named parameters can be looked up using the sqlite3_bind_parameter_name() API if desired. |
︙ | ︙ | |||
190 191 192 193 194 195 196 | special value SQLITE_STATIC, then the library assumes that the information is in static, unmanaged space and does not need to be freed. If the fifth argument has the value SQLITE_TRANSIENT, then SQLite makes its own private copy of the data immediately, before the sqlite3_bind_*() routine returns. The sqlite3_bind_*() routines must be called after | | | 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 | special value SQLITE_STATIC, then the library assumes that the information is in static, unmanaged space and does not need to be freed. If the fifth argument has the value SQLITE_TRANSIENT, then SQLite makes its own private copy of the data immediately, before the sqlite3_bind_*() routine returns. The sqlite3_bind_*() routines must be called after sqlite3_prepare_v2() or sqlite3_reset() and before sqlite3_step(). Bindings are not cleared by the sqlite3_reset() routine. Unbound parameters are interpreted as NULL. These routines return SQLITE_OK on success or an error code if anything goes wrong. SQLITE_RANGE is returned if the parameter index is out of range. SQLITE_NOMEM is returned if malloc fails. SQLITE_MISUSE is returned if these routines are called on a virtual |
︙ | ︙ | |||
377 378 379 380 381 382 383 | #define SQLITE_TEXT 3 #define SQLITE_BLOB 4 #define SQLITE_NULL 5 } { These routines return information about the information in a single column of the current result row of a query. In every case the first argument is a pointer to the SQL statement that is being | | | 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 | #define SQLITE_TEXT 3 #define SQLITE_BLOB 4 #define SQLITE_NULL 5 } { These routines return information about the information in a single column of the current result row of a query. In every case the first argument is a pointer to the SQL statement that is being executed (the sqlite_stmt* that was returned from sqlite3_prepare_v2()) and the second argument is the index of the column for which information should be returned. iCol is zero-indexed. The left-most column has an index of 0. If the SQL statement is not currently point to a valid row, or if the the column index is out of range, the result is undefined. |
︙ | ︙ | |||
890 891 892 893 894 895 896 | and sqlite3_busy_timeout() functions.) } {} api {} { int sqlite3_finalize(sqlite3_stmt *pStmt); } { The sqlite3_finalize() function is called to delete a prepared | | > | | 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 | and sqlite3_busy_timeout() functions.) } {} api {} { int sqlite3_finalize(sqlite3_stmt *pStmt); } { The sqlite3_finalize() function is called to delete a prepared SQL statement obtained by a previous call to sqlite3_prepare(), sqlite3_prepare_v2(), sqlite3_prepare16(), or sqlite3_prepare16_v2(). If the statement was executed successfully, or not executed at all, then SQLITE_OK is returned. If execution of the statement failed then an error code is returned. All prepared statements must finalized before sqlite3_close() is called or else the close will fail with a return code of SQLITE_BUSY. This routine can be called at any point during the execution of the |
︙ | ︙ | |||
1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 | Note to windows users: The encoding used for the filename argument of sqlite3_open() must be UTF-8, not whatever codepage is currently defined. Filenames containing international characters must be converted to UTF-8 prior to passing them into sqlite3_open(). } api {} { int sqlite3_prepare( sqlite3 *db, /* Database handle */ const char *zSql, /* SQL statement, UTF-8 encoded */ int nBytes, /* 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 nBytes, /* 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 | > > > > > > > > > > > > > > | < < < > > | > > > > > > > > > > > > > > > > > | 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 | Note to windows users: The encoding used for the filename argument of sqlite3_open() must be UTF-8, not whatever codepage is currently defined. Filenames containing international characters must be converted to UTF-8 prior to passing them into sqlite3_open(). } api {} { int sqlite3_prepare_v2( sqlite3 *db, /* Database handle */ const char *zSql, /* SQL statement, UTF-8 encoded */ int nBytes, /* Length of zSql in bytes. */ sqlite3_stmt **ppStmt, /* OUT: Statement handle */ const char **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 nBytes, /* Length of zSql in bytes. */ sqlite3_stmt **ppStmt, /* OUT: Statement handle */ const void **pzTail /* OUT: Pointer to unused portion of zSql */ ); int sqlite3_prepare( sqlite3 *db, /* Database handle */ const char *zSql, /* SQL statement, UTF-8 encoded */ int nBytes, /* 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 nBytes, /* 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 an SQLite database handle. 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 uses UTF-8 and sqlite3_prepare16() and sqlite3_prepare16_v2() use UTF-16. If the next argument, "nBytes", is less than zero, then zSql is read up to the first nul terminator. If "nBytes" is not less than zero, then it is the length of the string zSql in bytes (not characters). *pzTail is made to point to the first byte past the end of the first SQL statement in zSql. This routine only compiles the first statement in zSql, so *pzTail is left pointing to what remains uncompiled. *ppStmt is left pointing to a compiled SQL statement that can be executed using sqlite3_step(). Or if there is an error, *ppStmt may be set to NULL. If the input text contained no SQL (if the input is and empty string or a comment) then *ppStmt is set to NULL. The calling procedure is responsible for deleting this compiled SQL statement using sqlite3_finalize() after it has finished with it. On success, SQLITE_OK is returned. Otherwise an error code is returned. The sqlite3_prepare_v2() and sqlite3_prepare16_v2() interfaces are recommended for all new programs. The other two interfaces are retained for backwards compatibility. In the "v2" interfaces, the prepared statement that is returned (the sqlite3_stmt object) contains a copy of the original SQL. This causes the sqlite3_step() interface to behave a little differently. 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. Only after 5 consecutive failures will an SQLITE_SCHEMA failure be reported back. The other change is that sqlite3_step() will return one of the detailed result-codes like SQLITE_IOERR or SQLITE_FULL or SQLITE_SCHEMA directly. The legacy behavior was that sqlite3_step() would only return a generic SQLITE_ERROR code and you 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 directly. } api {} { void sqlite3_progress_handler(sqlite3*, int, int(*)(void*), void*); } { <i>Experimental</i> |
︙ | ︙ | |||
1196 1197 1198 1199 1200 1201 1202 | } api {} { int sqlite3_reset(sqlite3_stmt *pStmt); } { The sqlite3_reset() function is called to reset a prepared SQL | | > | | 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 | } api {} { int sqlite3_reset(sqlite3_stmt *pStmt); } { The sqlite3_reset() function is called to reset a prepared SQL statement obtained by a previous call to sqlite3_prepare(), sqlite3_prepare_v2(), sqlite3_prepare16() or sqlite3_prepare16_v2() back to it's initial state, ready to be re-executed. Any SQL statement variables that had values bound to them using the sqlite3_bind_*() API retain their values. } api {} { void sqlite3_result_blob(sqlite3_context*, const void*, int n, void(*)(void*)); void sqlite3_result_double(sqlite3_context*, double); |
︙ | ︙ | |||
1267 1268 1269 1270 1271 1272 1273 | #define SQLITE_DROP_VTABLE 30 /* Table Name Module Name */ #define SQLITE_FUNCTION 31 /* Function Name NULL */ #define SQLITE_DENY 1 /* Abort the SQL statement with an error */ #define SQLITE_IGNORE 2 /* Don't allow access, but don't generate an error */ } { This routine registers a callback with the SQLite library. The | | | 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 | #define SQLITE_DROP_VTABLE 30 /* Table Name Module Name */ #define SQLITE_FUNCTION 31 /* Function Name NULL */ #define SQLITE_DENY 1 /* Abort the SQL statement with an error */ #define SQLITE_IGNORE 2 /* Don't allow access, but don't generate an error */ } { This routine registers a callback with the SQLite library. The callback is invoked by sqlite3_prepare_v2() to authorize various operations against the database. The callback should return SQLITE_OK if access is allowed, SQLITE_DENY if the entire SQL statement should be aborted with an error and SQLITE_IGNORE if the operation should be treated as a no-op. Each database connection have at most one authorizer registered at a time one time. Each call |
︙ | ︙ | |||
1298 1299 1300 1301 1302 1303 1304 | is the name of the inner-most trigger or view that is responsible for the access attempt or NULL if this access attempt is directly from input SQL code. The return value of the authorization function should be one of the constants SQLITE_OK, SQLITE_DENY, or SQLITE_IGNORE. A return of SQLITE_OK means that the operation is permitted and that | | | | > | > > > > > > > | | > > | 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 | is the name of the inner-most trigger or view that is responsible for the access attempt or NULL if this access attempt is directly from input SQL code. The return value of the authorization function should be one of the constants SQLITE_OK, SQLITE_DENY, or SQLITE_IGNORE. A return of SQLITE_OK means that the operation is permitted and that sqlite3_prepare_v2() can proceed as normal. A return of SQLITE_DENY means that the sqlite3_prepare_v2() should fail with an error. A return of SQLITE_IGNORE causes the sqlite3_prepare_v2() to continue as normal but the requested operation is silently converted into a no-op. A return of SQLITE_IGNORE in response to an SQLITE_READ or SQLITE_FUNCTION causes the column being read or the function being invoked to return a NULL. The intent of this routine is to allow applications to safely execute user-entered SQL. An appropriate callback can deny the user-entered SQL access certain operations (ex: anything that changes the database) or to deny access to certain tables or columns within the database. } api {} { int sqlite3_step(sqlite3_stmt*); } { After an SQL query has been prepared with a call to either sqlite3_prepare(), sqlite3_prepare_v2(), sqlite3_prepare16(), or sqlite3_prepare16_v2(), then this function must be called one or more times to execute the statement. The details of the behavior of this sqlite3_step() interface depend on whether the statement was prepared using the newer "v2" interface sqlite3_prepare_v2() and sqlite3_prepare16_v2() or the older legacy interface sqlite3_prepare() and sqlite3_prepare16(). The use of the new "v2" interface is recommended for new applications but the legacy interface will continue to be supported. In the lagacy 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 SQLite result-codes might be returned as well. SQLITE_BUSY means that the database engine attempted to open a locked database and there is no busy callback registered. Call sqlite3_step() again to retry the open. SQLITE_DONE means that the statement has finished executing successfully. sqlite3_step() should not be called again on this virtual |
︙ | ︙ | |||
1342 1343 1344 1345 1346 1347 1348 | is called again to retrieve the next row of data. SQLITE_ERROR means that a run-time error (such as a constraint violation) has occurred. sqlite3_step() should not be called again on the VM. More information may be found by calling sqlite3_errmsg(). A more specific error code (example: SQLITE_INTERRUPT, SQLITE_SCHEMA, SQLITE_CORRUPT, and so forth) can be obtained by calling | | > > | | | < < | < < | < < | < < < | < < < < < < | < < < < | | 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 | is called again to retrieve the next row of data. SQLITE_ERROR means that a run-time error (such as a constraint violation) has occurred. sqlite3_step() should not be called again on the VM. More information may be found by calling sqlite3_errmsg(). A more specific error code (example: SQLITE_INTERRUPT, SQLITE_SCHEMA, SQLITE_CORRUPT, and so forth) can be obtained by calling sqlite3_reset() on the prepared statement. In the "v2" interface, the more specific error code is returned directly by sqlite3_step(). SQLITE_MISUSE means that the this routine was called inappropriately. Perhaps it was called on a virtual machine that had already been finalized or on one that had previously returned SQLITE_ERROR or SQLITE_DONE. Or it could be the case that a database connection is being used by a different thread than the one it was created it. <b>Goofy Interface Alert:</b> In the legacy interface, the sqlite3_step() API always returns a generic error code, SQLITE_ERROR, following any error other than SQLITE_BUSY and SQLITE_MISUSE. You must call sqlite3_reset() (or sqlite3_finalize()) in order to find one of the specific result-codes that better describes the error. We admit that this is a goofy design. The problem has been fixed with the "v2" interface. If you prepare all of your SQL statements using either sqlite3_prepare_v2() or sqlite3_prepare16_v2() instead of the legacy sqlite3_prepare() and sqlite3_prepare16(), then the more specific result-codes are returned directly by sqlite3_step(). The use of the "v2" interface is recommended. } api {} { void *sqlite3_trace(sqlite3*, void(*xTrace)(void*,const char*), void*); } { Register a function that is called each time an SQL statement is evaluated. The callback function is invoked on the first call to sqlite3_step() after calls to sqlite3_prepare_v2() or sqlite3_reset(). This function can be used (for example) to generate a log file of all SQL executed against a database. This can be useful when debugging an application that uses SQLite. } api {} { void *sqlite3_user_data(sqlite3_context*); |
︙ | ︙ | |||
1566 1567 1568 1569 1570 1571 1572 | This routine must not be called when any database connections are active in the current thread. Enabling or disabling shared cache while there are active database connections will result in memory corruption. When the shared cache is enabled, the following routines must always be called from the same thread: | | | 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 | This routine must not be called when any database connections are active in the current thread. Enabling or disabling shared cache while there are active database connections will result in memory corruption. When the shared cache is enabled, the following routines must always be called from the same thread: sqlite3_open(), sqlite3_prepare_v2(), sqlite3_step(), sqlite3_reset(), sqlite3_finalize(), and sqlite3_close(). This is due to the fact that the shared cache makes use of thread-specific storage so that it will be available for sharing with other connections. This routine returns SQLITE_OK if shared cache was enabled or disabled successfully. An error code is returned |
︙ | ︙ |