QSqlRelationalTableModel && removeRow() [Solved]

[luca]

Hi all,
in my application I can't remove a row from a QSqlRelationalTableModel.
This is the situation:
@
...
...
m_applicazioniTableModel = new QSqlRelationalTableModel(this, QSqlDatabase::database("db_vs_manager"));
m_applicazioniTableModel->setTable("vs_permessi");
m_applicazioniTableModel->setRelation(1,QSqlRelation("vs_applicazioni","id","descrizione"));
m_applicazioniTableModel->setEditStrategy(QSqlRelationalTableModel::OnManualSubmit);
m_applicazioniTableModel->select();
...
...
@

now if I call:
@
m_applicazioniTableModel->removeRow(5);
@
I get from debug:
@
QSqlQuery::value: not positioned on a valid record
@
but my model has 53 rows .

How can I solve this problem?

[kunashir]

Is Select() work fine?

[luca]

[quote author="kunashir" date="1310101354"]Is Select() work fine? [/quote]

Yes, I get a populated QTableVew.

[luca]

I found some discussion where someone has the same problem: "this":http://bugreports.qt.nokia.com/browse/QTBUG-5027 and "this":http://www.developer.nokia.com/Community/Discussion/showthread.php?190187-QSqlRelationalTableModel-removeRow-and-QSqlRelationalTableModel-removeRows-not-work . But no one post a solution...

[kunashir]

I was used it example - and all work fine:

@ #include <QtGui>
#include <QtSql>

int main(int argc, char *argv[]) {
QApplication app(argc, argv);
QTextCodec *codec = QTextCodec::codecForName("CP1251");
QTextCodec::setCodecForTr(codec);

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("mydb1");
db.setUserName("...");
db.setPassword("...");
db.open();

// create and fill table
QSqlQuery query;
query.exec("create table employee( "
           " id int primary key, "
           " name varchar(20), "
           " department int, "
           " position int)" );
query.exec(QObject::tr("insert into employee values(1, 'Ivanov A.K.', 1, 3)"));
query.exec(QObject::tr("insert into employee values(2, 'Petorv I.E.', 3, 2)"));
query.exec(QObject::tr("insert into employee values(3, 'Mihailov N.P.', 2, 1)"));

query.exec("create table department(id int, name varchar(20))");
query.exec(QObject::tr("insert into department values(1, 'Administration')"));
query.exec(QObject::tr("insert into department values(2, 'Accounting')"));
query.exec(QObject::tr("insert into department values(3, 'Planning Department)"));

query.exec("create table position(id int, name varchar(20))");
query.exec(QObject::tr("insert into position values(1, 'Operator')"));
query.exec(QObject::tr("insert into position values(2, 'Economist')"));
query.exec(QObject::tr("insert into position values(3, 'Boss')"));

QSqlRelationalTableModel model;
model.setTable("employee");
model.setSort(1, Qt::AscendingOrder);

model.setEditStrategy(QSqlTableModel::OnFieldchange);//but this other stategy

// set relation
model.setRelation(2, QSqlRelation("department", "id", "name"));
model.setRelation(3, QSqlRelation("position", "id", "name"));

model.setHeaderData(0, Qt::Horizontal, QObject::tr("Number"));
model.setHeaderData(1, Qt::Horizontal, QObject::tr("Name"));
model.setHeaderData(2, Qt::Horizontal, QObject::tr("department"));
model.setHeaderData(3, Qt::Horizontal, QObject::tr("Post"));

model.select();


// create view
QTableView view;
view.setModel(&model);

// create delegate
view.setItemDelegate(new QSqlRelationalDelegate(&view));

view.setWindowTitle(QObject::tr("Relation table"));
view.show();
model.removeRow(1);
return app.exec();

}@

Can You show scheme of DB?

[luca]

I have this situation:
@
CREATE TABLE vs_applicazioni (
id int(11) NOT NULL,
nome varchar(100) NOT NULL,
descrizione varchar(250) NOT NULL,
menu varchar(20) NOT NULL,
funzione_di_avvio varchar(200) DEFAULT NULL,
hidden tinyint(1) DEFAULT '0',
PRIMARY KEY (id),
KEY menu (menu),
CONSTRAINT vs_applicazioni_ibfk_1 FOREIGN KEY (menu) REFERENCES vs_menu (nome)
)

CREATE TABLE vs_permessi (
id_gruppo int(11) NOT NULL,
id_applicazione int(11) NOT NULL,
PRIMARY KEY (id_gruppo,id_applicazione),
KEY id_applicazione (id_applicazione),
CONSTRAINT vs_permessi_ibfk_1 FOREIGN KEY (id_applicazione) REFERENCES vs_applicazioni (id),
CONSTRAINT vs_permessi_ibfk_2 FOREIGN KEY (id_gruppo) REFERENCES vs_gruppi_utenti (id)
)
@

And I'm showing in a QTableView the vs_permessi table replacing id_applicazione column with vs_applicazioni.descrizione column . It show the correct data.

[kunashir]

I suppose, that the complex primary key for QSqlRelationalTableModel is a problem.
Maybe, easier to create your own model?

[luca]

[quote author="kunashir" date="1310112233"]I suppose, that the complex primary key for QSqlRelationalTableModel is a problem.
Maybe, easier to create your own model?[/quote]

I also tried with this tables:
@
CREATE TABLE vs_applicazioni (
id int(11) NOT NULL,
nome varchar(100) NOT NULL,
descrizione varchar(250) NOT NULL,
menu varchar(20) NOT NULL,
funzione_di_avvio varchar(200) DEFAULT NULL,
hidden tinyint(1) DEFAULT '0',
PRIMARY KEY (id),
KEY menu (menu)
)

CREATE TABLE vs_permessi (
id_gruppo int(11) NOT NULL,
id_applicazione int(11) NOT NULL,
PRIMARY KEY (id_gruppo,id_applicazione),
KEY id_applicazione (id_applicazione)
)
@

with the same result...

[luca]

I also tried your example with my table (the versione without foregn key):
@
#include <QtGui>
#include <QtSql>

int main(int argc, char *argv[]) {
QApplication app(argc, argv);
QTextCodec *codec = QTextCodec::codecForName("CP1251");
QTextCodec::setCodecForTr(codec);

QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setDatabaseName("vs_manager");
db.setUserName("luca");
db.setPassword("luca123");

if(!db.open())
{
    qDebug() << db.lastError().number();
    
}

QSqlRelationalTableModel model;
model.setTable("vs_permessi");
model.setSort(1, Qt::AscendingOrder);

model.setEditStrategy(QSqlTableModel::OnFieldChange);//but this other stategy

// set relation
model.setRelation(1,QSqlRelation("vs_applicazioni","id","descrizione"));

model.select();


// create view
QTableView view;
view.setModel(&model);

// create delegate
view.setItemDelegate(new QSqlRelationalDelegate(&view));

view.setWindowTitle(QObject::tr("Relation table"));
view.show();
model.removeRow(1);
return app.exec();

}

@

but I get the same error:
@
QSqlQuery::value: not positioned on a valid record
@

Should it be a mysql driver problem?

[kunashir]

I don't understand why:
@
m_applicazioniTableModel->setRelation(1,QSqlRelation("vs_applicazioni","id","descrizione"));
@

at the same time, table vs_permessi don't have column "descrizione".
If I rightly understand Your scheme.

[luca]

this should mean that I want "replace" the column 1 of vs_permessi table with the column descrizione of vs_applicazioni table linking it with vs_applicazioni.id .
The table view show the correct data.

[luca]

Now I tried to compile the example in Windows using ODBC to connect to the MySql DB with the same result...

[kunashir]

Hmm....
Query of removed is not correct
"DELETE FROM vs_permessi WHERE id_gruppo = ? AND id_applicazione IS NULL"
but I'm don't know why....
Sorry, his have also some value...

[luca]

I found the cause.
The table vs_permessi:
@
CREATE TABLE vs_permessi (
id_gruppo int(11) NOT NULL,
id_applicazione int(11) NOT NULL,
PRIMARY KEY (id_gruppo,id_applicazione),
KEY id_applicazione (id_applicazione)
)
@
has the column id_applicazione in its primary key and this seems the problem.

If I modify the table this way:
@
CREATE TABLE vs_permessi (
id_gruppo int(11) NOT NULL,
id_applicazione int(11) NOT NULL,
PRIMARY KEY (id_gruppo)
)
@

the problem disappears...

Very thanks for you time and your help...