Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Inserting data into SQLite DB via QSqlQuery::addBindValue not working
Forum Updated to NodeBB v4.3 + New Features

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

Scheduled Pinned Locked Moved Solved General and Desktop
11 Posts 4 Posters 1.1k Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • N Offline
    N Offline
    Niagarer
    wrote on 6 Dec 2019, 16:17 last edited by Niagarer 12 Jun 2019, 16:24
    #1

    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();
    

    Qt 54.png
    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!

    1 Reply Last reply
    0
    • M Offline
      M Offline
      mrjj
      Lifetime Qt Champion
      wrote on 6 Dec 2019, 16:34 last edited by
      #2

      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.

      N 1 Reply Last reply 6 Dec 2019, 17:08
      3
      • C Online
        C Online
        Christian Ehrlicher
        Lifetime Qt Champion
        wrote on 6 Dec 2019, 16:36 last edited by
        #3

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

        Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
        Visit the Qt Academy at https://academy.qt.io/catalog

        1 Reply Last reply
        1
        • M mrjj
          6 Dec 2019, 16:34

          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.

          N Offline
          N Offline
          Niagarer
          wrote on 6 Dec 2019, 17:08 last edited by Niagarer 12 Jun 2019, 17:12
          #4

          @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.

          1 Reply Last reply
          0
          • C Online
            C Online
            Christian Ehrlicher
            Lifetime Qt Champion
            wrote on 6 Dec 2019, 19:28 last edited by Christian Ehrlicher 12 Jun 2019, 19:30
            #5

            @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.

            Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
            Visit the Qt Academy at https://academy.qt.io/catalog

            N JonBJ 2 Replies Last reply 6 Dec 2019, 22:34
            2
            • C Christian Ehrlicher
              6 Dec 2019, 19:28

              @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.

              N Offline
              N Offline
              Niagarer
              wrote on 6 Dec 2019, 22:34 last edited by
              #6

              @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.

              1 Reply Last reply
              0
              • C Online
                C Online
                Christian Ehrlicher
                Lifetime Qt Champion
                wrote on 7 Dec 2019, 07:12 last edited by
                #7

                @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!

                Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                Visit the Qt Academy at https://academy.qt.io/catalog

                1 Reply Last reply
                0
                • C Christian Ehrlicher
                  6 Dec 2019, 19:28

                  @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.

                  JonBJ Online
                  JonBJ Online
                  JonB
                  wrote on 7 Dec 2019, 10:23 last edited by
                  #8

                  @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!

                  C N 2 Replies Last reply 7 Dec 2019, 10:49
                  0
                  • JonBJ JonB
                    7 Dec 2019, 10:23

                    @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!

                    C Online
                    C Online
                    Christian Ehrlicher
                    Lifetime Qt Champion
                    wrote on 7 Dec 2019, 10:49 last edited by Christian Ehrlicher 12 Jul 2019, 10:49
                    #9

                    @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.

                    Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                    Visit the Qt Academy at https://academy.qt.io/catalog

                    1 Reply Last reply
                    1
                    • JonBJ JonB
                      7 Dec 2019, 10:23

                      @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!

                      N Offline
                      N Offline
                      Niagarer
                      wrote on 7 Dec 2019, 15:22 last edited by Niagarer 12 Jul 2019, 15:33
                      #10

                      @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.

                      1 Reply Last reply
                      1
                      • C Online
                        C Online
                        Christian Ehrlicher
                        Lifetime Qt Champion
                        wrote on 7 Dec 2019, 18:24 last edited by
                        #11

                        @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 :)

                        Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                        Visit the Qt Academy at https://academy.qt.io/catalog

                        1 Reply Last reply
                        1

                        1/11

                        6 Dec 2019, 16:17

                        • Login

                        • Login or register to search.
                        1 out of 11
                        • First post
                          1/11
                          Last post
                        0
                        • Categories
                        • Recent
                        • Tags
                        • Popular
                        • Users
                        • Groups
                        • Search
                        • Get Qt Extensions
                        • Unsolved