In the previous two sections, we used the sqlite3_exec() function to execute SQL statements. This function is more appropriate for SQL statements that do not return data (such as INSERT,DROP, and CREATE). For SQL statements that return data, such as SELECT, prepared statements are usually used.
The use of prepared statements involves three phases:
Preparation.
In the preparation phase, you present a statement for the SQLite engine
for compilation. The engine compiles this statement into byte code and
reserves the resources needed for its actual execution.
Execution.
This phase is used to actually execute the byte code and obtain rows
from the result of the statement. You repeat this phase for every row
in the result set.
Finalization.
After obtaining all rows in the result set, you finalize the prepared
statement so that resources reserved for it can be freed.
In the following sections, we discuss these three phases in detail.
1. Preparation
You prepare an SQL statement using sqlite3_prepare_v2() function. The function is declared as follows:
int sqlite3_prepare_v2(
sqlite3 *db, /* Database handle */
const char* zSql, /* SQL statement, UTF-8 encoded */s
int nBytes, /* Length of zSql in bytes. */
sqlite3_stmt **ppStmt, /* OUT: Statement handle */
const char **pzTail /*OUT: Ptr to unused portion of zSql*/
)
The first parameter, db, is the pointer to the database handle obtained from a prior sqlite3_open() call. The SQL statement (e.g., SELECT statement) is passed in the zSql
parameter. You pass the length (in bytes) of that statement in the
third parameter. The fourth parameter is used to obtain a statement
handle. You pass a reference to a variable of type sqlite3_stmt*, and on successful preparation of the SQL statement, that variable will hold the statement handle. In the case that *zSql points to multiple SQL statements, the function will make *pzTail point to the first byte past the first SQL statement in zSql. If *zSql points to a single SQL statement, passing a NULL for the fifth parameter is appropriate.
2. Execution
Once you have compiled the SQL statement, you need
to execute it and retrieve the first row result. The SQL statement is
executed using the function sqlite3_step(). The declaration of the function is as follows:
int sqlite3_step(sqlite3_stmt*);
The function takes a pointer to the statement handle
as its sole parameter. As long as there is a new row in the result set,
the function returns SQLITE_ROW. When all rows have been exhausted, the function returns SQLITE_DONE.
3. Finalization
After retrieving the last row, the statement is finalized by calling sqlite3_finalize(). The function's declaration is as follows:
int sqlite3_finalize(sqlite3_stmt *pStmt);
It takes as the sole parameter a pointer to the statement handle. Finalization closes the statement and frees resources.
4. Putting it together
Let's demonstrate these concepts by showing a small working example. The function main() in Listing 1 is where we open a database, select some records from a table, and print them one by one.
Example 1. The function main() demonstrating prepared statements.
#import "/usr/include/sqlite3.h"
int main(int argc, char* argv[]) {
char *sqlStatement;
sqlite3 *database;
int returnCode;
char *databaseName;
sqlite3_stmt *statement;
databaseName = "financial.db";
returnCode = sqlite3_open(databaseName, &database);
if(returnCode!=SQLITE_OK) {
fprintf(stderr, "Error in opening the database. Error: %s",
sqlite3_errmsg(database));
sqlite3_close(database);
return −1;
}
sqlStatement = sqlite3_mprintf(
"SELECT S.symbol, S.unitsPurchased, "
"S.purchasePrice FROM stocks AS S WHERE "
"S.purchasePrice >= %f", 30.0);
returnCode =
sqlite3_prepare_v2(database,
sqlStatement, strlen(sqlStatement),
&statement, NULL);
if(returnCode != SQLITE_OK) {
fprintf(stderr, "Error in preparation of query. Error: %s",
sqlite3_errmsg(database));
sqlite3_close(database);
return −1;
}
returnCode = sqlite3_step(statement);
while(returnCode == SQLITE_ROW){
char* symbol;
int units;
double price;
symbol = sqlite3_column_text(statement, 0);
units = sqlite3_column_int(statement, 1);
price = sqlite3_column_double(statement, 2);
printf("We bought %d from %s at a price equal to %.4f\n",
units, symbol, price);
returnCode = sqlite3_step(statement);
}
sqlite3_finalize(statement);
sqlite3_free(sqlStatement);
return 0;
}
|
After opening the database, we invoke the sqlite3_prepare_v2() function on the following SQL statement:
SELECT
S.symbol, S.unitsPurchased, S.purchasePrice
FROM stocks AS S
WHERE S.purchasePrice >= 30.0
The SQL statement will result in a set of records from the table stocks whose purchasePrice is greater than or equal to $30. The statement is compiled as follows:
returnCode = sqlite3_prepare_v2(database,
sqlStatement, strlen(sqlStatement),
&statement, NULL);
Notice that we pass NULL for the last
parameter as we only have one SQL statement to compile. If the
statement compilation is successful, the return code will be equal to SQLITE_OK. If there is an error, we display the error message and exit the main() function.
After compiling the statement, we execute the
statement to retrieve the first result record. The function used in the
execution of the statement is sqlite3_step(). If there is a successful retrieval of a row, the return code will be SQLITE_ROW. If we receive an SQLITE_ROW
return code, we retrieve the values for the columns in that row. To
retrieve a column value, you use an SQLite function of the form sqlite3_column_XXX(). The first parameter to this function is a pointer to the SQL statement (type sqlite3_stmt) that was returned by the sqlite3_prepare_v2()
function. The second parameter is the column index, where the left-most
column has an index of 0. The return value depends on the version of
the function.
We have the following three statements corresponding to the three columns:
symbol = sqlite3_column_text(statement, 0);
units = sqlite3_column_int(statement, 1);
price = sqlite3_column_double(statement, 2);
The first statement corresponds to the S.symbol column. The column belongs to the TEXT storage class. The function sqlite3_column_text() will return a C-string of the symbol column that is stored in that row. The other functions, sqlite3_column_int() and sqlite3_column_double(), work in the same way except that they return an integer and a double value, respectively.
After printing the values for the columns constituting the row, we move to the next row in the result by again invoking the sqlite3_step() function. When we are finished with the result, we exit the while-loop and finalize the statement by invoking the sqlite3_finalize() function. The result of running this query, provided that the stocks table was populated as in the previous sections, is as follows:
We bought 20 from GOOG at a price equal to 600.7700
We bought 5 from MSFT at a price equal to 30.2300
