Record update windowd entered data saving

[MarkoSan]

So, am I nut or what? This time submitAll failed and first qDebug() we have

QSqlQueryModel::lastError returned {...}
QSqlError::text returned {" " size=1}

QSqlQueryModel::lastError returned {...}
QSqlError::text returned {" " size=1}

To copy to clipboard, switch view to plain text mode 

and after second qDebug() we have:

QSqlQueryModel::query returned	{...}
QSqlQuery::lastQuery returned	{"SELECT stranka.SIFRA,stranka.DS,stranka.PRIIMEK,stranka.IME,ulica.naziv,stranka.STEVILKA_ULICE,kraj.naziv_kraja FROM stranka,u" size=217}

QSqlQueryModel::query returned	{...}
QSqlQuery::lastQuery returned	{"SELECT stranka.SIFRA,stranka.DS,stranka.PRIIMEK,stranka.IME,ulica.naziv,stranka.STEVILKA_ULICE,kraj.naziv_kraja FROM stranka,u" size=217}

To copy to clipboard, switch view to plain text mode 

What is going on?

[jacek]

What is going on?

Indeed something strange is going on. What does QSqlError::type() return?

[MarkoSan]

Ok, this is diagnostic code:

if(m_pTableModel->submitAll())
{
     qDebug() << "Data submitted successfully";
} else 
{
     qDebug() << m_pTableModel->lastError().text();
     qDebug() << m_pTableModel->lastError().type();
     qDebug() << m_pTableModel->query().lastQuery();
}

if(m_pTableModel->submitAll())
{
     qDebug() << "Data submitted successfully";
} else 
{
     qDebug() << m_pTableModel->lastError().text();
     qDebug() << m_pTableModel->lastError().type();
     qDebug() << m_pTableModel->query().lastQuery();
}

To copy to clipboard, switch view to plain text mode 

This is very very strange:

QSqlError::type returned    NoError

QSqlError::type returned    NoError

To copy to clipboard, switch view to plain text mode 

NoError has been reported!!! How come???

[wysota]

SELECT stranka.SIFRA,stranka.DS,stranka.PRIIMEK,stranka.I ME,ulica.naziv,stranka.STEVILKA_ULICE,kraj.naziv_kraja FROM stranka,u

This stinks... What is the table "kraj" doing here if it's not in the "FROM" part of the query? On the other hand, looks like "u" is not used here, so why is it in the statement? Is that a complete output from lastQuery? Is this a SqlTableModel or QSqlRelationalTableModel? How come SqlTableModel has more than one table in use? Is that last query connected with the model at all?

[MarkoSan]

This stinks... What is the table "kraj" doing here if it's not in the "FROM" part of the query? On the other hand, looks like "u" is not used here, so why is it in the statement? Is that a complete output from lastQuery? Is this a SqlTableModel or QSqlRelationalTableModel? How come SqlTableModel has more than one table in use? Is that last query connected with the model at all?

You are right. Does submitAll() generated any SQL statement? The reason I ask is that that statement is responsible for displaying data in QSqlRelationalTableModel and it works fine and lastQuery after sumbitAll still reports that statement, which is obviously not statement for inserting records.

The statement that I get from debuger is:

QSqlQuery::lastQuery returned    {"SELECT stranka.SIFRA,stranka.DS,stranka.PRIIMEK,stranka.IME,ulica.naziv,stranka.STEVILKA_ULICE,kraj.naziv_kraja FROM stranka,u" size=217}

QSqlQuery::lastQuery returned    {"SELECT stranka.SIFRA,stranka.DS,stranka.PRIIMEK,stranka.IME,ulica.naziv,stranka.STEVILKA_ULICE,kraj.naziv_kraja FROM stranka,u" size=217}

To copy to clipboard, switch view to plain text mode 

[GreyGeek]

I do something like what is shown below, but if you have an updatable view (using multiple tables) on your database then something similar to this snippet should work.

void homestead::UpdateProperty() {
	QDateTime dtNow = QDateTime::currentDateTime();
	QString propQryPrep = "UPDATE property_";
	propQryPrep.append(this->dbYear);
	propQryPrep.append(" SET \
					   county     = :county, \
					   cntyname   = :cntyname, \
					   txdistrict = :txdistrict, \
					   legal      = :legal, \
					   parcel_id  = :parcel_id, \
					   pvalue     = :pvalue, \
					   entry_id   = :entry_id, \
					   entry_date = :entry_date, \
					   notes      = :notes \
					   WHERE proprty_id = :proprty_id");
 
	propQry.prepare(propQryPrep);
	propQry.bindValue(":county",ui.leCountyNumber->text().toInt());
	propQry.bindValue(":cntyname",ui.cboCountyName->currentText());
	propQry.bindValue(":txdistrict",ui.leTaxDistrict->text());
	propQry.bindValue(":legal",ui.txtLegal->toPlainText());
	propQry.bindValue(":parcel_id",ui.leParcelID->text());
	propQry.bindValue(":pvalue",ui.leHomeValue->text().toInt());
	propQry.bindValue(":entry_id",homestead::RevID);
	propQry.bindValue(":entry_date",dtNow);
	propQry.bindValue(":notes",ui.teNotes->toPlainText());
	propQry.bindValue(":proprty_id",ui.leProprtyID->text().toInt());
	if (propQry.exec()) {
		ui.leStatus->setText("Property record: "+ui.leProprtyID->text()+" updated!");
	} else {
		ui.leStatus->setText("Cannot updated property record: "+ui.leProprtyID->text());
	}
}

void homestead::UpdateProperty() {
	QDateTime dtNow = QDateTime::currentDateTime();
	QString propQryPrep = "UPDATE property_";
	propQryPrep.append(this->dbYear);
	propQryPrep.append(" SET \
					   county     = :county, \
					   cntyname   = :cntyname, \
					   txdistrict = :txdistrict, \
					   legal      = :legal, \
					   parcel_id  = :parcel_id, \
					   pvalue     = :pvalue, \
					   entry_id   = :entry_id, \
					   entry_date = :entry_date, \
					   notes      = :notes \
					   WHERE proprty_id = :proprty_id");

	propQry.prepare(propQryPrep);
	propQry.bindValue(":county",ui.leCountyNumber->text().toInt());
	propQry.bindValue(":cntyname",ui.cboCountyName->currentText());
	propQry.bindValue(":txdistrict",ui.leTaxDistrict->text());
	propQry.bindValue(":legal",ui.txtLegal->toPlainText());
	propQry.bindValue(":parcel_id",ui.leParcelID->text());
	propQry.bindValue(":pvalue",ui.leHomeValue->text().toInt());
	propQry.bindValue(":entry_id",homestead::RevID);
	propQry.bindValue(":entry_date",dtNow);
	propQry.bindValue(":notes",ui.teNotes->toPlainText());
	propQry.bindValue(":proprty_id",ui.leProprtyID->text().toInt());
	if (propQry.exec()) {
		ui.leStatus->setText("Property record: "+ui.leProprtyID->text()+" updated!");
	} else {
		ui.leStatus->setText("Cannot updated property record: "+ui.leProprtyID->text());
	}
}

To copy to clipboard, switch view to plain text mode 

BTW, this code works on Oracle and on PostgreSQL before 8.1.0. On PostgreSQL after 8.0 they've added 3 millisecond positions to the QDateTime return.

[fane]

I have found a solution to my problem. I have appended a QSqlField to the record retrieved with model->record()

QSqlRecord rec = model->record();
QSqlField cityField("city", QVariant::Int);
rec.append(cityField);
rec.setValue("name", "Test Customer");
rec.setValue("city", 1); // or whatever value exists in the related table 'cities'
 
model->insertRecord(-1, rec);
model->submit();

QSqlRecord rec = model->record();
QSqlField cityField("city", QVariant::Int);
rec.append(cityField);
rec.setValue("name", "Test Customer");
rec.setValue("city", 1); // or whatever value exists in the related table 'cities'

model->insertRecord(-1, rec);
model->submit();

To copy to clipboard, switch view to plain text mode 

'city' is the name of a field in the 'customers' database table but which is not part of the query that filled the model.
'city_name' is the name of a field in the related table 'cities' and is contained by rec.
I run this code and I got the same "error" - record inserted in the table 'customers' but the value of 'city' field is set to the default == 0. Then I have installed Qt 4.1.0 and magically it worked - record inserted with proper value for field 'city' == 1 (in this case).
So, as wysota said, it is probably a bug in Qt 4.0.0.
Thanks for all suggestions.

[MarkoSan]

Whatever I do, I cannot get data into database!!!! I am going mad!!!

[wysota]

Whatever I do, I cannot get data into database!!!! I am going mad!!!

Please provide a minimal compilable example which reproduces the problem. Your code is too complex (and incomplete) for us to test on our own machines.

[MarkoSan]

Please provide a minimal compilable example which reproduces the problem. Your code is too complex (and incomplete) for us to test on our own machines.

I tried, but zip file is too big and i do not know how to distribure sql databse.

[MarkoSan]

Is the situation maybe connected with http://www.trolltech.com/developer/t...entry&id=86645, what do you think?

[wysota]

I tried, but zip file is too big and i do not know how to distribure sql databse.

So make it smaller. The example is to be minimal. We don't need your database, just a table schema and some sample data.

[MarkoSan]

Here is minimal project file and sources (VC++ 6.0 Service Pack 6, Qt 4.0.1). After unziping in .sql file there are table declarations and sample data.

[fane]

I have managed to make it work but I use Qt 4.1.0 on WinXp and MySQL 4.1.11. Similar code didn't work on Qt 4.0.0, but I don't know about 4.0.1. My modifications are commented with 'modified by fane' (hope I didn't miss anyone), and of course your MySQL root password has to be changed.
I have appended two QSqlFields to the record retrieved with model->record() named "ULICA" and "POSTA_STEVILKA", set their value with QSqlRecord::setValue(). I have modified the edit strategy to be OnRowChange and removed the setValue() call for the "SIFRA" field which is autoincremented by MySQL.
I guess you have to move to Qt 4.1.0 according to this