Inserting data into SQLite DB via QSqlQuery::addBindValue not working
-
wrote on 6 Dec 2019, 16:17 last edited by Niagarer 12 Jun 2019, 16:24
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! -
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. -
Apart from what @mrjj said - why do you prepare the query every time in the loop? This somehow contradicts preparing a query...
-
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.wrote on 6 Dec 2019, 17:08 last edited by Niagarer 12 Jun 2019, 17:12@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. -
Lifetime Qt Championwrote on 6 Dec 2019, 19:28 last edited by Christian Ehrlicher 12 Jun 2019, 19:30
@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 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.wrote on 6 Dec 2019, 22:34 last edited by@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.
-
@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!
-
@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.wrote on 7 Dec 2019, 10:23 last edited by@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....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 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....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!Lifetime Qt Championwrote on 7 Dec 2019, 10:49 last edited by Christian Ehrlicher 12 Jul 2019, 10:49@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.
-
@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....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!wrote on 7 Dec 2019, 15:22 last edited by Niagarer 12 Jul 2019, 15:33@JonB
Thank you!NULL
gets translated to 0 andnullptr
(orQ_NULLPTR
) raises an ambiguous error.
ButQVariant()
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.
-
@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 :)
1/11