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”先行編譯介面，錯誤的內在原因會立即返回。