Unfortunatelly, the documentation completely lacks examples (what is really strange), as if it assumes all its readers to be good programmers. Whereas, I'm quite new to C++
and can not really figure out from the documentation how to really prepare and execute statements. I love the way how it is implemented in PDO
for PHP
. Normally, I just do it like this:
$s = $db->prepare("SELECT id FROM mytable WHERE id = :id");
$s->bindParam(':id', $id);
$s->execute();
or do it using ?
tokens:
$data = array();
$data[] = 1;
$data[] = 2;
$s = $db->prepare("SELECT id FROM mytable WHERE id = ? or id = ?");
$s->execute($data);
Now, I have C++
and sqlite3.h
in my hands. At this moment, I know just how to connect to database - I do it and get no errors:
sqlite3 * conn;
int rc = sqlite3_open(db_name, &conn);
Please, give some instructions (with clear tiny examples) about how to implement similar things that PDO
does in PHP
- prepare a statement using named arguments and using ?
tokens.
Bind parameters—also called dynamic parameters or bind variables—are an alternative way to pass data to the database. Instead of putting the values directly into the SQL statement, you just use a placeholder like ? , :name or @name and provide the actual values using a separate API call.
Prepared statements reduce parsing time as the preparation on the query is done only once (although the statement is executed multiple times) Bound parameters minimize bandwidth to the server as you need send only the parameters each time, and not the whole query.
The setXXX() methods bind values to the parameters, where XXX represents the Java data type of the value you wish to bind to the input parameter.
Parameterized SQL queries allow you to place parameters in an SQL query instead of a constant value. A parameter takes a value only when the query is executed, which allows the query to be reused with different values and for different purposes.
You can find a decent amount of documentation here: sqlite.org
This example does not explain the sqlite3
function calls and parameters in detail, as that is quite a lot of information to cover - instead refer to the given link for more in-depth detail.
This example binds values to the statement in your question multiple times, and reads all query results after each bind:
sqlite3* conn;
sqlite3_stmt* stmt = 0;
int rc = sqlite3_open(db_name, &conn);
// Good idea to always check the return value of sqlite3 function calls.
// Only done once in this example:
if ( rc != SQLITE_OK ) { // Do something }
rc = sqlite3_prepare_v2( conn, "SELECT id FROM myTable WHERE id = ? or id = ?", -1, &stmt, 0 );
// Optional, but will most likely increase performance.
rc = sqlite3_exec( conn, "BEGIN TRANSACTION", 0, 0, 0 );
for ( int bindIndex = 0; bindIndex < number_of_times_you_wish_to_bind; bindIndex++ ) {
// Binding integer values in this example.
// Bind functions for other data-types are available - see end of post.
// Bind-parameter indexing is 1-based.
rc = sqlite3_bind_int( stmt, 1, int_you_wish_to_bind ); // Bind first parameter.
rc = sqlite3_bind_int( stmt, 2, int_you_wish_to_bind ); // Bind second parameter.
// Reading interger results in this example.
// Read functions for other data-types are available - see end of post.
while ( sqlite3_step( stmt ) == SQLITE_ROW ) { // While query has result-rows.
// In your example the column count will be 1.
for ( int colIndex = 0; colIndex < sqlite3_column_count( stmt ); colIndex++ ) {
int result = sqlite3_column_int( stmt, colIndex );
// Do something with the result.
}
}
// Step, Clear and Reset the statement after each bind.
rc = sqlite3_step( stmt );
rc = sqlite3_clear_bindings( stmt );
rc = sqlite3_reset( stmt );
}
char *zErrMsg = 0; // Can perhaps display the error message if rc != SQLITE_OK.
rc = sqlite3_exec( conn, "END TRANSACTION", 0, 0, &zErrMsg ); // End the transaction.
rc = sqlite3_finalize( stmt ); // Finalize the prepared statement.
More Bind Functions
More Read Functions
As I understand from http://hoogli.com/items/Avoid_sqlite3_clear_bindings().html, the step rc = sqlite3_clear_bindings( stmt );
is not necessary in this case.
Unfortunately, I am not yet allowed to post this as a comment to the very good previous answer.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With