Multiple database connections

[cyberboy]

Hello everybody,

I'm busy writing some kind of export function for my program.

My program is using a SQLite database and I'm writing a function to chunk it up in several pieces.

The whole database contains items with a date field in it. The function has to export all the items between 2 dates and remove them from the current database.

This is the function



bool db::backupData(QDate startDate, QDate endDate){

//database name
QString databaseName;
QDate current = QDate::currentDate();
databaseName = current.toString();


//retreive old data
this->connect();

QSqlQuery query, queryClients;

if( this->isConnected){

query.prepare("SELECT * FROM `orders` WHERE (`currentStatus` = '2' OR `currentStatus` = '3') AND deadline BETWEEN :start AND :end");
query.bindValue(QString(":start"), QVariant(startDate.toString("yyyy-MM-dd")));
query.bindValue(QString(":end"), QVariant(endDate.toString("yyyy-MM-dd")));
if(!query.exec()){

QMessageBox::critical(this, tr("SQL error"), tr("Query failed, please contact the developer or try again"));

}

if(!queryClients.exec("SELECT * FROM `clients`")){

QMessageBox::critical(this, tr("SQL error"), tr("Query failed, please contact the developer or try again"));

}


}

//close the current database connection
this->closeConnection();



//create new database
QSqlDatabase backup = QSqlDatabase::addDatabase("QSQLITE");
backup.setDatabaseName(databaseName);

if(!backup.open()){
QMessageBox::critical(this, tr("Woops"), tr("Couldn't establish connection with the database, pleas contact the developer or try again"));
return false;
}

this->createTables();


//backup clients table
while(queryClients.next()){

QSqlQuery queryInsertClients;

queryInsertClients.prepare("INSERT INTO `clients` "
"(`cid`, "
"`firstName`, "
"`lastName`, "
"`address`, "
"`zipCode`, "
"`city`, "
"`phoneNr`, "
"`email`)"
" VALUES "
"("
":cid, "
":firstName, "
":lastName, "
":address, "
":zipCode, "
":city, "
":phoneNr, "
":email "
")");


queryInsertClients.bindValue(QString(":cid"), QVariant(queryClients.value(0).toString()));
queryInsertClients.bindValue(QString(":firstName"), QVariant(queryClients.value(1).toString()));
queryInsertClients.bindValue(QString(":lastName"), QVariant(queryClients.value(2).toString()));
queryInsertClients.bindValue(QString(":address"), QVariant(queryClients.value(3).toString()));
queryInsertClients.bindValue(QString(":zipCode"), QVariant(queryClients.value(4).toString()));
queryInsertClients.bindValue(QString(":city"), QVariant(queryClients.value(5).toString()));
queryInsertClients.bindValue(QString(":phoneNr"), QVariant(queryClients.value(6).toString()));
queryInsertClients.bindValue(QString(":email"), QVariant(queryClients.value(7).toString()));

if( !queryInsertClients.exec()){

QMessageBox::critical(this, tr("Woops"), tr("Cannot commit query, backup clients failed"));
return false;
}

}


//backup order table
while(query.next()){

//debug
QMessageBox::critical(this, tr("Debug"), tr("Backup order"));

QSqlQuery queryInsert;
queryInsert.prepare("INSERT INTO `orders`"
"(`oid`,"
"`cid`,"
"`articleDescription`,"
"`articleNr`, "
"`ordered`, "
"`suspectedOn`,"
"`delivered`, "
"`customized`,"
"`deadline`, "
"`totalAmount`,"
"`prePayment`, "
"`previousStatus`,"
"`currentStatus`)"
"VALUES"
"(:oid, "
":cid, "
":articleDesc,"
":articleNr, "
":ordered,"
":suspectedOn,"
":delivered,"
":customized,"
":deadline,"
":totalAmount,"
":prepayment,"
":previousStatus,"
":currentStatus)");

queryInsert.bindValue(QString(":oid"), QVariant(query.value(0).toString()));
queryInsert.bindValue(QString(":cid"), QVariant(query.value(1).toString()));
queryInsert.bindValue(QString(":articleDesc"), QVariant(query.value(2).toString()));
queryInsert.bindValue(QString(":articleNr"), QVariant(query.value(3).toString()));
queryInsert.bindValue(QString(":ordered"), QVariant(query.value(4).toString()));
queryInsert.bindValue(QString(":suspectedOn"), QVariant(query.value(5).toString()));
queryInsert.bindValue(QString(":delivered"), QVariant(query.value(6).toString()));
queryInsert.bindValue(QString(":customized"), QVariant(query.value(7).toString()));
queryInsert.bindValue(QString(":deadline"), QVariant(query.value(8).toString()));
queryInsert.bindValue(QString(":totalAmount"), QVariant(query.value(9).toString()));
queryInsert.bindValue(QString(":prepayment"), QVariant(query.value(10).toString()));
queryInsert.bindValue(QString(":previousStatus"), QVariant(query.value(11).toString()));
queryInsert.bindValue(QString(":currentStatus"), QVariant(query.value(12).toString()));

//cannot commit query
if(!queryInsert.exec()){

QMessageBox::critical(this, tr("Woops"), tr("Cannot commit query, backup orders failed"));
return false;
}


}


//close database

backup.close();


//remove the items from the current database
this->connect();

if( this->isConnected){

//scrollback in the result list
while(query.previous()){

QSqlQuery queryDEL;

//debug
QMessageBox::critical(this, tr("Debug"), query.value(0).toString());

queryDEL.prepare("DELETE FROM `orders` WHERE oid = :oid");
queryDEL.bindValue(QString(":oid"), QVariant(query.value(0).toString()));

if(!queryDEL.exec()){
QMessageBox::critical(this, tr("Woops"), tr("Cannot commit query, removing items from the current database failed"));
//debug
QSqlError error = queryDEL.lastQuery();
QString sError = error.text();
QMessageBox::critical(this, tr("SQL error"), sError);
return false;
}

}

}

this->closeConnection();
return true;

}


The problem is, that the last query won't execute. The first thing I thought was that the query was wrong. But the query works well, its something the database connection.

The error output:


[Session started at 2008-03-30 16:11:34 +0200.]
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.
QSqlQuery::exec: database not open
QSqlQuery::prepare: database not open
ASSERT failure in QList<T>::at: "index out of range", file /usr/local/Trolltech/Qt-4.3.2/include/QtCore/qlist.h, line 386





Can somebody help me?

Greetz

cyberboy

[jpn]

QSqlDatabase::addDatabase() is supposed to be called once per type and connection name. Calling addDatabase("QSQLITE") multiple times (you do so every time db::backupData() is called) leads to


QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.

Furthermore, having existing QSqlQuery objects (query, queryClients) while removing a database leads to:


QSqlDatabasePrivate::removeDatabase: connection 'qt_sql_default_connection' is still in use, all queries will cease to work.

The rest "database not open" warnings should be quite self explanatory. The easiest fix is to divide the task into smaller pieces so that those QSqlQuery objects go out of scope when the relevant subtask is done.

[cyberboy]

First of all thanks,

But I don't get it exactly, how can I create another QSQLite database without calling QSqlDatabase::addDatabase(); ?

And can I keep somehow the QSqlQuery objects while closing/removing the database?
Or do I have to copy all the information in a QStringList or something?

[jpn]

But I don't get it exactly, how can I create another QSQLite database without calling QSqlDatabase::addDatabase(); ?
In that case you must pass a different connection name. Currently you are not passing a connection name at all so the default one, 'qt_sql_default_connection', is used. See QSqlDatabase::addDatabase() docs for more details.


And can I keep somehow the QSqlQuery objects while closing/removing the database?
Nope, you can't.


Or do I have to copy all the information in a QStringList or something?
I guess you don't have to once you define proper connection names so that you'll have a separate connection to each database.