Not able to insertRecord with QSqlRelationalTableModel

[zanahade]

I am trying to teach myself database features, and I have come into a problem. I have a relational table model for a mysql database table, but insertRecord fails. I am sure I am not understanding something, probably at a fundamental level. I was hoping some of you more experienced users could help. Here is what we are dealing with:

MySql 5.7 Tables:

DESCRIBE games;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| name | varchar(16) | NO | UNI | NULL | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
+-------+------------------+------+-----+---------+----------------+

DESCRIBE drawings;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| date | date | NO | | NULL | |
| game_id | int(11) | NO | | NULL | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
+---------+------------------+------+-----+---------+----------------+



C++


Game::Game(QTabWidget *parent, QString name) :
QTabWidget(parent),
ui(new Ui::Game)
{
this->db = QSqlDatabase::database("LC");

this->dbGames = new QSqlRelationalTableModel(this, db);
this->dbDrawings = new QSqlRelationalTableModel(this ,db);
this->dbRecords = new QSqlRelationalTableModel(this, db);
this->dbRules = new QSqlRelationalTableModel(this, db);
this->dbTypes = new QSqlRelationalTableModel(this, db);

this->dbGames->setTable("games");
this->dbDrawings->setTable("drawings");
this->dbRecords->setTable("records");
this->dbRules->setTable("rules");
this->dbTypes->setTable("types");

this->dbGames->setEditStrategy(QSqlTableModel::OnFieldChange);
this->dbDrawings->setEditStrategy(QSqlTableModel::OnFieldChange);
this->dbRecords->setEditStrategy(QSqlTableModel::OnFieldChange);
this->dbRules->setEditStrategy(QSqlTableModel::OnFieldChange);
this->dbTypes->setEditStrategy(QSqlTableModel::OnFieldChange);

this->dbDrawings->setRelation(this->dbDrawings->fieldIndex("game_id"), QSqlRelation("games", "id", "name"));
this->dbRecords->setRelation(this->dbRecords->fieldIndex("drawing_id"), QSqlRelation("drawings", "id", "date"));
this->dbRecords->setRelation(this->dbRecords->fieldIndex("type_id"), QSqlRelation("types", "id", "type"));
this->dbRules->setRelation(this->dbRules->fieldIndex("type_id"), QSqlRelation("types", "id", "type"));
this->dbRules->setRelation(this->dbRules->fieldIndex("game_id"), QSqlRelation("games", "id", "name"));

this->dbGames->select();
this->dbDrawings->select();
this->dbRecords->select();
this->dbRules->select();
this->dbTypes->select();



and later in the same class:


int Game::dbaddDrawing(QString dateString)
{
this->dbDrawings->setFilter("");
this->dbDrawings->select();

QDate recordDate = QDate::fromString(dateString, this->dateFormat);

QSqlRecord drawing = this->dbDrawings->record();

drawing.setValue(this->dbDrawings->fieldIndex("date"), recordDate);
drawing.setValue(this->dbDrawings->fieldIndex("name"), QVariant(this->gameName));
drawing.setGenerated(this->dbDrawings->fieldIndex("id"), false);

if (!this->dbDrawings->insertRecord(-1, drawing)) //this seems to be where the problem is.
{
console("Drawing Input failure!");
}

int returnVal = this->dbDrawings->query().lastInsertId().toInt();

return returnVal;
}


If I setValue for game_id instead of the relational tables show field (name), the outcome does not change.

My biggest thought has been to look at the insert query and see if that gives hints to where the failure is, but looking at

this->dbDrawings->query().lastQuery();
it always seems to show select queries and never the insert. How can I look at the insert query? is it not showing the insert query because it is never passed? Recommendations for how I can check?

Also, in the docs for QSqlRelationalTableModel it says "If you use a read-write QSqlRelationalTableModel, you probably..." That first part about read-write, what is the deal with that? Is that in reference to the permissions of the db user on the db, or is there a property that I am not seeing in the class that sets permissions?

**Deep breath** I have been at this for a couple of weeks now, while I can operate the database in QSqlTableModel's I can not seem to insert to it's relational counterpart, and I am out of ideas.

Please remember that I am very new (less than a year) to Qt and C++, and not very proficient in MySQL, so please be kind. Any help is greatly appreciated.

THANKS!

[Lesiok]

Take a look to MySQL logs.

[zanahade]

Thank you for the suggestion, Lesiok. I had already looked, but I figured, what the heck, one more time to be sure. Unfortunately, when I look through the MySQL logs there is nothing pointing to the problem. My guess at this point, is that it fails in the Qt Sql class somewhere (most likely because I screwed something up in my code) and never even gets presented to MySQL. I think I am going to revert to the QSqlTableModel, and process relations manually for now... unless anyone has any other suggestions? Is this the best way to process relational tables? Thanks again, in advance.