Insert record into QSqlTableModel

tovax

Hi all,
I have QSqlTableModel and connected QTableView. I want to insert record into tableview's current row, but it is always inserted into the last row of the tableview. I found that the primary key could not be generated correctly. could you give me some suggestion please?
The demo project is here: Demo Project
Best regards!

JonB

@tovax
Maybe someone will choose to look through all the files in your external project. For such a simple issue as this, couldn't you have narrowed it down in your code and only presented a couple of relevant lines here?

I found that the primary key could not be generated correctly.

Huh? Fix whatever that is, if you can't generate a PK correctly there's no point asking about where it does/doesn't appear in a view.

tovax

@JonB I'm sorry for that. The "Insert record" code as follows:

void JCDemoDatabase::onInsertClicked()
{
    qDebug() << __FUNCTION__ << tableView->currentIndex().row();

    int32_t currentRowIndex = tableView->currentIndex().row();

    tableModel->database().transaction();

    QSqlRecord record = tableModel->record();
    record.field(Table::PrimaryKey).setReadOnly(false);
    record.field(Table::PrimaryKey).setAutoValue(true);
    record.setGenerated(Table::PrimaryKey, true); // true

    for (int32_t columnIndex = Table::Field::Min; columnIndex <= Table::Field::Max; columnIndex++) {
        record.setValue(Table::FieldText[columnIndex], QString("---"));
    }

    if (!tableModel->insertRecord(currentRowIndex, record)) {
        qDebug() << __FUNCTION__ << 1 << "Error inserting record to the model";
        qDebug() << __FUNCTION__ << 2 << tableModel->lastError().text();
    } else {
        qDebug() << __FUNCTION__ << 1 << record.field(Table::PrimaryKey);
        qDebug() << __FUNCTION__ << 2 << record.field(Table::PrimaryKey).value();
    }

    if (tableModel->submitAll()) {
        tableModel->database().commit();
    } else {
        tableModel->database().rollback();
        qDebug() << __FUNCTION__ << 3 << tableModel->lastError().text();
    }

//        tableModel->select();
//        tableView->setModel(tableModel);
    qDebug() << __FUNCTION__ << 4 << tableModel->rowCount();
}

tovax

@JonB For example, the tablemodel has 50 rows and the primary key is from 1 to 50. If you delete line 20, the number 20 will be missing from the primary key. If a row is inserted in line 20, the primary key will not generate the primary key 20, but will add the primary key 51 at the end.

tovax

void JCDemoDatabase::onRemoveClicked()
{
    qDebug() << __FUNCTION__ << tableView->currentIndex().row();

    int32_t currentRowIndex = tableView->currentIndex().row();

    tableModel->database().transaction();

    if (!tableModel->removeRow(currentRowIndex)) {
        qDebug() << __FUNCTION__ << 1 << "Error inserting record to the model";
        qDebug() << __FUNCTION__ << 2 << tableModel->lastError().text();
    }

    if (tableModel->submitAll()) {
        tableModel->database().commit();
    } else {
        tableModel->database().rollback();
        qDebug() << __FUNCTION__ << 3 << tableModel->lastError().text();
    }

//        tableModel->select();
//        tableView->setModel(tableModel);
    qDebug() << __FUNCTION__ << 4 << tableModel->rowCount();
}

JonB

@tovax said in Insert record into QSqlTableModel:

@JonB For example, the tablemodel has 50 rows and the primary key is from 1 to 50. If you delete line 20, the number 20 will be missing from the primary key. If a row is inserted in line 20, the primary key will not generate the primary key 20, but will add the primary key 51 at the end.

This is probably a true statement, but I don't know what you are asking.

If you have an auto-increment primary key in the backend database, and you delete a row, then left to its own devices the next record will not "fill" the gap, it will use the next highest available number. If in your code you attempt to tell it to use the missing number for its value on inserting a new record, then (I believe) some databases will respect that, some will ignore it and allocate the next number, and some will probably error with "duplicate key". If your database supports inserting a row with a PK in the "middle", it is your job in your code to calculate that missing number and pass it in your INSERT statement. The backend will not do that for you. And I don't see any indication you are doing that.

Your Qt code does not know about this. I am not sure whether after submitAll() Qt automatically tries to re-read the record inserted/get its values from the backend and will see what it actually got or not. If necessary your commented-out tableModel->select(); can be issued to re-read what is actually there now.

I do not understand what your question here is? You say nothing about whether you get any error messages, nothing about what you or do not want. And overall your question is about the "tableview", which I thought is what you were questioning, but now I wonder if you are just reporting it displays whatever is in the model? So if it gets the new highest number it will indeed appear at the end in the view.

tovax

@JonB Hi, thank you for your patience. My intention is to insert a record in the current row of tablemodel, but it is always appended to the end.
The relevant code is this sentence:

tableModel->insertRecord(currentRowIndex, record)

That's what the help manual says：

bool QSqlTableModel::insertRecord(int row, const QSqlRecord &record)
Inserts the record at position row. If row is negative, the record will be appended to the end.

But the test result is: no matter what the value of “row” is, record is always appended to the end.

JonB

@tovax
As I said, this may depend on when you do this with regard to when you submit your changes to the backend, which may cause the value to be altered. Print out what the value of the PK auto-increment is after you insert, or more to the point after you submit.

Purely at a guess, comment out your record.setGenerated(Table::PrimaryKey, true); // true, and even your record.field(Table::PrimaryKey).setAutoValue(true);. Does this make a difference to whether it respects your value if you pass in a "missing" middle one?

tovax

@JonB The test result is: Whether "setGenerated" and "setAutoValue" are commented out or not, the primary key is always 0 before insertion. I think that's why we can't meet our expectations.
So I'm confused about "setGenerated" and "setAutoValue", don't they mean automatic generation of primary keys?

tovax

@JonB When I manually set a non duplicate value for the primary key, the record can be correctly inserted into the current row.

JonB

@tovax said in Insert record into QSqlTableModel:

So I'm confused about "setGenerated" and "setAutoValue", don't they mean automatic generation of primary keys?

@JonB When I manually set a non duplicate value for the primary key, the record can be correctly inserted into the current row.

Yes, they mean auto-generation. So they generate the next number, not some "middle" one! As you say, if you want to "force in" your own, missing number then you cannot ask it to auto-generate the value for you!

You can probably disable auto-generate, insert your explicit one, and then re-enable auto-generate from then on?

tovax

@JonB I see. I'll try to add some code to generate the middle primary key as you suggest.

JonB

@tovax
I will say one thing: you should not really be trying to do this at all! You're not "supposed" to go back and re-insert auto-gen keys if you have deleted one.

Imagine you have one million rows. One of them has a "gap". How are you going to find that gap one? If you have to do it client/Qt side, the only way is to read all one million into the client and iterate them to find any gap. Not a good idea! Even if you try to use server-side SQL code to avoid downloading them all, I don't think your SQL will offer any (efficient) way of discovering the gap. So maybe thing again....??

tovax

@JonB How can I insert a record in the current row, please?

jsulm

@tovax said in Insert record into QSqlTableModel:

How can I insert a record in the current row, please?

What do you mean? A record is a row. Do you want to update a row?

tovax

@JonB The database is local, and the data structure is completely controlled by myself, as long as the record can be inserted into the current row.

tovax

@jsulm It is similar to inserting a row of data in Excel table.

jsulm

@tovax A SQL table is not like a Excel table: you do not insert rows in SQL at specific positions, there is nothing like that in SQL (no specific order of rows). So, what do you mean by "current row"?

tovax

@jsulm The row which the mouse clicked.

tovax

@jsulm The help manual says：

bool QSqlTableModel::insertRecord(int row, const QSqlRecord &record)
Inserts the record at position row. If row is negative, the record will be appended to the end.