22PO2 error with a QSqlTableModel on a psql database

rnfmcb

I am developing on a Linux machine, have a PSQL 9 database, QT4.8
My database table has 40,700 records
I am attempting to display a table that can be edited and then the edits submitted to my database through a save button. My database displays, I am able to edit on the model, but when I submit the changes, I get a really weird error. I do not know how to fix this error.

TableEditor::TableEditor(const QString &tableName, QWidget *parent)
: QWidget(parent)
{
model = new QSqlTableModel(this);

model->setTable(tableName);
model->setEditStrategy(QSqlTableModel::OnManualSubmit);
model->select();

model->setHeaderData(0, Qt::Horizontal, QObject::tr("ID"));
model->setHeaderData(1, Qt::Horizontal, QObject::tr("Service Type"));
model->setHeaderData(2, Qt::Horizontal, QObject::tr("Serviced By"));
model->setHeaderData(3, Qt::Horizontal, QObject::tr("Service Date"));
model->setHeaderData(4, Qt::Horizontal, QObject::tr("Duration"));
model->setHeaderData(5, Qt::Horizontal, QObject::tr("Alarm State"));	

QTableView *view = new QTableView;
view->setModel(model);
view->resizeColumnsToContents();

saveButton = new QPushButton(tr("Save"));
saveButton->setDefault(true);
revertButton = new QPushButton(tr("&Revert"));
quitButton = new QPushButton(tr("Quit"));

buttonBox = new QDialogButtonBox(Qt::Vertical);
buttonBox->addButton(saveButton, QDialogButtonBox::ActionRole);
buttonBox->addButton(revertButton, QDialogButtonBox::ActionRole);
buttonBox->addButton(quitButton, QDialogButtonBox::RejectRole);

connect(saveButton, SIGNAL(clicked()), this, SLOT(submit()));
connect(revertButton, SIGNAL(clicked()), model, SLOT(revertAll()));
connect(quitButton, SIGNAL(clicked()), this, SLOT(close()));

QHBoxLayout *mainLayout = new QHBoxLayout;
mainLayout->addWidget(view);
mainLayout->addWidget(buttonBox);
setLayout(mainLayout);

setWindowTitle(tr("Ra Service History"));

}

void TableEditor::submit()
{
model->database().transaction();
if (model->submitAll()) {
model->database().commit();
} else {
model->database().rollback();
QMessageBox::warning(this, tr("Table"),
tr("The database reported an error: %1")
.arg(model->lastError().text()));
}
}

Pl45m4

@rnfmcb said in 22PO2 error with a QSqlTableModel on a psql database:

I get a really weird error

That's not a 'weird' error and there's also a hint in it, where to look.

https://stackoverflow.com/questions/31519063/cannot-insert-error-array-value-must-start-with-or-dimension-information

Seems like you are trying to store a string (varchar) value inside an array field. (P)SQL expects { VALUE } there to successfully build the query.

rnfmcb

No, I am not writing the psql statement. The QSqlTableModel function does. I can not access that code as it is part of the QT framework. Your link would help if I was building the query, but that is not how the QSqlTableModel works. This is something that happens when the table is edited by the user and then the model creates the psql statement incorrectly. I know what the error is, but not how to fix it or get around this problem.

Pl45m4

@rnfmcb

It seems that the standard QSqlTableModel can not handle PostgreSQL arrays by default.
At least your typed in values dont match / got refused by the model (i.e. the query). If the the database expect these braces { } around your value(s), try to put them around your model input (e.g. { foo } ).

If this doesn't work out, you have two options, I guess:
Either you change the data type of your database table column to VARCHAR, INTEGER or whatever your array should store OR you write your own table model, which can handle your user input and stores it properly inside your database.

Here is an example of how to deal with PSQL arrays
https://forum.qt.io/topic/52630/parse-array-fields-from-postgresql/3

and another one
https://forum.qt.io/topic/51497/qsqlquery-with-postgresql-array-s

rnfmcb

@Pl45m4 Yes, I do not think that the QSqlTableModel will work. I think there is a bug in the code that makes it so that it does not make the right format for the array.
I am currently working on a QuerySqlModel to solve this problem. I am able to insert data into the database using this model. It's significantly more work then the QsqlTableModel and a whole lot more complicated.

Pl45m4

@rnfmcb said in 22PO2 error with a QSqlTableModel on a psql database:

I think there is a bug in the code that makes it so that it does not make the right format for the array.

Why should there a bug?! QSqlTableModel provides a quite simple model for just one (SQL) table only. It's not documented that it can deal with PostgreSQL arrays, so I think the model doesn't know better :) For the model the expected format ({ v1, v2, v3...}) is unknown, so it can't insert the typed values properly.

@rnfmcb said in 22PO2 error with a QSqlTableModel on a psql database:

It's significantly more work then the QsqlTableModel and a whole lot more complicated.

Nobody said, that it will be an easy job :) Subclassing SQLModels and implementing custom behavior can be very time consuming.