Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to prepare sql statements and bind parameters?

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.

like image 217
Jacobian Avatar asked Jul 31 '15 11:07

Jacobian


People also ask

How do you bind a parameter in SQL?

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.

What do you understand by prepared and bind parameters statement?

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.

Which methods on the PreparedStatement can be used to bind the parameters?

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.

What is SQL query 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.


2 Answers

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

like image 161
HvS Avatar answered Sep 20 '22 05:09

HvS


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.

like image 29
Simon1X Avatar answered Sep 21 '22 05:09

Simon1X