PDA

View Full Version : PostgreSQL + QSqlRelationalTableModel + editing problem



Mirko1974
26th May 2010, 10:26
Hi guys,
I have a problem trying to update a PostgreSQL record.

What I want to develop is a framework that would help me to speed up making db applications but I'm, having some beginner troubles .

It seems like Qt doesn't like when I try to update a QSqlRelationalTableModel which has a QSqlRelation applied to it.

I searched all around on internet and the only help i could find was this link that i applied
http://qtwiki.org/QSqlRelationalTableMod…ate_table_model but it doesn't seem to work.

the code is:



sqlModel->setTable("regions");
sqlModel->setPrimaryKey("id");
sqlModel->setRelation(1, QSqlRelation("nations", "id", "nation"));
sqlModel->setEditStrategy(QSqlTableModel::OnManualSubmit);
sqlModel->select();



I use this model to show the table data in a gridview

and then, clicking on a row a second form pops up with edits and combos mapped to the model.

the record creating code is:



QSqlRecord TableInformationProvider::getRecord(bool newRecord){
if (newRecord){
record = sqlModel->record();
setDefaultRecordValues(record);
emit recordPrepared(record);
} else {
record = sqlModel->record(currentModelIndex.row());
}
return record;
}



and the update code is:



bool TableInformationProvider::setRecord(bool newRecord){
int row = currentModelIndex.row();
bool result = false;
if (newRecord){
result = sqlModel->insertRecord(-1, record) && sqlModel->submitAll();
} else {
result = sqlModel->setRecord(row, record) && sqlModel->submitAll();
}
if (!result) {
QMessageBox::critical(0, qApp->tr("Database error"),
sqlModel->lastError().text());
}
return result;
}



the error message is:
"ERROR: invalid input syntax for integer: "Italy"
LINE 1: EXECUTE: qpsqlpstmt_1 (2, 'Italy', 'Piedmont', 2, 2)
QPSQL: Unable to create query"

the tables are:



CREATE TABLE nations(
id serial NOT NULL,
nation character varying NOT NULL DEFAULT 100,
CONSTRAINT nationspk PRIMARY KEY (id))

CREATE TABLE regions(
id serial NOT NULL,
nationid integer NOT NULL,
region character varying(100) NOT NULL,
CONSTRAINT regionspk PRIMARY KEY (id),
CONSTRAINT nations_regionsfk FOREIGN KEY (nationid)
REFERENCES nations (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT)



I'm pretty new on c++ and Qt (just a couple of months) so, please, be gentle.

cheers
Mirko

Mirko1974
27th May 2010, 09:36
I found myself a workaround, hope it will help others.

1) the application opens a View and not a query. the view is defined as follows: "select id, nationid, region, nationid as nationid2 from regions"
2) I set the relation on the 4thcolumn:


getModel()->setRelation(3, QSqlRelation("nations", "id", "nation"));

3) On the view swap the 4th and 3rd columns:


QHeaderView *headerView = view->horizontalHeader();
headerView->swapSections(3, 2);
4) After I get the record for insert/update I delete the lookup columns:


int index = record.indexOf("nation");
if (index>=0){
record.remove(index);
}


It is an awful workaround but it works.

bye
Mirko