Submitting QSqlRecord to MySQL database in Qt

Question

I want to access a MySQL database and I want to read and write data from/to the database within my Qt(4.5.x)/C++ program. For the read/write process, I'm trying to use QSqlTableModel, QSqlTableRcord and QSqlDatabase.

What am I doing wrong within these few lines of example code:

The test database has 3 columns: float x, float y, blob img.

int main(){
    QImage img("./some_image.png");
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("test");
    db.setPort(3306);
    db.setUserName("root");
    db.setPassword("xxxxxxx");
    if (!db.open() )
        qDebug("Mising db / unable to open");
    else {
        QSqlTableModel model;
        qDebug() << "tables::" <<db.tables(); //so I see the table exists and gets detected
        model.setTable("test_table");

        QSqlRecord rec;
        rec.setValue(0,1.0f);
        rec.setValue(1,2.0f);

        QByteArray ba;
        QBuffer buffer(&ba);
        buffer.open(QIODevice::WriteOnly);
        img.save(&buffer, "PNG");
        rec.setValue(2,ba);

        model.insertRecord(0,rec);
        qDebug() << model.lastError().text();
        if (!model.submitAll())
            qDebug() << "Submit all did not work";
    }
    return 0;
}

model.lasterror().text() is empty.

How do I fix this? Or should I do it completely different?

Answer 1

Your QSqlRecord doesn't have any fields defined. You need to add

rec.append(QSqlField("x", QVariant::Double));
rec.append(QSqlField("y", QVariant::Double));
rec.append(QSqlField("img", QVariant::Image));

before you set the values

Answer 2

Use your table to generate the record, qt will automatically create the fields contained by the table.

QSqlTableModel table;
table.setTable("test_table");
QSqlRecord rcd = table.record();
rcd.setValue("x",1.0f);
rcd.setValue("y",2.0f);

QByteArray ba;
QBuffer buffer(&ba);
buffer.open(QIODevice::WriteOnly);
img.save(&buffer, "PNG");
rcd.setValue("img",ba);

model.insertRecord(-1,rcd);