Database issue with adding record using QSqlRelationalTable and QDataWidgetMapper

[AlphaWolfXV]

Post 1 of 2 due to length... Question in part 2 of 2...
Hello,
I am using Qt Creator 1.2.1, with Qt 4.5.2 on Windows Vista x32. I have a MySQL database created with MySQL Workbench v5.2.16.

I have been trying to get a cpp class completed to access 1 of the tables in the db and have run into an issue with the add record algorithm. Basically, I can successfully navigate the records in the table, but on adding a record, if I navigate away the model submits a copy of the record I nav'd to. For example:

tEmployees:
simple table with 2 related fields for department and status

CREATE TABLE IF NOT EXISTS `timeDB`.`tEmployees` (
`idtEmployees` INT NOT NULL AUTO_INCREMENT ,
`empNameCode` VARCHAR(45) NOT NULL ,
`empFirstName` VARCHAR(50) NULL ,
`empLastName` VARCHAR(80) NULL ,
`tDepartment_idtDepartment` INT NOT NULL ,
`tStatus_idtStatus` INT NOT NULL ,
`empStartDate` DATETIME NOT NULL ,
PRIMARY KEY (`idtEmployees`) ,
INDEX `fk_tEmployees_tDepartment` (`tDepartment_idtDepartment` ASC) ,
INDEX `fk_tEmployees_tStatus` (`tStatus_idtStatus` ASC) ,
CONSTRAINT `fk_tEmployees_tDepartment`
FOREIGN KEY (`tDepartment_idtDepartment` )
REFERENCES `timeDB`.`tDepartment` (`idtDepartment` )
ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT `fk_tEmployees_tStatus`
FOREIGN KEY (`tStatus_idtStatus` )
REFERENCES `timeDB`.`tStatus` (`idtStatus` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB

tDepartment:
(contains fk links for dept id in employee table)

CREATE TABLE IF NOT EXISTS `timeDB`.`tDepartment` (
`idtDepartment` INT NOT NULL AUTO_INCREMENT ,
`DepartmentCode` VARCHAR(10) NULL ,
`DepartmentName` VARCHAR(45) NULL ,
`DepartmentSupervisor` VARCHAR(45) NULL ,
`tStatus_idtStatus` INT NOT NULL ,
PRIMARY KEY (`idtDepartment`) ,
INDEX `fk_tDepartment_tStatus` (`tStatus_idtStatus` ASC) ,
CONSTRAINT `fk_tDepartment_tStatus`
FOREIGN KEY (`tStatus_idtStatus` )
REFERENCES `timeDB`.`tStatus` (`idtStatus` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB

tStatus:
(contains fk for statusid (i.e. active/inactive))


CREATE TABLE IF NOT EXISTS `timeDB`.`tStatus` (
`idtStatus` INT NOT NULL AUTO_INCREMENT ,
`StatusDescription` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`idtStatus`) )
ENGINE = InnoDB


tEmployee - Data:


1 QQ QQ QQ 1 1 2010-05-05 00:00:00
2 qq qw ww 1 1 2010-05-05 00:00:00
17 test 2 1 2010-08-03 00:00:00
18 asdgd asd asd 1 1 2010-07-30 00:00:00
19 QQ QQ QQ 1 1 2010-05-05 00:00:00
20 QQ QQ QQ 1 1 2010-05-05 00:00:00

tDepartment - Data:


1 t t1 T 1
2 R R2 R 1


tStatus - Data:


1 Active
2 InActive


ok, so now when I add an employee to the table, I basically implement the following:

[AlphaWolfXV]

employeeForm.cpp


#include "employeeform.h"

employeeForm::employeeForm(int id, QWidget *parent)
:QDialog(parent)
{
leNameCode = new QLineEdit;
lblNameCode = new QLabel(tr("Alias"));
lblNameCode->setBuddy(leNameCode);
QHBoxLayout *hblNameCode = new QHBoxLayout();
hblNameCode->addWidget(lblNameCode);
hblNameCode->addWidget(leNameCode);

cbDept = new QComboBox;
lblDept = new QLabel(tr("Department"));
lblDept->setBuddy(cbDept);
QHBoxLayout *hblDept = new QHBoxLayout();
hblDept->addWidget(lblDept);
hblDept->addWidget(cbDept);

leFName = new QLineEdit;
lblFName = new QLabel(tr("First Name:"));
lblFName->setBuddy(leFName);
QHBoxLayout *hblName = new QHBoxLayout();
hblName->addWidget(lblFName);
hblName->addWidget(leFName);
hblName->addSpacing(20);

leLName = new QLineEdit;
lblLName = new QLabel(tr("Last Name:"));
lblLName->setBuddy(leLName);
hblName->addWidget(lblLName);
hblName->addWidget(leLName);

cbStatus = new QComboBox;
lblStatus = new QLabel(tr("Employment Status:"));
lblStatus->setBuddy(cbStatus);
QHBoxLayout *hblStatus = new QHBoxLayout();
hblStatus->addWidget(lblStatus);
hblStatus->addWidget(cbStatus);

deStartDate = new QDateEdit;
deStartDate->setCalendarPopup(true);
QDate today = QDate::currentDate();
deStartDate->setDateRange(today.addDays(-90),today.addDays(90));
deStartDate->setDate(today);
lblStartDate = new QLabel(tr("Start Date:"));
lblStartDate->setBuddy(deStartDate);
QHBoxLayout *hblStartDate = new QHBoxLayout();
hblStartDate->addWidget(lblStartDate);
hblStartDate->addWidget(deStartDate);

bClose = new QPushButton("&Close");

//finally lets set up the model
tableModel = new QSqlRelationalTableModel(this);
tableModel->setTable("tEmployees");
tableModel->setRelation(tEmployee_DepartmentId, QSqlRelation("tDepartment","idtDepartment","DepartmentName"));
tableModel->setRelation(tEmployee_StatusId,QSqlRelation("tStatus","idtStatus","StatusDescription"));
//tableModel->setSort(tEmployee_NameCode,Qt::AscendingOrder);
tableModel->select();

//uses the relation set earlier when setRelation() was called.
//because of this, we cannot just use the table enums to get the model row, they may be different

QSqlTableModel *relationModel = tableModel->relationModel(tEmployee_DepartmentId);
cbDept->setModel(relationModel);
cbDept->setModelColumn(relationModel->fieldIndex("DepartmentName"));//cbDept->setModelColumn(tDepartment_Name);
QSqlTableModel *relationModel_status = tableModel->relationModel(tEmployee_StatusId);
cbStatus->setModel(relationModel_status);
cbStatus->setModelColumn(relationModel_status->fieldIndex("StatusDescription"));//cbStatus->setModelColumn(tStatus_Desc);

//now we can map the records from the database into the form...
mapper = new QDataWidgetMapper(this);
mapper->setSubmitPolicy(QDataWidgetMapper::ManualSubmit);
mapper->setModel(tableModel);
mapper->setItemDelegate(new QSqlRelationalDelegate(this));
mapper->addMapping(leNameCode, tEmployee_NameCode);
mapper->addMapping(leFName,tEmployee_FName);
mapper->addMapping(leLName,tEmployee_LName);
mapper->addMapping(cbDept, tEmployee_DepartmentId);
mapper->addMapping(deStartDate,tEmployee_StartDate);
mapper->addMapping(cbStatus,tEmployee_StatusId);

//finally now if the form was called with a valid id, then populated with that Ids records...
//otherwise just use the first record ...
if(id != -1)
{
for(int row = 0; row < tableModel->rowCount(); ++row)
{
QSqlRecord record = tableModel->record(row);
if(record.value(tEmployee_Id).toInt() == id)
{
mapper->setCurrentIndex(row);
break;
}
}
}
else
mapper->toFirst();

nav = new dbrecordnav(this);
nav->setCurrentRec(mapper->currentIndex()+1);
nav->setTotalRec(tableModel->rowCount());

QVBoxLayout *vblMain = new QVBoxLayout();
vblMain->addLayout(hblNameCode);
vblMain->addLayout(hblDept);
vblMain->addLayout(hblName);
vblMain->addLayout(hblStartDate);
vblMain->addLayout(hblStatus);
vblMain->addWidget(nav);
setLayout(vblMain);

//finally connect statements
connect(nav,SIGNAL(first()),this,SLOT(go2First())) ;
connect(nav,SIGNAL(prev()),this,SLOT(go2Prev()));
connect(nav,SIGNAL(next()),this,SLOT(go2Next()));
connect(nav,SIGNAL(last()),this,SLOT(go2Last()));
connect(nav,SIGNAL(newrec()),this,SLOT(addEmployee ()));
connect(nav,SIGNAL(deleterec()),this,SLOT(deleteEm ployee()));

connect(bClose,SIGNAL(clicked()),this,SLOT(close() ));
}
void employeeForm::go2First()
{
int row = mapper->currentIndex();
mapper->submit();
mapper->setCurrentIndex(row);
mapper->toFirst();
nav->setCurrentRec(mapper->currentIndex()+1);
nav->setTotalRec(tableModel->rowCount());
}
void employeeForm::go2Prev()
{
int row = mapper->currentIndex();
mapper->submit();
mapper->setCurrentIndex(row);
mapper->toPrevious();
nav->setCurrentRec(mapper->currentIndex()+1);
nav->setTotalRec(tableModel->rowCount());
}
void employeeForm::go2Next()
{
int row = mapper->currentIndex();
mapper->submit();
mapper->setCurrentIndex(row);
mapper->toNext();
nav->setCurrentRec(mapper->currentIndex()+1);
nav->setTotalRec(tableModel->rowCount());
}
void employeeForm::go2Last()
{
int row = mapper->currentIndex();
mapper->submit();
mapper->setCurrentIndex(row);
mapper->toLast();
nav->setCurrentRec(mapper->currentIndex()+1);
nav->setTotalRec(tableModel->rowCount());
}
void employeeForm::deleteEmployee()
{
int row = mapper->currentIndex();
tableModel->removeRow(row);
mapper->submit();
mapper->setCurrentIndex(qMin(row,tableModel->rowCount()-1));
nav->setCurrentRec(mapper->currentIndex()+1);
nav->setTotalRec(tableModel->rowCount());
}



void employeeForm::addEmployee()
{
//add records at the end of the list
mapper->toLast();
int row = mapper->currentIndex();
mapper->submit();
tableModel->insertRow(row+1);
mapper->setCurrentIndex(row+1);
//set the nav counts up 1
nav->setCurrentRec(mapper->currentIndex()+1);
nav->setTotalRec(tableModel->rowCount());
leNameCode->clear();
leFName->clear();
leLName->clear();
cbDept->clear();
cbStatus->clear();
deStartDate->setDate(QDate::currentDate());
leNameCode->setFocus();
}


As you can see, I nav to the last record, call submit on the mapper which I thought saved any started record edits (and seems to work), then call insertRow() clear the data fields and set the focus at the control ready to receive the latest information.

If I cancel after the add, without doing any other navigations, the record is not added as it does not meet the null field criterion, i.e. tDepartment_idtDepartment and tStatus_idtStatus are null, therefore can't be added to the database.

If I navigate back to a previous or the first record, however, it basically copies the record data of the record navigated to and saves it as the new record. I am confused by this as on a navigation, the first thing it should do is "submit" the record, which I would have thought that it would not save due to null value constraints...

Can someone please explain how I should handle this navigation issue once the record is added?
Thanks for the help!
AlphaWolfXV

[AlphaWolfXV]

Ok, so I have kind of an update, when I added a qDebug() statement in each of the go2prev(), go2Next(), go2First(), and go2Last() where I look for the last error...

Modifications:


int row = mapper->currentIndex();
mapper->submit();
qDebug()<<"lasterror:"<<tableModel->lastError().text();


Shows the following when I add a new record with no new information and press previous...

lasterror: "Field 'tDepartment_idtDepartment' doesn't have a default value QMYSQL3: Unable to execute statement"

This is perfect, as it shouldn't be able to store a record with part of the fk's missing information. If I close the program at this point, the database is not modified and the information is ignored... However, when I click any other navigation button, it will place the copy of the first previous record as the new record...

It appears as though since the submit failed, it is waiting for the next submit, which uses the current rowindex (a valid record) data, then re-submits and this time accepts because the valid record has valid fk's.

Maybe a more general question: How is adding a record normally done in a form, should I be using a signal/slot or am I just missing a validation check? Or is there a way I should change to OnManualSubmit, and then how would I still be able to validate the data prior to submit? Any thoughts!
Thanks,
AlphaWolfXV

[AlphaWolfXV]

Ok, figured it out... For anyone else who might be experiencing similar issues the fix that I am using is:


void employeeForm::go2First()
{
int row = mapper->currentIndex();
if(! mapper->submit())
{
qDebug()<<"lasterror:"<<tableModel->lastError().text();
tableModel->revertAll();
}
mapper->toFirst();
nav->setCurrentRec(mapper->currentIndex()+1);
nav->setTotalRec(tableModel->rowCount());
}
void employeeForm::go2Prev()
{
int row = mapper->currentIndex();
if(! mapper->submit())
{
qDebug()<<"lasterror:"<<tableModel->lastError().text();
tableModel->revertRow(row);
}
if(row > 0)
row--;
mapper->setCurrentIndex(row);

nav->setCurrentRec(mapper->currentIndex()+1);
nav->setTotalRec(tableModel->rowCount());
}
void employeeForm::go2Next()
{
int row = mapper->currentIndex();
if(!mapper->submit())
{
qDebug()<<"lasterror:"<<tableModel->lastError().text();
tableModel->revertRow(row);
row--;
}

mapper->setCurrentIndex(row);
mapper->toNext();
nav->setCurrentRec(mapper->currentIndex()+1);
nav->setTotalRec(tableModel->rowCount());
}
void employeeForm::go2Last()
{
int row = mapper->currentIndex();
if(!mapper->submit())
{
qDebug()<<"lasterror:"<<tableModel->lastError().text();
tableModel->revertRow(row);
row--;
}
mapper->setCurrentIndex(row);
mapper->toLast();
nav->setCurrentRec(mapper->currentIndex()+1);
nav->setTotalRec(tableModel->rowCount());
}


Basically, just check if the submit failed, if it did, revert the row changes and update the index counter to match... Then just handle the mapper and anything else!
AlphaWolfXV