SQLite parameterized query in C

Still very angry at Discovery Health but that is not reason to stop using C. To execute parameterized query we prepare SQL statement with one or more placeholders. Placeholder could be question mark, alone or followed by number, column, dollar sign or at sign followed by alphanumeric. For example:

select title, full_name from jane where id = @id;

We prepare such statement using sqlite3_prepare_v2, later we bind our parameter and finally execute query. To do binding we will use appropriate function, there is few of them:

int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*)); int sqlite3_bind_double(sqlite3_stmt*, int, double); int sqlite3_bind_int(sqlite3_stmt*, int, int); int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64); int sqlite3_bind_null(sqlite3_stmt*, int); int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*)); int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int, void(*)(void*)); int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*); int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n);

All bind functions will return SQLITE_OK if binding is successful or error code if it fails. The first argument is handle to prepared statement. The second argument is index of parameter. The third argument is value to be set. For blob, text we have the fourth argument, size in bytes and the fifth – destructor function. Instead of destructor function we can pass constants SQLITE_STATIC - do nothing or SQLITE_TRANSIENT – make local copy and free it when done. To find out what is index of parameter we are using this function:

int sqlite3_bind_parameter_index(sqlite3_stmt*, const char *zName);

We pass prepared statement and parameter name it returns zero if there is no such parameter or parameter index if it exists. Even if we know that our parameter must have index one, we will still look for it to demonstrate how it is done. Here is the code:

#include <sqlite3.h> #include <stdio.h> #include <string.h> int main(int argc, char **argv) { int rc, i, v, col; sqlite3 *db; sqlite3_stmt *stmt; char *sql; rc = sqlite3_open_v2("surogat.db", &db, SQLITE_OPEN_READWRITE, NULL); if(rc != SQLITE_OK) { printf("Can't open database: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); return 1; } sql = "select title, full_name from jane where id = @id"; rc = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, 0); if(rc != SQLITE_OK) { printf("SQL error: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); return 1; } i = sqlite3_bind_parameter_index(stmt, "@id"); v = 3; rc = sqlite3_bind_int(stmt, i, v); if(rc != SQLITE_OK) { printf("SQL error: %s\n", sqlite3_errmsg(db)); sqlite3_finalize(stmt); sqlite3_close(db); return 1; } rc = sqlite3_step(stmt); col = sqlite3_column_count(stmt); for(i=0; i < col; i++) { printf("%s\t",sqlite3_column_name(stmt, i)); } printf("\n-----------------\n"); while(rc == SQLITE_ROW) { for(i=0; i < col; i++) { printf("%s\t", sqlite3_column_text(stmt, i)); } printf("\n"); rc = sqlite3_step(stmt); } sqlite3_finalize(stmt); sqlite3_close(db); return 0; }

Database should be loaded with required values in previous examples, if not here is sql to create it:

CREATE TABLE jane ( id INTEGER PRIMARY KEY NOT NULL, title TEXT, full_name TEXT NOT NULL )
INSERT INTO jane VALUES(1,'Mr','Smith');
INSERT INTO jane VALUES(2,'Mrs','Doe');
INSERT INTO jane VALUES(3,'Mr','Doe');

After we build it using:

gcc -g test.c -lsqlite3 -o test

We execute test and see the following output:

title full_name ----------------- Mr Doe

We could also misspell parameter name and rebuild to check is error handling working.