PostgreSQL + QSqlRelationalTableModel + editing problem

[Mirko1974]

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/QSqlRelationalTabl...te_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();

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

To copy to clipboard, switch view to plain text mode 

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;
}

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

To copy to clipboard, switch view to plain text mode 

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;
}

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;
}

To copy to clipboard, switch view to plain text mode 

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)

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)

To copy to clipboard, switch view to plain text mode 

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

cheers
Mirko

[Mirko1974]

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"));

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

To copy to clipboard, switch view to plain text mode 

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

QHeaderView *headerView = view->horizontalHeader();
    headerView->swapSections(3, 2);

QHeaderView *headerView = view->horizontalHeader();
    headerView->swapSections(3, 2);

To copy to clipboard, switch view to plain text mode 

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);
    }

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

To copy to clipboard, switch view to plain text mode 

It is an awful workaround but it works.

bye
Mirko