Record update windowd entered data saving

[wysota]

Does insertRecord() return true?

[MarkoSan]

Yes, I've tried it several times. submitAll also returns true, there is no visible error (in debugger), just empy field is added to QSqlRelationDataModel (not to databse, becasue if I reopen window, the empty field dissapears). I thought it was something wrong with relations, so I commented relation setup, still same result. Do you need whole code listing?

[wysota]

Try this:

QSqlRecord rec;
rec.append(QSqlField("id", QVariant::Int));
rec.append(QSqlField("name", QVariant::String));
rec.append(QSqlField("address", QVariant::String));
rec.setValue("id", lineedit1->text());
rec.setValue("name", lineedit2->text());
rec.setValue("address", lineedit3->text());
      //...
model->insertRecord(-1, rec);

QSqlRecord rec;
rec.append(QSqlField("id", QVariant::Int));
rec.append(QSqlField("name", QVariant::String));
rec.append(QSqlField("address", QVariant::String));
rec.setValue("id", lineedit1->text());
rec.setValue("name", lineedit2->text());
rec.setValue("address", lineedit3->text());
      //...
model->insertRecord(-1, rec);

To copy to clipboard, switch view to plain text mode 

[MarkoSan]

God damn, it works, but still there is no data in the table after reopening the window! Is this problem maybe connected with my databse schematic, because I have an ID field in customer table autonumbered, but I cannot get its value from SQL to Qt?

[jacek]

Try this:

QSqlRecord rec;
rec.append(QSqlField("id", QVariant::Int));
rec.append(QSqlField("name", QVariant::String));
rec.append(QSqlField("address", QVariant::String));

QSqlRecord rec;
rec.append(QSqlField("id", QVariant::Int));
rec.append(QSqlField("name", QVariant::String));
rec.append(QSqlField("address", QVariant::String));

To copy to clipboard, switch view to plain text mode 

I just wonder why didn't we thought about this earlier

A shorter version:

QSqlRecord rec( m_pTableModel->record() );

QSqlRecord rec( m_pTableModel->record() );

To copy to clipboard, switch view to plain text mode 

[MarkoSan]

I just wonder why didn't we thought about this earlier

A shorter version:

QSqlRecord rec( m_pTableModel->record() );

QSqlRecord rec( m_pTableModel->record() );

To copy to clipboard, switch view to plain text mode 

But isn't there a QSqlDatabase connection or some other mechanism, which fetches fields names automaticly?

[MarkoSan]

But isn't there a QSqlDatabase connection or some other mechanism, which fetches fields names automaticly?

Now it works, thank you very very much! But can you please tell me how to fetch ID of CUSTOMER, which is set to AUTONUMBER in SQL Administrator, but it does not work, since If I add record manualy, the ID does not grow!!

[jacek]

But isn't there a QSqlDatabase connection or some other mechanism, which fetches fields names automaticly?

That's what QSqlTableModel::record() does.

can you please tell me how to fetch ID of CUSTOMER, which is set to AUTONUMBER in SQL Administrator, but it does not work, since If I add record manualy, the ID does not grow!!

Try:

rec.setGenerated( "id", false );

rec.setGenerated( "id", false );

To copy to clipboard, switch view to plain text mode 

[MarkoSan]

Ok, I have another question: Why do I get now table filled with new data correctly, but If i reopen windows, the data dissapears, that means, record is still not being written to database?

[jacek]

Did you try QSqlTableModel::submit() or QSqlTableModel::submitAll()?

[MarkoSan]

I checked it out, just wait, now returns false, I'll post the error soon.

[MarkoSan]

I checked it out, just wait, now returns false, I'll post the error soon.

submitAll returns false, further diagnostic:

QSqlError::type returned    StatementError
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}

QSqlError::type returned    StatementError
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 

[wysota]

Ok, I have another question: Why do I get now table filled with new data correctly, but If i reopen windows, the data dissapears, that means, record is still not being written to database?

Is it not written to the database or not displayed in the tableview?


Oh, and guys, try not to use UPPERCASE column names in SQL tables, it's a risk while porting between platforms.

[fane]

Thanks.
1. I have tried

rec.setValue("city", "London");

rec.setValue("city", "London");

To copy to clipboard, switch view to plain text mode 

and

rec.setValue("city", 1) ;

rec.setValue("city", 1) ;

To copy to clipboard, switch view to plain text mode 

with the same result -- record added to database but 'city' field value == 0.

2 - 3. I'll go for them later, I do not have Qt4 right now.

Is my problem similar to MarkoSan's or am I wrong?

[fane]

I have done some more "research" and this is what I have:
rec.field("city").type() returns QVariant::Invalid - that's because rec does not contain a field named "city".
rec.field("city_name").type() returns QVariant::String which is correct as long the database field city_name is a VARCHAR.

Using rec.setValue("city", "London") apparently works because QSqlRecord::setValue(const QString & name, const QVariant & val) does nothing if the field ("city" in this case) does not exist. So the record is inserted in the database using the default value (0) for the field "city_name" which is contained by the record rec. In this case why rec.setValue("city_name", "London") doesn't work and model->insertRecord(-1, rec) returns FALSE?
I have also found something that looks strange, at least for me. I run in MySQL QueryBrowser the query upon which the QSqlRelationalTableModel model is filled with data and a saw that I can't insert or update records in the resultset returned.

I think I have to dig deeper into this model/view architecture. For now I will try a different approach and use a QSqlQuery("INSERT INTO ...") to make things going on, but the topic still remains opened.

[wysota]

You might want to ask Trolltech about it, it might be some kind of bug or lack of documentation.

[MarkoSan]

I know why submitAll is not working for me. I've set up foreign keys, but I had not done the JOIN statement, I'll try it now and report the result.

[wysota]

I just wonder why didn't we thought about this earlier

Because we didn't read QSqlRecord docs carefully enough (I didn't read it at all, just looked at it).

[fane]

I have been watching this thread since its beginnings because I am stuck with the same problem. Look at this scenario:
Two database tables

CUSTOMERS
customer_id INTEGER AUTOINCREMENT NOT NULL
name VARCHAR
city INTEGER NOT NULL DEFAULT 0

CITIES
city_id INTEGER AUTOINCREMENT NOT NULL
city_name VARCHAR

A model:

model = new QSqlRelationalTableModel(this);
model->setTable("customers");
model->setRelation(2, QSqlRelation("cities", "city_id", "city_name"));
model->select();

model = new QSqlRelationalTableModel(this);
model->setTable("customers");
model->setRelation(2, QSqlRelation("cities", "city_id", "city_name"));
model->select();

To copy to clipboard, switch view to plain text mode 

"this" beeing the form on which there is a QTableView:

ui.tableView->setModel(model);

ui.tableView->setModel(model);

To copy to clipboard, switch view to plain text mode 

A QPushButton insertButton and a custom slot:

void MainWindow::on_insertButton_clicked(bool checked)
{
	QSqlRecord rec = model->record();
 
	rec.setValue("name", "Test Customer");
	rec.setValue("city_name", "London");
 
	bool insert = model->insertRecord(-1, rec);
	bool submit = model->submit();
}

void MainWindow::on_insertButton_clicked(bool checked)
{
	QSqlRecord rec = model->record();
	
	rec.setValue("name", "Test Customer");
	rec.setValue("city_name", "London");

	bool insert = model->insertRecord(-1, rec);
	bool submit = model->submit();
}

To copy to clipboard, switch view to plain text mode 

The select statement upon which the model is created is like this:
"SELECT customers.customer_id, customers.name, cities.city_name
FROM customers, cities
WHERE customers.city = cities.city_id"
This is the return of model->query().lastQuery().

The QSqlRecord rec from above have the same fields: customer_id, name, city_name, which is obvious.
BUT if you run the code like this both insertRecord() and submit() return FALSE and the last error reported by model->lastError().text() is:
"Unknown column 'city_name' in 'field list' QMYSQL3 : QMYSQLResult". Surprisingly in the tableView there is a new row which contain the values used by the setRecord() calls.
You get the same result if you use a numerical value with rec.setValue() in line 6:

rec.setValue("city_name", 1);

rec.setValue("city_name", 1);

To copy to clipboard, switch view to plain text mode 

QSqlTableModel::insertRecord() calls internally QSqlTableModel::insertRows() and QSqlTableModel::setRecord(). The new row is shown in the tableView and that means that only insertRows() worked but not setRecord().

Now change this line (6) to be like this:

rec.setValue("city", 1);

rec.setValue("city", 1);

To copy to clipboard, switch view to plain text mode 

Both insertRecord() and submit() return TRUE, the record is added in the database (I have checked with MySQL Query Browser) but the field "city" is set to 0, which is the default value for the field and model->lastError().text() is empty. The record isn't shown in the tableView because there is no corresponding record in the related table "cities" to match the WHERE clause 'customers.city = cities.city_id'.

So the first way is not good (it generates SQL errors and no record added) the second one seems also not to be working (no SQL error, record added, but with a useless value in the field upon the relation is set). Until now I couldn't narrow it more than this. Which way??

[wysota]

The behaviour is (probably) because you can't insert into two tables with a single insert statement. Using this model you can only insert into the table which you set to be the model table ("customers" in your case). That's why you have to reference "city" and not "city_name".

Now we come to the second part -- the Problem(R).

I would try two things:

1.

rec.setValue("city", "London");

rec.setValue("city", "London");

To copy to clipboard, switch view to plain text mode 

2.

rec.setGenerated("city", true);

rec.setGenerated("city", true);

To copy to clipboard, switch view to plain text mode 

A third thing to do could be to check what is the type of QVariant associated with "city" field (using rec.field().type()).