Inserting data into SQLite DB via QSqlQuery::addBindValue not working

[Niagarer]

Hi,
I am surely missing something obvious, but I just don't see what's wrong.
The following code should result in an insert in the shown SQLite database:

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(filename);
    if(!db.open()){
        ui->statusBar->showMessage("could not open the database");
        return;
    }

    QSqlQuery query;

    query.exec("\
CREATE TABLE `Data` (\
`ID`	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\
`Key`	TEXT,\
`City`	TEXT,\
`Time_abs`	INTEGER,\
`Time_year`	INTEGER,\
`Time_month`	INTEGER,\
`Time_day`	INTEGER,\
`Time_hour`	INTEGER,\
`Time_minute`	INTEGER,\
`Time_second`	INTEGER,\
`code`	INTEGER,\
`name`	TEXT,\
`lon`	REAL,\
`lat`	REAL,\
`_id`	INTEGER,\
`timezone`	INTEGER,\
`dt`	INTEGER,\
`base`	TEXT,\
`w_id`	INTEGER,\
`w_main`	TEXT,\
`w_description`	TEXT,\
`w_icon`	TEXT,\
`m_temp`	REAL,\
`m_temp_min`	REAL,\
`m_temp_max`	REAL,\
`m_pressure`	REAL,\
`m_humidity`	REAL,\
`visibility`	INTEGER,\
`wind_speed`	REAL,\
`wind_deg`	REAL,\
`clouds`	INTEGER,\
`sys_type`	INTEGER,\
`sys_id`	INTEGER,\
`sys_country`	TEXT,\
`sys_sunrise`	INTEGER,\
`sys_sunset`	INTEGER\
);\
    ");

    for(CurrentData_meta r : metaList){
        query.prepare("INSERT INTO Data VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        query.addBindValue("asdf");
        query.addBindValue("asdf");
        query.addBindValue(0);
        query.addBindValue(0);
        query.addBindValue(0);
        query.addBindValue(0);
        query.addBindValue(0);
        query.addBindValue(0);
        query.addBindValue(0);
        query.addBindValue(0);
        query.addBindValue("asdf");
        query.addBindValue(1.5);
        query.addBindValue(1.5);
        query.addBindValue(0);
        query.addBindValue(0);
        query.addBindValue(0);
        query.addBindValue("asdf");
        query.addBindValue(0);
        query.addBindValue("asdf");
        query.addBindValue("asdf");
        query.addBindValue("asdf");
        query.addBindValue(1.5);
        query.addBindValue(1.5);
        query.addBindValue(1.5);
        query.addBindValue(1.5);
        query.addBindValue(1.5);
        query.addBindValue(0);
        query.addBindValue(1.5);
        query.addBindValue(1.5);
        query.addBindValue(0);
        query.addBindValue(0);
        query.addBindValue(0);
        query.addBindValue("asdf");
        query.addBindValue(0);
        query.addBindValue(0);
        query.exec();

The database gets created successfully, but stays empty. The entered values are just for simplicity, of course it doesn't work with the dynamic ones I actually want to use either, but it doesn't even work this way, so wrong mapping does not seem to be the problem.

Any ideas what I could have forgotten?
Thanks for answers!

[mrjj]

Hi
Yes you forgot to include error handling so you are not checking return value of exec() or the
prepare.
if they return false , you can use
https://doc.qt.io/qt-5/qsqlquery.html#lastError
and https://doc.qt.io/qt-5/qsqlquery.html#executedQuery
to find out why it fails.

[Christian Ehrlicher]

Apart from what @mrjj said - why do you prepare the query every time in the loop? This somehow contradicts preparing a query...

[Niagarer]

@mrjj
Thank you, it states a Parameter count mismatch. Which is strange, since it's not true...
But I am ignoring the PK 'ID' in the database when binding values (because it's autoincrementing). Could this be a problem? So far this had worked for me.

@Christian-Ehrlicher
Yes, as I wrote, the constant values inserted are just placeholders for debugging until I find the reason why it's not being inserted. But later, the values shall be coming from r.

[Christian Ehrlicher]

@Niagarer said in Inserting data into SQLite DB via QSqlQuery::addBindValue not working:

Yes, as I wrote, the contant values inserted are just placeholders for debugging

I did not talk about the constant values, I talked about the QSqlQuery::prepare() statement.

Which is strange, since it's not true...

It is true, your table has 36 columns, you only pass 35. If you don't want to pass all columns, you have to specify which columns you want to pass.
And even if you specify all columns afaik no database guarantees you that the column ordering stays the same, so the values may land in the wrong column when you don't specify the columns.

[Niagarer]

@Christian-Ehrlicher said in Inserting data into SQLite DB via QSqlQuery::addBindValue not working:

I did not talk about the constant values, I talked about the QSqlQuery::prepare() statement.

Yeah, sorry, you're right, that is redundant.

It is true, your table has 36 columns, you only pass 35. If you don't want to pass all columns, you have to specify which columns you want to pass.
And even if you specify all columns afaik no database guarantees you that the column ordering stays the same, so the values may land in the wrong column when you don't specify the columns.

@Niagarer said in Inserting data into SQLite DB via QSqlQuery::addBindValue not working:

But I am ignoring the PK 'ID' in the database when binding values (because it's autoincrementing). Could this be a problem? So far this had worked for me.

Seems like, this is a problem, although it kind of stands against the idea of an autoincrement primary key, the query seems to count it as a value needing to be bound. There is probably a way to let the DBMS fill the id column out itself, but I haven't found a way using addBindValue() so far.

[Christian Ehrlicher]

@Niagarer said in Inserting data into SQLite DB via QSqlQuery::addBindValue not working:

There is probably a way to let the DBMS fill the id column out itself

As I already said - specify your columns!

[JonB]

@Christian-Ehrlicher said in Inserting data into SQLite DB via QSqlQuery::addBindValue not working:

And even if you specify all columns afaik no database guarantees you that the column ordering stays the same, so the values may land in the wrong column when you don't specify the columns.

To be fair, I think you are guaranteed that an INSERT with unnamed columns will insert in the order the columns are ordered in your table, whatever that might be....

@Niagarer

It is true, your table has 36 columns, you only pass 35. If you don't want to pass all columns, you have to specify which columns you want to pass.

I think if you want to be able to do this and have it "skip" an auto-increment column you pass something like NULL in the place for that column and that will make it work?

Having said that, even if you can make it work I wholeheartedly agree with @Christian-Ehrlicher that you should actually specify your columns. Apart from dealing with making sure you have the ordering correct, it's an awful lot more maintainable like that. When I saw your all your ?s and bound values I didn't fancy counting them all up to make sure they tallied and were in the right order!

[Christian Ehrlicher]

@JonB said in Inserting data into SQLite DB via QSqlQuery::addBindValue not working:

whatever that might be....

Exactly - whatever that might be is just an assumption...

And I would use named placeholders - the countless '?' are just confusing.

[Niagarer]

@JonB
Thank you! NULL gets translated to 0 and nullptr (or Q_NULLPTR) raises an ambiguous error.
But QVariant() works! It ignores and fills the ID column out automatically.

I agree, that it's better style to specify columns

    query.prepare("INSERT INTO Data ('Key', 'City', 'Time_abs', 'Time_year', 'Time_month', 'Time_day', 'Time_hour', 'Time_minute', 'Time_second', "
                  "'code', 'name', 'lon', 'lat', '_id', 'timezone', 'dt', 'base', 'w_id', 'w_main', 'w_description', 'w_icon', "
                  "'m_temp', 'm_temp_min', 'm_temp_max', 'm_pressure', 'm_humidity', "
                  "'visibility', 'wind_speed', 'wind_deg', 'clouds', 'sys_type', 'sys_id', 'sys_country', 'sys_sunrise', 'sys_sunset') "

                  "VALUES (:Key, :City, :Time_abs, :Time_year, :Time_month, :Time_day, :Time_hour, :Time_minute, :Time_second, "
                  ":code, :name, :lon, :lat, :_id, :timezone, :dt, :base, :w_id, :w_main, :w_description, :w_icon, "
                  ":m_temp, :m_temp_min, :m_temp_max, :m_pressure, :m_humidity, "
                  ":visibility, :wind_speed, :wind_deg, :clouds, :sys_type, :sys_id, :sys_country, :sys_sunrise, :sys_sunset)");

    for(int i=0; i<metaList.length(); i++){
        CurrentData_meta r = metaList[i];

        query.bindValue(":Key", r.key);
        query.bindValue(":City", r.city);
        query.bindValue(":Time_abs", r.time_abs);
        query.bindValue(":Time_year", r.time_year);
        query.bindValue(":Time_month", r.time_month);
        query.bindValue(":Time_day", r.time_day);
        query.bindValue(":Time_hour", r.time_hour);
        query.bindValue(":Time_minute", r.time_minute);
        query.bindValue(":Time_second", r.time_second);
        query.bindValue(":code", r.code);
        query.bindValue(":name", r.name);
        query.bindValue(":lon", r.lon);
        query.bindValue(":lat", r.lat);
        query.bindValue(":_id", r._id);
        query.bindValue(":timezone", r.timezone);
        query.bindValue(":dt", r.dt);
        query.bindValue(":base", r.base);
        query.bindValue(":w_id", r.w_id);
        query.bindValue(":w_main", r.w_main);
        query.bindValue(":w_description", r.w_description);
        query.bindValue(":w_icon", r.w_icon);
        query.bindValue(":m_temp", r.m_temp);
        query.bindValue(":m_temp_min", r.m_temp_min);
        query.bindValue(":m_temp_max", r.m_temp_max);
        query.bindValue(":m_pressure", r.m_pressure);
        query.bindValue(":m_humidity", r.m_humidity);
        query.bindValue(":visibility", r.visibility);
        query.bindValue(":wind_speed", r.wind_speed);
        query.bindValue(":wind_deg", r.wind_deg);
        query.bindValue(":clouds", r.clouds);
        query.bindValue(":sys_type", r.sys_type);
        query.bindValue(":sys_id", r.sys_id);
        query.bindValue(":sys_country", r.sys_country);
        query.bindValue(":sys_sunrise", r.sys_sunrise);
        query.bindValue(":sys_sunset", r.sys_sunset);

        query.exec();
    }

But the addBindValue() method is more flexible for loops in the end.

But in general I agree.

[Christian Ehrlicher]

@Niagarer said in Inserting data into SQLite DB via QSqlQuery::addBindValue not working:

but when using tables even bigger than this one

When you have columns larger than that your design is bad - and you should not generate such kind of stuff by hand. But it's not my project :)