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.