How to insert multiple rows wih QT Sqlite?

Question

I have the following code (it is a function), and I want to insert 500 values to my database whenever I call it.

QSqlDatabase::database().transaction();
query.prepare("INSERT INTO datatable (Name, age, data1, data2, data3, data4, data5)"
              "VALUES (?, ?, ?, ?, ?, ?, ?)");
for (int i = 0; i<500; i++){
    query.bindValue(0,Name[j]);
    query.bindValue(1,age[j]);
    query.bindValue(2,data1[j]);
    query.bindValue(3,data2[j]);
    query.bindValue(4,data3[j]);
    query.bindValue(5,data4[j]);
    query.bindValue(6,data5[j]);
    query.next();   //just trying to go for the next row
}
qDebug() << "Finish" << QSqlDatabase::database().commit();

The problem is that this is only inserting the data from the values when j=500, I mean, this only stores the last data, and the other 499 doesn't get stored.

Can anyone help me? I have tried to put the query, prepared for loop inside but this didn't work as well.

eyllanesc · Accepted Answer · 2019-12-05 17:46:29Z

3

You have to use the exec() method instead of next():

QSqlDatabase db = QSqlDatabase::database();

if(db.transaction()){

    QSqlQuery query;
    query.prepare("INSERT INTO datatable (Name, age, data1, data2, data3, data4, data5) VALUES (?, ?, ?, ?, ?, ?, ?)");
    for (int i = 0; i < 500; i++){
        query.bindValue(0, Name[i]);
        query.bindValue(1, age[i]);
        query.bindValue(2, data1[i]);
        query.bindValue(3, data2[i]);
        query.bindValue(4, data3[i]);
        query.bindValue(5, data4[i]);
        query.bindValue(6, data5[i]);
        if(!query.exec()){
            qDebug() << query.lastError().text();
        }
    }

    if(!db.commit()){
        qDebug() << db.lastError().text();
    }
}

answered Dec 5, 2019 at 17:46

eyllanesc

246k19 gold badges205 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

strythio109 · Accepted Answer · 2019-12-05 17:46:04Z

2

query.next() is usually used to iterate through rows of a data reader. You should be using query.exec() to insert data with your transaction.

Here is a pretty good tutorial: https://katecpp.wordpress.com/2015/08/28/sqlite-with-qt/

answered Dec 5, 2019 at 17:46

strythio109

463 bronze badges

Comments

Maxim Paperno · Accepted Answer · 2023-01-02 06:41:51Z

2

This is exactly what QSqlQuery::execBatch is for.

query.prepare("INSERT INTO datatable (Name, age, data1, data2, data3, data4, data5)"
              "VALUES (?, ?, ?, ?, ?, ?, ?)");
QVariantList names;
QVariantList ages;
QVariantList data[5];
for (int i = 0; i<500; i++) {
    names << Name[j];
    ages << age[j];
    data[0] << data1[j];
    data[1] << data2[j];
    data[2] << data3[j];
    data[3] << data4[j];
    data[4] << data5[j];
}
query.addBindValue(names);
query.addBindValue(ages);
for (int i=0; i < 5; ++i)
    query.addBindValue(data[i]);

if (!query.execBatch())
   qDebug() << q.lastError();

answered Jan 2, 2023 at 6:41

Maxim Paperno

4,9822 gold badges20 silver badges24 bronze badges

Collectives™ on Stack Overflow

How to insert multiple rows wih QT Sqlite?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related