QSQLITE Questions

[larry104]

Hi,

Im playing with sql and found some strange things :confused:



mSqlModel.setTable("test");
mSqlModel.setEditStrategy(QSqlTableModel::OnManual Submit);
mSqlModel.select();

for(int row=0;row<500;row++)
{
QSqlRecord record = mSqlModel.record();
record.setValue("str1",QVariant("blah"));
record.setValue("value",QVariant(row));
record.setValue("str2",QVariant("123abc"));
mSqlModel.insertRecord(-1,record);
if(!mSqlModel.submitAll())
std::cout << "Can't add row=" << std::endl;
}


Maybe someone can help me here:
- mSqlModel.submitAll() returns always false
- If I run the code twice or more I only get 756 records (500 through the first run).
- If I run that in a seperate thread I get max 256 records
- if I use
mSqlModel.insertRecord(row,record); I get sometimes only 256 records added

Can someone tell me what I'm doing wrong here?
Thanks a lot.

[jacek]

Could you prepare a compilable example, so we can test it on our systems?

[larry104]

Here it is:

It looks like the model can't hold more than 256 records. Something strange is going on :confused:

Run it first as is and them comment out
mSqlModel.setEditStrategy(QSqlTableModel::OnManual Submit); and the submitAll line

and see the difference.



#include <QApplication>
#include <QTranslator>
#include <QMessageBox>
#include <QSqlError>
#include <QSqlRecord>
#include <iostream>
#include <QtDebug>
#include <QSqlQuery>
#include <QSqlTableModel>


int main(int argc, char *argv[])
{
QApplication app(argc, argv);

// Set up the database connection
QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE");
database.setDatabaseName(":memory:");
if(!database.open())
{
QMessageBox::critical(0, QObject::tr("Error"),
QObject::tr("Could not open database; driver said: %1, database said: %2")
.arg(database.lastError().driverText())
.arg( database.lastError().databaseText()));
return -1;
}

QSqlTableModel mSqlModel;
QSqlQuery query;
if(!query.exec("CREATE TABLE test ("
"id INTEGER PRIMARY KEY,"
"str VARCHAR(100))"))
{
qDebug() << "Could not create table; driver said: "
<< database.lastError().driverText() << ", database said: "
<< database.lastError().databaseText();
return -2;
}

mSqlModel.setTable("test");
mSqlModel.setEditStrategy(QSqlTableModel::OnManual Submit);
mSqlModel.select();
std::cout << "ROWS=" << mSqlModel.rowCount() << std::endl;

for(int row=0;row<5000;row++)
{
QSqlRecord record = mSqlModel.record();
record.setValue("str",QVariant("123abc"));
mSqlModel.insertRecord(-1,record);
}

std::cout << "ROWS=" << mSqlModel.rowCount() << std::endl;
if(!mSqlModel.submitAll())
std::cout << "Can't add row=" << std::endl;
std::cout << "ROWS=" << mSqlModel.rowCount() << std::endl;
return -1;
}





TEMPLATE = app
TARGET +=
DEPENDPATH += .
INCLUDEPATH += .

QT += xml sql network

# Input RESOURCES +=
HEADERS += \

SOURCES += \
main.cpp \

FORMS += \

[jacek]

Quite interesting ;)

Update your example:

...
#include <QSqlDriver>
#include <QTableView>
...
std::cout << "ROWS=" << mSqlModel.rowCount() << std::endl;
std::cout << "FM=" << mSqlModel.canFetchMore() << std::endl;
if(!mSqlModel.submitAll())
std::cout << "Can't add row=" << std::endl;
std::cout << "ROWS=" << mSqlModel.rowCount() << std::endl;
std::cout << "FM=" << mSqlModel.canFetchMore() << std::endl;
std::cout << "QS=" << QSqlDatabase().driver()->hasFeature( QSqlDriver::QuerySize ) << std::endl;

QTableView v;
v.setModel( &mSqlModel );
v.show();
return app.exec();
}
and try to scroll down the view.

It looks like SQLite doesn't provide information about the number of rows returned by the query, so QSqlTableModel loads only 256 of them and fetches the rest when needed.

[larry104]

If I scroll down the view the last row I see is 257 - and this row is empty. Can you confirm that?

Here is something else: If I use


QSqlQuery query;
query.prepare("INSERT INTO test (str) "
"VALUES (:str)");
query.bindValue(":str",QVariant("abc123"));
query.exec();


to add data into the table and the model is defined before that



QSqlTableModel mSqlModel;
mSqlModel.setTable("test");


then the model is empty and stays empty. Is the definition after the data was written into the datatable the model shows all data. It looks like the driver does not tell the model that the data table has been changed.

[jacek]

If I scroll down the view the last row I see is 257 - and this row is empty. Can you confirm that?
No, I can see all 5000 rows using both Qt 4.1.5 and 4.2.1.


It looks like the driver does not tell the model that the data table has been changed.
Yes and actually I've never heard about a database that does it. You have to invoke QSqlTableModel::select() to get new data.

[larry104]

No, I can see all 5000 rows using both Qt 4.1.5 and 4.2.1


Hmm - Im using 4.2.0 /Linux/RHEL3.0. Let me install 4.2.1 next week.
Thanks a lot.

[jacek]

Im using 4.2.0 /Linux/RHEL3.0. Let me install 4.2.1 next week.
AFAIK there shouldn't be any difference in this matter between Qt 4.2.0 and 4.2.1. Maybe the problem lies in SQLite?

[larry104]

So I installed 4.2.1 and it works fine now (on windows as well as linux).

[Roger]

I am using QT 4.2.3 and have a Data entry form developed with designer.
I am using a tableView, widgetMapper, and proxy model.

SQLite locks the database and never makes the first update.

void EntryDialog::submit()
{
lineEditStatus->setText("I");

QSqlDatabase iolta = QSqlDatabase::database();
QSqlQuery updateInput(iolta);
QString updateString;
updateString = " update transinput set "
" status = :status,"
" rate = :rate,"
" earningstart = :earningstart,"
" earningend = :earningend,"
" gross = :gross,"
" maint = :maint,"
" activity = :activity,"
" handling = :handling,"
" net = :net"
" where transinput.banknum = :banknum"
" and transinput.trustaccount = :trustaccount ";
updateInput.prepare(updateString);
updateInput.bindValue(":status",lineEditStatus->text().trimmed());
updateInput.bindValue(":rate",lineEditRate->text().toDouble());
updateInput.bindValue(":earningstart",QDate::fromString(lineEditEarningStart->text(),"yyyy-MM-dd"));
updateInput.bindValue(":earningend",QDate::fromString(lineEditEarningEnd->text(),"yyyy-MM-dd"));
updateInput.bindValue(":gross",lineEditGross->text().toDouble());
updateInput.bindValue(":maint",lineEditMaint->text().toDouble());
updateInput.bindValue(":activity",lineEditActivity->text().toDouble());
updateInput.bindValue(":handling",lineEditHandling->text().toDouble());
updateInput.bindValue(":net",lineEditNet->text().toDouble());
updateInput.bindValue(":banknum",lineEditBankNumber->text());
updateInput.bindValue(":trustaccount",lineEditTrustAccount->text());

if (! updateInput.exec())
{
QMessageBox::critical(0, qApp->tr("Update Failed"),
updateInput.lastError().text()+"\n"+updateString, QMessageBox::Cancel);
return;
};