Problem of closing database

[cooper]

Hi everyone,

My small app has a button to start recording data into a database. A timer event happened every 1 second. My program write data to a database every second after the 'start' button has been clicked. A 'stop' button to stop recording data.

my problem is that after I click 'stop' button, and click 'start' again, i got a warning message from Qt creator:

"QSqlDatabasePrivate::removeDatabase: connection 'qt_sql_default_connection' is still in use, all queries will cease to work.
QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed."

I had a look the 'QSqlDatabase' class. the message indicates that a query on the database connection is still opened.

can anyone tell me how to stop the query in my program, please?
Thanks in advance.
here is part of my code:

void cycleScrDialog::on_pushButton_clicked()
{
    // create new database
    QDate sDate = QDate::currentDate();
    QString dbName;
    dbName = QString(QApplication::applicationDirPath()).append("/I-" + sDate.toString("MMyyyy") + ".db");
    m4db = QSqlDatabase::addDatabase("QSQLITE");
    m4db.setDatabaseName(dbName);
 
    if (!QFile::exists(dbName))
    {
        mydb.open();
        QSqlQuery query;
        query.exec("create table c1(ID int primary key unique, "
                       "StartTime QString, StopTime QString)");
        mydb.close();
    }
 
    if (!mydb.open())
    {
        QMessageBox::warning(this, tr("Unable to open database"), tr("An error occurred while "
                                      "opening the connection: ") + m4db.lastError().text());
        return;
    }
    else
    {
        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), String(qdatetime.toString(Qt::ISODate)));
        model->setData(model->index(0, 2), String(qdatetime.toString(Qt::ISODate)));
        model->submitAll();
    }
 
    saveIni();
    model->setTable("c1");
    model->filter();
    model->select();
}
 
void cycleScrDialog::timer1000Event()
{
        model->setTable("c1");
        model->select();
        model->insertRow(0);
        model->setData(model->index(0, 1), String(qdatetime.toString(Qt::ISODate)));
        model->setData(model->index(0, 2), String(qdatetime.toString(Qt::ISODate)));
        model->submitAll();
}

void cycleScrDialog::on_pushButton_clicked()
{
    // create new database
    QDate sDate = QDate::currentDate();
    QString dbName;
    dbName = QString(QApplication::applicationDirPath()).append("/I-" + sDate.toString("MMyyyy") + ".db");
    m4db = QSqlDatabase::addDatabase("QSQLITE");
    m4db.setDatabaseName(dbName);

    if (!QFile::exists(dbName))
    {
        mydb.open();
        QSqlQuery query;
        query.exec("create table c1(ID int primary key unique, "
                       "StartTime QString, StopTime QString)");
        mydb.close();
    }

    if (!mydb.open())
    {
        QMessageBox::warning(this, tr("Unable to open database"), tr("An error occurred while "
                                      "opening the connection: ") + m4db.lastError().text());
        return;
    }
    else
    {
        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), String(qdatetime.toString(Qt::ISODate)));
        model->setData(model->index(0, 2), String(qdatetime.toString(Qt::ISODate)));
        model->submitAll();
    }

    saveIni();
    model->setTable("c1");
    model->filter();
    model->select();
}

void cycleScrDialog::timer1000Event()
{
        model->setTable("c1");
        model->select();
        model->insertRow(0);
        model->setData(model->index(0, 1), String(qdatetime.toString(Qt::ISODate)));
        model->setData(model->index(0, 2), String(qdatetime.toString(Qt::ISODate)));
        model->submitAll();
}

To copy to clipboard, switch view to plain text mode 

[Lykurg]

Your example is incomplete, but I guess you have to delete model first.

[ChrisW67]

All objects that depend on the database connection must have been destroyed or disconnected (in some cases a clear() method exists). If you don't use the default connection name then you can simply call removeDatabase() with your known connection name. If you want to remove the default connection:

QString conName;
{
  QSqlDatabase db = QSqlDatabase::database();
  conName = db.connectionName();
}
QSqlDatabase::removeDatabase(conName);

QString conName;
{
  QSqlDatabase db = QSqlDatabase::database();
  conName = db.connectionName();
}
QSqlDatabase::removeDatabase(conName);

To copy to clipboard, switch view to plain text mode 

The braces ensure that db is destroyed before the removeDatabase() call otherwise the warning persists.

[cooper]

Note:
in my code, the database “m4db" should be "mydb", sorry about my typing mistake.

Hi Lykurg,

Which part of my code is incomplete?
if you mentioned about closing database, i add it as below.

To delete model, do i use model->clear() command?


Hi ChrisW67,

I close my database when i click "stop" button.

// stop button
void cycleScrDialog::on_pushButton_2_clicked()
{
    mydb.close();
    QSqlDatabase::removeDatabase("QSQLITE");
}

// stop button
void cycleScrDialog::on_pushButton_2_clicked()
{
    mydb.close();
    QSqlDatabase::removeDatabase("QSQLITE");
}

To copy to clipboard, switch view to plain text mode 

Thanks a lot

[ChrisW67]

To delete model, do i use model->clear() command?

No,

delete model;
model = 0;  // for safety if you intend the container object to hang around and reuse the model pointer.

delete model;
model = 0;  // for safety if you intend the container object to hang around and reuse the model pointer.

To copy to clipboard, switch view to plain text mode 

I close my database when i click "stop" button.

// stop button
void cycleScrDialog::on_pushButton_2_clicked()
{
    mydb.close();
    QSqlDatabase::removeDatabase("QSQLITE");
}

// stop button
void cycleScrDialog::on_pushButton_2_clicked()
{
    mydb.close();
    QSqlDatabase::removeDatabase("QSQLITE");
}

To copy to clipboard, switch view to plain text mode 

Thanks a lot

mydb, closed or not, is still a reference to the database and you will be receiving the warning. Don't hold the reference to the database as a member variable, just pick it up in local variables when required:

void someCoolMethod() 
{
  QSqlDatabase db = QSqlDatabase::database();
  ... do stuff with db
}

void someCoolMethod() 
{
  QSqlDatabase db = QSqlDatabase::database();
  ... do stuff with db
}

To copy to clipboard, switch view to plain text mode 

and you never have references hanging around. Once you correct the argument to removeDatabase() (it is the connection name not the database type) then your close routine should be warning free.

[cooper]

Thanks ChrisW67.

Can i have another question please?
as you see in my code:

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), String(qdatetime.toString(Qt::ISODate)));
        model->setData(model->index(0, 2), String(qdatetime.toString(Qt::ISODate)));
        model->setData(model->index(0, 3), 1);  // original value is 1 in int 
        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), String(qdatetime.toString(Qt::ISODate)));
        model->setData(model->index(0, 2), String(qdatetime.toString(Qt::ISODate)));
        model->setData(model->index(0, 3), 1);  // original value is 1 in int 
        model->submitAll();

To copy to clipboard, switch view to plain text mode 

when i click 'stop' button, i want to change the value in last row. here is my code:

model->setTable("c1");
        model->select();
        int row = model->rowCount();
        model->setData(model->index(row, 3), 55); // change the value to be 55 in int
        model->sumitAll();

model->setTable("c1");
        model->select();
        int row = model->rowCount();
        model->setData(model->index(row, 3), 55); // change the value to be 55 in int
        model->sumitAll();

To copy to clipboard, switch view to plain text mode 

however, after i stop my program and take a look database, the last value in (row, 3) has not been changed.

can you point out where i did wrong please?
I really appreciate your help.

[ChrisW67]

Firstly, turning of synchronous writing has data integrity risks that you should be aware of; http://www.sqlite.org/pragma.html#pragma_synchronous

There is no row rowCount() to update in your second snippet. The last existing row in the model is rowCount()-1 (if there are any rows at all).

[cooper]

Firstly, turning of synchronous writing has data integrity risks that you should be aware of

Hi ChristW67, Thanks for you suggestion. The reason why i turn off the sync is that i am writing data (10 different records) to database every second. I am not sure if the Normal (1) Sync is fast enough to perform what i need.

There is no row rowCount() to update in your second snippet. The last existing row in the model is rowCount()-1

yes, you are right, i did not realize that.
However, when i tried the following code, it wrote the data to all my 9th column. but i only want to change the value at the last row. where i missed?

model->setTable("c1");
        model->select();
        int row = model->rowCount();
        model->setData(model->index((row-1), 9), 55); // i only want to change the value at the last row to be 55 in int
        model->sumitAll();

model->setTable("c1");
        model->select();
        int row = model->rowCount();
        model->setData(model->index((row-1), 9), 55); // i only want to change the value at the last row to be 55 in int
        model->sumitAll();

To copy to clipboard, switch view to plain text mode 

[ChrisW67]

The code you show only updates a single row. When are you calling it?

[cooper]

I call this code when i click 'stop' button.

i debugged my program and put a stop mark in front of

model->setData(model->index((row-1), 9), 55);

model->setData(model->index((row-1), 9), 55);

To copy to clipboard, switch view to plain text mode 

it only been through this code once till i close my program.
I have checked through all this program, this is the only place i set data to be 55.

strange!!!

EDIT:

i changed my code a little bit to set last 2nd data to be 55555,

model->setData(model->index((row-2), 9), 55555);

model->setData(model->index((row-2), 9), 55555);

To copy to clipboard, switch view to plain text mode 

it changed all my 9th column to 55555 not just the last 2nd row.
my head full of ????? mark.

[ChrisW67]

If you do not set the value at all what is in the column?
Does the table have a primary key?
Is data flowing into the table asynchronously or is access single-threaded?
Comment out the PRAGMA and see if the behaviour changes.

[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.