Problem of closing database

[cooper]

Hi ChrisW67, thanks for your suggestion.

Comment out the PRAGMA and see if the behaviour changes.

it does not make any different.

Does the table have a primary key?

yes, it has a primary key. here is my code

QDate sDate = QDate::currentDate();
    QString dbName;
    dbName = QString(QApplication::applicationDirPath()).append("/db/I-" + sDate.toString("MMyyyy") + ".db");    
    mydb = QSqlDatabase::addDatabase("QSQLITE");
    mydb.setDatabaseName(dbName);
 
    if (!QFile::exists(dbName))
    {
        mydb.open();
        QSqlQuery query;
        query.exec("create table cycleParams (ID int primary key unique, "
                   "CNo int unique, DNo int, CName QString, "
                   "TSet int, TiSet int, DSet int, STime QString,"
                   "PTime QString, CycleTime QString, Status int, Spare QByteArray)");
 
        mydb.close();
    }

QDate sDate = QDate::currentDate();
    QString dbName;
    dbName = QString(QApplication::applicationDirPath()).append("/db/I-" + sDate.toString("MMyyyy") + ".db");    
    mydb = QSqlDatabase::addDatabase("QSQLITE");
    mydb.setDatabaseName(dbName);

    if (!QFile::exists(dbName))
    {
        mydb.open();
        QSqlQuery query;
        query.exec("create table cycleParams (ID int primary key unique, "
                   "CNo int unique, DNo int, CName QString, "
                   "TSet int, TiSet int, DSet int, STime QString,"
                   "PTime QString, CycleTime QString, Status int, Spare QByteArray)");

        mydb.close();
    }

To copy to clipboard, switch view to plain text mode 

If you do not set the value at all what is in the column?

ok, i modified my code a little bit. and debugged through my program.
i did not initial two parameters at the beginning:

QSqlQuery query;
        //query.exec("PRAGMA synchronous = OFF");
        model = new QSqlTableModel(this);
        model->setEditStrategy(QSqlTableModel::OnManualSubmit);
 
        model->setTable("c1");
        model->select();
        model->insertRow(0);
        model->setData(model->index(0, 1), gCNumber);
        model->setData(model->index(0, 2), gDNumber);
        model->setData(model->index(0, 3), gCName);
        model->setData(model->index(0, 4), gSTemp);
        model->setData(model->index(0, 5), gSTime);
        model->setData(model->index(0, 6), gDTime);
        model->setData(model->index(0, 7), QString(qdatetime.toString(Qt::ISODate)));
        model->setData(model->index(0, 8), QString(qdatetime.toString(Qt::ISODate)));
        //model->setData(model->index(0, 9), 0);
        //model->setData(model->index(0, 10), 0);
        model->setData(model->index(0, 11), "");
        model->submitAll();

QSqlQuery query;
        //query.exec("PRAGMA synchronous = OFF");
        model = new QSqlTableModel(this);
        model->setEditStrategy(QSqlTableModel::OnManualSubmit);

        model->setTable("c1");
        model->select();
        model->insertRow(0);
        model->setData(model->index(0, 1), gCNumber);
        model->setData(model->index(0, 2), gDNumber);
        model->setData(model->index(0, 3), gCName);
        model->setData(model->index(0, 4), gSTemp);
        model->setData(model->index(0, 5), gSTime);
        model->setData(model->index(0, 6), gDTime);
        model->setData(model->index(0, 7), QString(qdatetime.toString(Qt::ISODate)));
        model->setData(model->index(0, 8), QString(qdatetime.toString(Qt::ISODate)));
        //model->setData(model->index(0, 9), 0);
        //model->setData(model->index(0, 10), 0);
        model->setData(model->index(0, 11), "");
        model->submitAll();

To copy to clipboard, switch view to plain text mode 

then, i got a blank value
d1.png

after i submitted values:

model->setTable("c1");
        model->select();
        int row = model->rowCount() - 1;
        model->setData(model->index(row, 10), gErrorCode);
        model->setData(model->index(row, 9), gTotalCycleTime);
        model->submitAll();

model->setTable("c1");
        model->select();
        int row = model->rowCount() - 1;
        model->setData(model->index(row, 10), gErrorCode);
        model->setData(model->index(row, 9), gTotalCycleTime);
        model->submitAll();

To copy to clipboard, switch view to plain text mode 

all past records in the same column have been changed.
d2.png

[ChrisW67]

yes, it has a primary key. here is my code

if (!QFile::exists(dbName))
    {
        mydb.open();
        QSqlQuery query;
        query.exec("create table cycleParams (ID int primary key unique, "
                   "CNo int unique, DNo int, CName QString, "
                   "TSet int, TiSet int, DSet int, STime QString,"
                   "PTime QString, CycleTime QString, Status int, Spare QByteArray)");
 
        mydb.close();
    }

if (!QFile::exists(dbName))
    {
        mydb.open();
        QSqlQuery query;
        query.exec("create table cycleParams (ID int primary key unique, "
                   "CNo int unique, DNo int, CName QString, "
                   "TSet int, TiSet int, DSet int, STime QString,"
                   "PTime QString, CycleTime QString, Status int, Spare QByteArray)");

        mydb.close();
    }

To copy to clipboard, switch view to plain text mode 

The data types of the columns in your Sqlite database are not Qt datatypes (i.e. QString, QByteArray). This is only working because Sqlite does not enforce data types. See http://www.sqlite.org/datatype3.html.

You are allowing NULL into the column you have labelled " int primary key unique". This is not good given that you then insertRow() and do not provide a value for the ID column (column 0). Your ID column will be NULL. If you were expecting Sqlite to provide a unique integer key then the table should be defined:

create table cycleParams (
  ID INTEGER PRIMARY KEY,  -- literally this (case insensitive)
  CNo int unique, DNo int,  
  CName varchar(100), 
  TSet int, TiSet int, DSet int, STime varchar(20),
  PTime varchar(20), CycleTime varchar(20), Status int, Spare blob)

create table cycleParams (
  ID INTEGER PRIMARY KEY,  -- literally this (case insensitive)
  CNo int unique, DNo int,  
  CName varchar(100), 
  TSet int, TiSet int, DSet int, STime varchar(20),
  PTime varchar(20), CycleTime varchar(20), Status int, Spare blob)

To copy to clipboard, switch view to plain text mode 

If the ID number must be montonically increasing then use "INTEGER PRIMARY KEY AUTOINCREMENT". See http://www.sqlite.org/lang_createtable.html#rowid and http://www.sqlite.org/autoinc.html

Fixing the key will go some way to solving this problem.

after i submitted values:

model->setTable("c1");
        model->select();
        int row = model->rowCount() - 1;
        model->setData(model->index(row, 10), gErrorCode);
        model->setData(model->index(row, 9), gTotalCycleTime);
        model->submitAll();

model->setTable("c1");
        model->select();
        int row = model->rowCount() - 1;
        model->setData(model->index(row, 10), gErrorCode);
        model->setData(model->index(row, 9), gTotalCycleTime);
        model->submitAll();

To copy to clipboard, switch view to plain text mode 

all past records in the same column have been changed.

I assume c1 and cycleParams are the same table.
Why do you reset the model table and reselect here rather than just using the same, already established, model?
Since I expect there to be many rows in this table you should make sure that all have been fetched into the model:

model->select();
while (model->canFetchMore())
  model->fetchMore();
...

model->select();
while (model->canFetchMore())
  model->fetchMore();
...

To copy to clipboard, switch view to plain text mode 

An alternate approach would be to simply use a query to update the 'last' row (by whatever definition last is judged).

[cooper]

Solved.
After i replace primary key id to be ID INTEGER PRIMARY KEY, it works perfectly.

Thanks chrisw67. I really appreciate the time you have taken to point me to the right direction.