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.

要执行SQL查询，必须先使用这些例程之一将它编译成字节码。

 

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.

第1个参数，“db”是从之前成功调用sqlite3_open()、sqlite3_open_v2()或sqlite3_open16()所获取的数据库连接。数据库连接一定不能已经关闭。

 

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.

第2个参数，“zSql”，是要被编译的语句，UTF-8或UTF-16编码。sqlite3_prepare()和sqlite3_prepare_v2()接口使用UTF-8，而sqlite3_prepare16()和sqlite3_prepare16_v2()使用UTF-16。

 

If the nByte argument is less than zero, then zSql is read up to the first zero terminator. If nByte is non-negative, then it is the maximum number of bytes read from zSql. When nByte is non-negative, the zSql string ends at either the first '\000' or '\u0000' character or the nByte-th byte, whichever comes first. If the caller knows that the supplied string is nul-terminated, then there is a small performance advantage to be gained by passing an nByte parameter that is equal to the number of bytes in the input string including the nul-terminator bytes.

如果nByte参数小于0，则读取zSql直到第一个0终结符。如果nByte非负，那么这就是从zSql读取的最大字节数。当nByte非负时，zSql字符串以第一个“\000”、“\u0000”或第nByte个字节结束，无论哪个先出现。如果调用者知道所提供的字符串是以空结尾，则给nByte参数传递包括空结束符在内的输入字符串的字节数可以提高一点性能。

 

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.

如果pzTail不为NULL，则让*pzTail指向zSql中第一个SQL语句之后的第一个字节。这些例程只编译zSql中的第一条语句，因此*pzSql指向未编译的部分。

 

*ppStmt is left pointing to a compiled prepared statement that can be executed using sqlite3_step(). If there is an error, *ppStmt is set to NULL. 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.

让*ppStmt指向能用sqlite3_step()执行的编译了的预编译语句。如果有错误发生，则*ppStmt被设置为NULL。如果输入文本没有SQL（空串或注释）则*ppStmt被设置为NULL。主调过程负责在使用完后使用sqlite3_finalize()删除已编译的SQL语句。ppStmt可能不是NULL。

 

On success, SQLITE_OK is returned, otherwise an error code is returned.

成功时，返回SQLITE_OK，否则返回错误码。

 

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. 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 a differently in two ways:

1. 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. If the schema has changed in a way that makes the statement no longer valid, sqlite3_step() will still return SQLITE_SCHEMA. But unlike the legacy behavior, SQLITE_SCHEMA is now a fatal error. Calling sqlite3_prepare_v2() again will not make the error go away. Note: use sqlite3_errmsg() to find the text of the parsing error that results in an SQLITE_SCHEMA return.
2. When an error occurs, sqlite3_step() will return one of the detailed error codes or extended error codes. The legacy behavior was that sqlite3_step() would only return a generic SQLITE_ERROR result 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 immediately.

推荐所有的新程序使用sqlite3_prepare_v2()和sqlite3_prepare16_v2()接口。两个旧接口为了向后兼容而保留，但是它们的使用被阻止了。在“v2”接口中，所返回的预编译语句（sqlite3_stmt对象）包含了一份原始SQL文本拷贝。这使得sqlite3_step()接口在两个方面行为不同：

1. 如果数据库模式改变了，不会像以前那样返回SQLITE_SCHEMA，sqlite3_step()将自动重编译SQL语句并试着再次运行。如果模式的改变使得语句不再有效，则sqlite3_step()将仍然返回SQLITE_SCHEMA。但是和原来的行为不同，SQLITE_SCHEMA现在是重大错误。再次调用sqlite3_prepare_v2()将不会消除错误。注意：使用sqlite3_errmsg()获得导致返回SQLITE_SCHEMA的解析错误文本。
2. 当错误发生时，sqlite3_step()将返回一个详细的错误码或扩展错误码。原来的行为是sqlite3_step()只返回通用的SQLITE_ERROR结果码，你将不得不再次调用sqlite3_reset()得到问题的内在原因。使用“v2”预编译接口，错误的内在原因会立即返回。