QSQLITE Questions

[larry104]

Hi,

Im playing with sql and found some strange things

mSqlModel.setTable("test");
    mSqlModel.setEditStrategy(QSqlTableModel::OnManualSubmit);
    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;
    }

mSqlModel.setTable("test");
    mSqlModel.setEditStrategy(QSqlTableModel::OnManualSubmit);
    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;
    }

To copy to clipboard, switch view to plain text mode 

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);

mSqlModel.insertRecord(row,record);

To copy to clipboard, switch view to plain text mode 

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

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::OnManualSubmit);
   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;
}

#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::OnManualSubmit);
   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;
}

To copy to clipboard, switch view to plain text mode 

TEMPLATE = app
TARGET +=
DEPENDPATH += .
INCLUDEPATH += .
 
QT += xml sql network
 
# Input RESOURCES += 
HEADERS   += \
 
SOURCES   += \
             main.cpp \
 
FORMS     += \

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

QT += xml sql network

# Input RESOURCES += 
HEADERS   += \

SOURCES   += \
             main.cpp \

FORMS     += \

To copy to clipboard, switch view to plain text mode 

[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();
}

...
#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();
}

To copy to clipboard, switch view to plain text mode 

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();

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

To copy to clipboard, switch view to plain text mode 

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

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

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

To copy to clipboard, switch view to plain text mode 

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::fromS tring(lineEditEarningStart->text(),"yyyy-MM-dd"));
updateInput.bindValue(":earningend",QDate::fromStr ing(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",lineEditBankNumbe r->text());
updateInput.bindValue(":trustaccount",lineEditTrus tAccount->text());

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