How does prepared statements in Sqlite C++ work

user_11380400 0
Viewed 4041

I do not know how to implement prepared statements in my Sqlite3 code

#include <iostream>
#include <sqlite3.h>
#include <stdio.h>

static int callback (void* NotUsed, int argc, char** argv, char** azColName) {
    int i;
    for (i = 0; i < argc; i++) {
        std::cout << ("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    std::cout << ("\n");
    return 0;
}

int main (int argc, char* argv[]) {
    sqlite3* db;
    char* zErrMsg = 0;
    int rc;
    char* sql;

    /* Open database */
    rc = sqlite3_open ("test.db", &db);

    if (rc) {
        std::cerr << "Can't open database: \n" << sqlite3_errmsg (db);
        return (0);
    }
    else {
        std::cout << "Opened database successfully\n";
    }
    std::string newName;
    std::cin >> newName;
    /* Create SQL statement */
    sql = "UPDATE company SET name = newName WHERE id = 1";


    /* Execute SQL statement */
    rc = sqlite3_exec (db, sql, callback, 0, &zErrMsg);

    if (rc != SQLITE_OK) {
        std::cout << "SQL error: \n" << zErrMsg;
        sqlite3_free (zErrMsg);
    }
    else {
        std::cout << "Records created successfully\n";
    }
    sqlite3_close (db);
    return 0;
}

The user has to input newName and this variable should be used to Update a field in the Database. This way it does not work, because the Sql script is searching for a column. In the internet I found, that I had to use a prepared statement, but I do not know how to implement it.

c++17 sqlite c++ database
1 Answers

You start with an sql statement that has placeholders for the parameters that you wish to bind later. Here, I use a single question mark for the placeholder, but there are other options described in the documentation.

std::string sql = "UPDATE company SET name = ? WHERE id = 1";

Then you construct a prepared statement (or "compile", as they say it in sqlite documentation). You'll normally use sqlite_prepare_v2 function, but there are others (for when your statement is encoded in something else than utf-8, for example).

sqlite3_stmt* stmt; // will point to prepared stamement object
sqlite3_prepare_v2(
    db,            // the handle to your (opened and ready) database
    sql.c_str(),    // the sql statement, utf-8 encoded
    sql.length(),   // max length of sql statement
    &stmt,          // this is an "out" parameter, the compiled statement goes here
    nullptr);       // pointer to the tail end of sql statement (when there are 
                    // multiple statements inside the string; can be null)

Then you bind the parameter(s). There's a whole bunch of avaliable functions. Which one exactly you use depends on the type of data that you're binding to the parameter. Here, we bind text, so we use sqlite3_bind_text:

std::string newName = /* get name from user */;
sqlite3_bind_text(
    stmt,             // previously compiled prepared statement object
    1,                // parameter index, 1-based
    newName.c_str(),  // the data
    newName.length(), // length of data
    SQLITE_STATIC);   // this parameter is a little tricky - it's a pointer to the callback
                      // function that frees the data after the call to this function.
                      // It can be null if the data doesn't need to be freed, or like in this case,
                      // special value SQLITE_STATIC (the data is managed by the std::string
                      // object and will be freed automatically).

So, the prepared statement is ready to go. Now you execute it by passing it to sqlite3_step:

 sqlite3_step(stmt); // you'll want to check the return value, read on...

Now, when you step through a statement that's supposed to return rows of a result table, this function will keep returning SQLITE_ROW as long as there are result rows to process, and SQLITE_DONE when there are none left. You can use sqlite3_column_* family of functions to get the single columns from a result row. I'll let you figure this out on your own.

For a simple update statements that you have, sqlite3_step will return SQLITE_DONE on the first call. More info and possible error codes are here.

When it's all done, you finish by destructing the prepared statement.

sqlite3_finalize(stmt);

I hope this should get you started.

user_9478360
Related