Question 134137 in Visual Studio Code Question posted in
View the official documentation.

Visual Studio Code – sqlite3_bind_text does not work for me but bind-double works fine

I have written a little class to handle SQLite prepared queries. If my variable in the SQL is a double the code runs fine giving a list of values. But when I bind a string, I get no results because the sqlite3_step function returns SQLITE_DONE immediately.

The query that fails is
`

string prepsql =
        "Select foodname, price, storename from prices 
        join foods on (foods.foodkey = prices.foodkey)  
        join stores on (stores.storekey = prices.storekey) 
        where foodname = ? order by price";
`

and the C++ code for calling the functions is

 PreparedQuery pq(db, prepsql);
    pq.setVariable(1, "Milk");
    pq.execute();

The actual code that calls SQLite functions is

PreparedQuery::PreparedQuery(sqltDatabase db, string query): 
    Query(db, query)    {
         int rc = sqlite3_prepare_v2(db.getDb(), query.c_str(), query.size(),
             &stmt, nullptr);
         checkerr(rc);
    }
//bind a text value to the query
void PreparedQuery::setVariable(int index, string value) {
    string sval = value;
    auto val1 = sval.c_str();
    int rc = sqlite3_bind_text(stmt, 1,val1, sizeof(val1), NULL);
    checkerr(rc);
}
//bind a double value to the query
void PreparedQuery::setVariable(int index, double value) {
    int rc = sqlite3_bind_double(stmt, 1, value);
    checkerr(rc);
}

//execute the query and get back the column names and values
int PreparedQuery::execute() {
        
    while (sqlite3_step(stmt) != SQLITE_DONE){  
        for (int col = 0; col < sqlite3_column_count(stmt); col++) {
            const char* name = sqlite3_column_name(stmt, col);
            const unsigned char* val = sqlite3_column_text(stmt, col);
            std::cout << name << " is " << val << std::endl;
        }
    }
    sqlite3_finalize(stmt);
    return 0;
}

However, if I change the last part of the query to use a double

where price > ? order by price";

and call the setVariable method for a double

pq.setVariable(1, 2.00);

the query works correctly and I get a list of rows that match the query.

So there must be something wrong with my sqlite_bind_text call that I can’t discern. What might I be doing wrong.
This is a Windows 10 machine running Visual Studio Community Edition and I downloaded SQlite3 on 2/22/2023. C++ v20.

I tried the string argument enclosed in quotes "Milk" which made no difference. If I change the query to contain the actual string instead of the question mark,

where foodname = "Milk" order by price";

the query runs correctly.
If I run the question mark containing prepared query in SQLite Studio using the same database, it pops up a place to enter the variable value: Milk for instance, without quotes, and it runs correctly.

I have a feeling I am missing something obvious, but I haven’t spotted it yet. Suggestions welcome.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Two changes made this finally work. I made the string sval a class level variable so it wouldn't be deleted, and used strlen rather than sizeof.

    sval = value;  //class level variable
const char* val1 = sval.c_str();
int rc = sqlite3_bind_text(stmt, 1,val1, strlen(val1), NULL);

  2. 0

    Likely, the problem is that the string inside your C++ string object is getting moved or deleted before the actual SQL execution. Try passing SQLITE_TRANSIENT to flag the string as a “temporary”, so that SQLite makes its own internal copy.

    Edit: Also, don’t use sizeof(val1) for the length argument, because that just gives you the size of a char*. You need the actual length of the string. So:

    int rc = sqlite3_bind_text(stmt, 1, val1, strlen(val1), SQLITE_TRANSIENT);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search