QStandardItemModel to sqlite database table

[sattu]

hi everyone,
I am trying to insert the data present in a QStandardItemModel.I have to set that data into a table present in sqlite database.I tried like below



QSqlTableModel table(0,db_local);
table.setTable("menu_list");
table.setEditStrategy(QSqlTableModel::OnManualSubm it);
for(i=0;i<model->rowCount();i++)
{
table.insertRow(i);
for(j=0;j<model->columnCount();j++)
ok= table.setData(table.index(i,j),model->data(model->index(i,j)));
}



In QSqlTableModel the required value is present.
But in database nothing is there.
Is there any other statement required to update the table inside database?
plz help me.

thanks

[wysota]

Get rid of line #3 in the above snippet.

[sattu]

thanks for reply.
if i remove the line 3 then table.insertRow(i) returns false.
this things was working for QSqlQUeryModel.

[wysota]

If it returns false then there has to be a reason for it. What exactly are you trying to do?

[sattu]

I have a xml string whoch contain data of a table.I want to insert that data in a table.
so first i inserted all data into QStandardItemModel .Now i have to insert the data into table.
Is there any other way to do this?

[wysota]

So why did you insert it to QStandardItemModel in the first place? Also does your sql table have a primary key defined?

[sattu]

No there is no primary key.Is there any other way to extract data? this is the only way which i know.
If there is any other easy plz suggest me.

[wysota]

If there is no primary key defined then I don't see how you would want to insert rows into the database.

[sattu]

I did not get you.Actually here primary key is not necessary.Is primary key necessary?

[wysota]

If you wish to say "insert me a new row" and then "set data on row X" how should the database know which row is "X"?

[sattu]

ok then how i will do this.plz tel me.

now i tried like below.but same thing.(means not working).



for(i=0;i<model->rowCount();i++)
{
QSqlRecord rec;
for(j=0;j<model->columnCount();j++)
{
QSqlField field;
field.setValue(model->data(model->index(i,j)));
field.setName(model->headerData(j,Qt::Horizontal).toString());;
rec.append(field);
}
ok=table->insertRecord(i,rec);
}

[wysota]

Your table needs to have a primary key defined.

[sattu]

how i set one field to primary key.
is there any function to set ?

[sattu]

I created menu_list table with a primary key.But it isnot working.
Did you want to do this ?

[ChrisW67]

Other reasons for inserts to fail include:

If you don't provide a value and the column in the table has a NOT NULL constraint and no default value.
Inserted values are not valid data for the column types e.g. insert word into integer field.
Inserted values violate check constraints, e.g. insert "X" into column accepting only "Y" or "N".
Inserted values violate unique index constraints, e.g. insert same value twice.
Inserted values violate foreign key constraints e.g. code column not in related code lookup table.
We have no information that would help us narrow this list down.
What is your actual table schema and what is a sample of the data you are trying to insert? Can you execute the same insert manually through the relevant database CLI?

Wysota's question regarding why you used a QStandardItemModel was intended to make you wonder if there was another way. You have all the pieces. Your current process goes:

Read XML file
Insert into QStandardItemModel
User fiddles with the data (I assume this) through a view on the QStandardItemModel
Copy the QStandardItemModel into a QSqlTableModel


Have you considered?

Read XML file
Insert into QSqlTableModel
User fiddles with the data (I assume this) through a view on the QSqlTableModel
No copying to do


Other ways to get data into a table involve writing your own SQL INSERT queries, binding values and executing once per row (QSqlQuery).

[sattu]

hi ChrisW67,
when i am trying to insert row in qsqltable model it returns false.If i will try with
table.setEditStrategy(QSqlTableModel::OnManualSubm it);
then there is no problem with insertion.But in database there is no change in table.

is there any soln ?

[wysota]

If i will try with
table.setEditStrategy(QSqlTableModel::OnManualSubm it);
then there is no problem with insertion.
Because there is no insertion then until you submit the model to the database. Which will fail because you fail to acknowledge that your database schema is incorrect. It seems you do not have sufficient knowledge about relational databases, maybe it's time to learn a bit about them?

[ChrisW67]

is there any soln ?

Yes. You need to look at what Qt's SQL components are going to do with the data you are providing, the actual data you are providing, and then work out why it isn't working. Wysota and I have already given you a list of possible causes and some things to look at that would help you work out your problem. Repeatedly coming back with "It doesn't work, fix it for me" responses and no specifics is not going to help.

Here is a repeat hint: Ultimately your code causes an SQL INSERT with the data you provide. If you cannot manually perform the equivalent insert then there is no way that Qt will be able to either. Manually performing the equivalent insert will tell you what the SQL engine thinks is the problem. This activity has nothing to do with Qt-based code.

[sattu]

Thanks both for help me.
Now it is working,Actually i did not mention the field type and field name .So i am not able to insert.

Can any one told me is there any better way to extract data from xml string ?
I am using QXmlStreamReader for reading data.For this i use a loop to read data.Is there any way by which i can directly convert the xml string to tablemodel or querymodel or any equivalent model ?
thank.

[ChrisW67]

You could write a custom model wrapped around the XML document loaded into a QDomDocument. This is only going to work if the XML document is small enough to fit in RAM and will not be particularly efficient.

However, since your requirement seems to be that the data ends up in a relational table then you might as well read the XML and insert it straight into the table. Then your users view and edit the data from the table.