SQLite3 How to handle column index out of range-CodePudding

Whenever I want to insert a type object (define below) in the database I get "error 25 : column index out of range". Which means that the value I try to bind doesn't fit in the query right?

CREATE TABLE color (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    label TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS object (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    label TEXT NOT NULL,
    color_id INTEGER,
    position_x REAL,
    position_y REAL,
    position_z REAL,
    distance REAL,
    FOREIGN KEY(color_id) REFERENCES color(id)
);

-- few insert to populate color
INSERT INTO color(id,label) VALUES (1,'Black');
INSERT INTO color(id,label) VALUES (2,'White');
INSERT INTO color(id,label) VALUES (3,'Red');
INSERT INTO color(id,label) VALUES (4,'Lime');
INSERT INTO color(id,label) VALUES (5,'Blue');
INSERT INTO color(id,label) VALUES (6,'Yellow');

I tried putting the query using the CLI interface and this worked.

insert into object (label,color_id,position_x,position_y,position_z,distance) values ("can",2,0.2,0.2,0.2,1.2);

I checked the number of fields, the name of variables, my database is open when my class is created and close on destruction. And last colorIndex is an int that I retrieve from a function I already use to insert other element in a different table. I also printed the values and they are correct.

I guess I m missing something here. I can't see where else can be my mistake.

        typedef struct Object{
            std::string label;
            std::string color;
            float pos_x;
            float pos_y;
            float pos_z;
            float distance;
        } Object;
    
        int VisionModel::getColorByLabel(std::string sColor){
            int colorId;
            query = "SELECT id FROM color WHERE label = (?)";
            pStmt = nullptr;
            int rc;
            rc = sqlite3_prepare_v2(db,query.c_str(), -1, &pStmt, NULL);
            if (rc != SQLITE_OK){
                std::cout << "prepare getColorByLabel didn t went through" << std::endl;
                manageSQLiteErrors(pStmt);
                return colorId;
            }

            if (sqlite3_bind_text(pStmt, 1, sColor.c_str(), -1, NULL) != SQLITE_OK){
                std::cout << "bind color label didn t went through" << std::endl;
                manageSQLiteErrors(pStmt);
                return colorId;
            }

            while ( (rc = sqlite3_step(pStmt)) == SQLITE_ROW) {                                              
                colorId = sqlite3_column_int(pStmt, 0);
            }
            sqlite3_finalize(pStmt);
            return colorId;
        }

        void VisionModel::createObject(Object object){
            query = "INSERT INTO object (label,color_id,position_x,position_y,position_z,distance) VALUES (?,?,?,?,?,?)";
            pStmt = nullptr;
            int rc;
            // get the index for given color
            int colorIndex = -1;
            if (!object.color.empty() && object.color != "0"){
                colorIndex = getColorByLabel(object.color); 
            }
            rc = sqlite3_prepare_v2(db,query.c_str(), -1, &pStmt, NULL);
            if (rc != SQLITE_OK){
                std::cout << "prepare createObject didn t went through" << std::endl;
                manageSQLiteErrors(pStmt);
                return ;
            }

            if (sqlite3_bind_text(pStmt, 1, object.label.c_str(), -1, NULL) != SQLITE_OK){
                std::cout << "bind object label didn t went through" << std::endl;
                manageSQLiteErrors(pStmt);
                return ;
            }

         
            if (colorIndex != -1){
                rc = sqlite3_bind_int(pStmt, 2, colorIndex);
                if ( rc != SQLITE_OK){
                    std::cout << "bind object color index didn t went through" << std::endl;
                    std::cout << std::to_string(colorIndex) << std::endl;
                    manageSQLiteErrors(pStmt);
                    return ;
                }
            }

            if (sqlite3_bind_double(pStmt, 3, object.pos_x) != SQLITE_OK){
                std::cout << "bind object pos x didn t went through" << std::endl;
                manageSQLiteErrors(pStmt);
                return ;
            }
            if (sqlite3_bind_double(pStmt, 4, object.pos_y) != SQLITE_OK){
                std::cout << "bind person object y didn t went through" << std::endl;
                manageSQLiteErrors(pStmt);
                return ;
            }
            if (sqlite3_bind_double(pStmt, 5, object.pos_z) != SQLITE_OK){
                std::cout << "bind person object z didn t went through" << std::endl;
                manageSQLiteErrors(pStmt);
                return ;
            }
            if (sqlite3_bind_double(pStmt, 6, object.distance) != SQLITE_OK){
                std::cout << "bind person object distance didn t went through" << std::endl;
                manageSQLiteErrors(pStmt);
                return ;
            }

            if ((rc = sqlite3_step(pStmt)) != SQLITE_DONE) {                                              /* 2 */
                std::cout << "step didn t went through" << std::endl;
                manageSQLiteErrors(pStmt);
                return ;
            }
            sqlite3_finalize(pStmt);
        }

Here is a minimal reproductible version of the code that you can copy paste and compile. Don't forget to create the database with the query above first.

compile : g -o main main.cpp -lsqlite3

code :

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

typedef struct Object
{
    std::string label;
    std::string color;
    float pos_x;
    float pos_y;
    float pos_z;
    float distance;
} Object;

class VisionModel
{
private:
    sqlite3 *db;
    const char *zErrMsg = 0;
    std::string query;
    sqlite3_stmt *pStmt; 
public:

    VisionModel()  
    {
        connect();
    }

    ~VisionModel()
    {
        close();
    }

    void connect()
    {
        int rc;

        std::string db_file_path("./database.db");

        rc = sqlite3_open(db_file_path.c_str(), &db);
        if (rc)
        {
            std::cerr << "Can't open database" << sqlite3_errmsg(db) << std::endl;
            std::cerr << "SQL code error : " << sqlite3_extended_errcode(db) << std::endl;
            return;
        }
    };

    void manageSQLiteErrors(sqlite3_stmt *pStmt)
    {
        std::cerr << "SQL error : " << sqlite3_errmsg(db) << std::endl;
        std::cerr << "SQL code error : " << sqlite3_extended_errcode(db) << std::endl;
        sqlite3_finalize(pStmt);
    }

    void close()
    {
        sqlite3_close(db);
    };
    int getColorByLabel(std::string sColor)
    {
        int colorId;
        query = "SELECT id FROM color WHERE label = (?)";
        pStmt = nullptr;
        int rc;
        rc = sqlite3_prepare_v2(db, query.c_str(), -1, &pStmt, NULL);
        if (rc != SQLITE_OK)
        {
            std::cout << "prepare getColorByLabel didn t went through" << std::endl;
            manageSQLiteErrors(pStmt);
            return colorId;
        }

        if (sqlite3_bind_text(pStmt, 1, sColor.c_str(), -1, NULL) != SQLITE_OK)
        {
            std::cout << "bind color label didn t went through" << std::endl;
            manageSQLiteErrors(pStmt);
            return colorId;
        }

        while ((rc = sqlite3_step(pStmt)) == SQLITE_ROW)
        {
            colorId = sqlite3_column_int(pStmt, 0);
        }
        sqlite3_finalize(pStmt);
        return colorId;
    }

    void createObject(Object object)
    {
        query = "INSERT INTO object (label,color_id,position_x,position_y,position_z,distance) VALUES (?,?,?,?,?,?)";
        pStmt = nullptr;
        int rc;
        // get the index for given color
        int colorIndex = -1;
        if (!object.color.empty() && object.color != "0")
        {
            colorIndex = getColorByLabel(object.color);
        }
        rc = sqlite3_prepare_v2(db, query.c_str(), -1, &pStmt, NULL);
        if (rc != SQLITE_OK)
        {
            std::cout << "prepare createObject didn t went through" << std::endl;
            manageSQLiteErrors(pStmt);
            return;
        }

        if (sqlite3_bind_text(pStmt, 1, object.label.c_str(), -1, NULL) != SQLITE_OK)
        {
            std::cout << "bind object label didn t went through" << std::endl;
            manageSQLiteErrors(pStmt);
            return;
        }

        if (colorIndex != -1)
        {
            rc = sqlite3_bind_int(pStmt, 2, colorIndex);
            if (rc != SQLITE_OK)
            {
                std::cout << "bind object color index didn t went through" << std::endl;
                std::cout << std::to_string(colorIndex) << std::endl;
                manageSQLiteErrors(pStmt);
                return;
            }
        }

        if (sqlite3_bind_double(pStmt, 3, object.pos_x) != SQLITE_OK)
        {
            std::cout << "bind object pos x didn t went through" << std::endl;
            manageSQLiteErrors(pStmt);
            return;
        }
        if (sqlite3_bind_double(pStmt, 4, object.pos_y) != SQLITE_OK)
        {
            std::cout << "bind person object y didn t went through" << std::endl;
            manageSQLiteErrors(pStmt);
            return;
        }
        if (sqlite3_bind_double(pStmt, 5, object.pos_z) != SQLITE_OK)
        {
            std::cout << "bind person object z didn t went through" << std::endl;
            manageSQLiteErrors(pStmt);
            return;
        }
        if (sqlite3_bind_double(pStmt, 6, object.distance) != SQLITE_OK)
        {
            std::cout << "bind person object distance didn t went through" << std::endl;
            manageSQLiteErrors(pStmt);
            return;
        }

        if ((rc = sqlite3_step(pStmt)) != SQLITE_DONE)
        { /* 2 */
            std::cout << "step didn t went through" << std::endl;
            manageSQLiteErrors(pStmt);
            return;
        }
        sqlite3_finalize(pStmt);
    }
};

int main()
{
    Object obj = {"apple", "Blue", 0.1, 0.2, 0.1, 2.0};
    VisionModel vm;
    vm.createObject(obj);
}

CodePudding user response：

If you used your debugger to show the value of query here, the problem becomes very easy to see:

rc = sqlite3_prepare_v2(db, query.c_str(), -1, &pStmt, NULL);

You will discover that this query is not really the INSERT statement that you think it is.

This is because just a few lines earlier:

colorIndex = getColorByLabel(object.color);

And this ends up clobbering the query variable, setting it to a "SELECT" with just one ? placeholder.

You can use this as a golden opportunity to learn how to use a debugger, which makes solving these Scooby-Doo mysteries simple. Try to use the shown program in your debugger, exactly as is, setting a breakpoint at the sqlite3_prepare_v2 line in createObject, and then inspecting what's in the query, and having your own "Eureka!" moment.