zanahade
18th June 2017, 21:32
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!
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!