QSqlTableModel set Integer data to NULL

[lynnH]

I have a sql table with a column of type integer allowing null values and I use a QSqlTableModel to manipulate its data.

I am facing the following problem: I want to insert a Null value using the QSqlTableModel setData function. When I do this, QSqlTableModel translates my Null into 0, its default value (Qt QTBUG-1512), which is not what I want (0 has a specific meaning in my application).

How can I set a null value?

Any hint will be greatly appreciated.

[mcosta]

HI,

I'm working with Qt 4.7.1 on Windows XP and for me it works fine.

This Code

QString connName;
 
    QString fileName = QFileDialog::getSaveFileName (this, tr ("Create DB"));
    if (!fileName.isEmpty ()) {
        bool result = false;
 
        QSqlDatabase db = QSqlDatabase::addDatabase ("QSQLITE");
        db.setDatabaseName (fileName);
 
        result = db.open ();
        if (!result) {
            qWarning () << "Error in db.open():" << db.lastError ().text();
            return;
        }
        connName = db.connectionName ();
 
        db.exec ("CREATE TABLE T_PDC ("
                 "val1 INTEGER NOT NULL, "
                 "val2 INTEGER)");
 
        QSqlRecord record = db.record ("T_PDC");
 
        QModelIndex idx;
 
        QSqlTableModel model (this, db);
        model.setTable ("T_PDC");
        model.setEditStrategy (QSqlTableModel::OnManualSubmit);
        model.select ();
 
        int row;
        for (int i = 0; i < 5; ++i) {
            row = model.rowCount ();
            qDebug () << "model.rowCount() = " << row;
 
            result = model.insertRows (row, 1);
            if (!result) {
                qWarning () << "Error in model.insertRow () :" << model.lastError ().text ();
                break;
            }
            else
                qDebug () << "model.insertRow(): SUCCESS";
 
            idx = model.index (row, 0);
            if (!idx.isValid ()) {
                qWarning () << "Invalid Index (" << row << ", 0)";
                break;
            }
            model.setData (idx, i);
 
            idx = model.index (row, 1);
            if (!idx.isValid ()) {
                qWarning () << "Invalid Index (" << row << ", 0)";
                break;
            }
 
            if (i % 2)
                result = model.setData (idx, 10 * (i + 1));
            else
                result = model.setData (idx, QVariant());
 
            if (!result) {
                qWarning() << "Error in model.setData ():" << model.lastError ().text ();
                break;
            }
        }
 
        result = model.submitAll ();
        if (!result) {
            qWarning () << "Error in model.submitAll() :" << model.lastError ().text ();
        }
        db.close ();
    }
    QSqlDatabase::removeDatabase (connName);

QString connName;

    QString fileName = QFileDialog::getSaveFileName (this, tr ("Create DB"));
    if (!fileName.isEmpty ()) {
        bool result = false;

        QSqlDatabase db = QSqlDatabase::addDatabase ("QSQLITE");
        db.setDatabaseName (fileName);

        result = db.open ();
        if (!result) {
            qWarning () << "Error in db.open():" << db.lastError ().text();
            return;
        }
        connName = db.connectionName ();

        db.exec ("CREATE TABLE T_PDC ("
                 "val1 INTEGER NOT NULL, "
                 "val2 INTEGER)");

        QSqlRecord record = db.record ("T_PDC");

        QModelIndex idx;

        QSqlTableModel model (this, db);
        model.setTable ("T_PDC");
        model.setEditStrategy (QSqlTableModel::OnManualSubmit);
        model.select ();

        int row;
        for (int i = 0; i < 5; ++i) {
            row = model.rowCount ();
            qDebug () << "model.rowCount() = " << row;

            result = model.insertRows (row, 1);
            if (!result) {
                qWarning () << "Error in model.insertRow () :" << model.lastError ().text ();
                break;
            }
            else
                qDebug () << "model.insertRow(): SUCCESS";

            idx = model.index (row, 0);
            if (!idx.isValid ()) {
                qWarning () << "Invalid Index (" << row << ", 0)";
                break;
            }
            model.setData (idx, i);

            idx = model.index (row, 1);
            if (!idx.isValid ()) {
                qWarning () << "Invalid Index (" << row << ", 0)";
                break;
            }

            if (i % 2)
                result = model.setData (idx, 10 * (i + 1));
            else
                result = model.setData (idx, QVariant());

            if (!result) {
                qWarning() << "Error in model.setData ():" << model.lastError ().text ();
                break;
            }
        }

        result = model.submitAll ();
        if (!result) {
            qWarning () << "Error in model.submitAll() :" << model.lastError ().text ();
        }
        db.close ();
    }
    QSqlDatabase::removeDatabase (connName);

To copy to clipboard, switch view to plain text mode 

produce this result

sql.jpg

However consider to use QSqlRecord for obtain more compact code

int row;
        QSqlRecord record = db.record (model.tableName ());
        for (int i = 0; i < 5; ++i) {
            row = model.rowCount ();
            qDebug () << "model.rowCount() = " << row;
 
            record.setValue (0, i);
            if (i % 2)
                record.setNull (1);
            else
                record.setValue (1, 10 * (i + 1));
 
            model.insertRecord (row, record);
        }

int row;
        QSqlRecord record = db.record (model.tableName ());
        for (int i = 0; i < 5; ++i) {
            row = model.rowCount ();
            qDebug () << "model.rowCount() = " << row;

            record.setValue (0, i);
            if (i % 2)
                record.setNull (1);
            else
                record.setValue (1, 10 * (i + 1));

            model.insertRecord (row, record);
        }

To copy to clipboard, switch view to plain text mode 

[lynnH]

Thanks for answering. Unfortunately I am using Qt 4.5.3 and this is not working. I tried to set the value directly with the QSqlRecord.

Is it an issue corrected in Qt 4.7 or do I have a chance with Qt 4.6?