Results 1 to 4 of 4

Thread: Multiple database connections

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. 30th March 2008, 15:19 #1
    cyberboy
    cyberboy is offline Novice
    Join Date
    Jan 2008
    Posts
    58
    Thanks
    3
    Qt products
    Qt4
    Platforms
    MacOS X

    Default Multiple database connections

    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

    Qt Code: 
    1. bool db::backupData(QDate startDate, QDate endDate){
    2.  
    3. //database name
    4. QString databaseName;
    5. QDate current = QDate::currentDate();
    6. databaseName = current.toString();
    7.  
    8.  
    9. //retreive old data
    10. 	this->connect();
    11.  
    12. 	QSqlQuery query, queryClients;
    13.  
    14. 	if( this->isConnected){
    15.  
    16. 		query.prepare("SELECT * FROM `orders` WHERE (`currentStatus` = '2' OR `currentStatus` = '3')  AND deadline BETWEEN :start AND :end");
    17. 		query.bindValue(QString(":start"), QVariant(startDate.toString("yyyy-MM-dd")));
    18. 		query.bindValue(QString(":end"), QVariant(endDate.toString("yyyy-MM-dd")));
    19. 		if(!query.exec()){
    20.  
    21. 			QMessageBox::critical(this, tr("SQL error"), tr("Query failed, please contact the developer or try again"));
    22.  
    23. 		}
    24.  
    25. 		if(!queryClients.exec("SELECT * FROM `clients`")){
    26.  
    27. 			QMessageBox::critical(this, tr("SQL error"), tr("Query failed, please contact the developer or try again"));
    28.  
    29. 		}
    30.  
    31.  
    32. 	}
    33.  
    34. 	//close the current database connection
    35. 	this->closeConnection();
    36.  
    37.  
    38.  
    39. 	//create new database
    40. 	QSqlDatabase backup = QSqlDatabase::addDatabase("QSQLITE");
    41. 	backup.setDatabaseName(databaseName);
    42.  
    43. 	if(!backup.open()){
    44. 		QMessageBox::critical(this, tr("Woops"), tr("Couldn't establish connection with the database, pleas contact the developer or try again"));
    45. 		return false;
    46. 	}
    47.  
    48. 	this->createTables();
    49.  
    50.  
    51. 	//backup clients table
    52. 	while(queryClients.next()){
    53.  
    54. 		QSqlQuery  queryInsertClients;
    55.  
    56. 		queryInsertClients.prepare("INSERT INTO `clients` "
    57. 				"(`cid`, "
    58. 				"`firstName`, "
    59. 				"`lastName`, "
    60. 				"`address`, "
    61. 				"`zipCode`,  "
    62. 				"`city`, "
    63. 				"`phoneNr`, "
    64. 				"`email`)"
    65. 				" VALUES "
    66. 				"("
    67. 				":cid, "
    68. 				":firstName, "
    69. 				":lastName, "
    70. 				":address, "
    71. 				":zipCode, "
    72. 				":city, "
    73. 				":phoneNr, "
    74. 				":email "
    75. 				")");
    76.  
    77.  
    78. 				queryInsertClients.bindValue(QString(":cid"), QVariant(queryClients.value(0).toString()));
    79. 				queryInsertClients.bindValue(QString(":firstName"), QVariant(queryClients.value(1).toString()));
    80. 				queryInsertClients.bindValue(QString(":lastName"), QVariant(queryClients.value(2).toString()));
    81. 				queryInsertClients.bindValue(QString(":address"), QVariant(queryClients.value(3).toString()));
    82. 				queryInsertClients.bindValue(QString(":zipCode"), QVariant(queryClients.value(4).toString()));
    83. 				queryInsertClients.bindValue(QString(":city"), QVariant(queryClients.value(5).toString()));
    84. 				queryInsertClients.bindValue(QString(":phoneNr"), QVariant(queryClients.value(6).toString()));
    85. 				queryInsertClients.bindValue(QString(":email"), QVariant(queryClients.value(7).toString()));				
    86.  
    87. 				if( !queryInsertClients.exec()){
    88.  
    89. 						QMessageBox::critical(this, tr("Woops"), tr("Cannot commit query, backup clients failed"));
    90. 						return false;
    91. 				}
    92.  
    93. 	}
    94.  
    95.  
    96. 	//backup order table	
    97. 	while(query.next()){
    98.  
    99. 				//debug
    100. 				QMessageBox::critical(this, tr("Debug"), tr("Backup order"));
    101.  
    102. 				QSqlQuery queryInsert;
    103. 				queryInsert.prepare("INSERT INTO `orders`"
    104. 						"(`oid`," 
    105. 						 "`cid`," 
    106. 						"`articleDescription`," 
    107. 						"`articleNr`, "
    108. 						"`ordered`, "
    109. 						"`suspectedOn`," 
    110. 						"`delivered`, "
    111. 						"`customized`,"
    112. 						"`deadline`, "
    113. 						"`totalAmount`," 
    114. 						"`prePayment`, "
    115. 						"`previousStatus`," 
    116. 						"`currentStatus`)"
    117. 						"VALUES"
    118. 						"(:oid, "
    119. 						":cid, "
    120. 						":articleDesc," 
    121. 						":articleNr, "
    122. 						":ordered,"
    123. 						":suspectedOn,"
    124. 						":delivered,"
    125. 						":customized,"
    126. 						":deadline,"
    127. 						":totalAmount,"
    128. 						":prepayment,"
    129. 						":previousStatus,"
    130. 						":currentStatus)");
    131.  
    132. 					queryInsert.bindValue(QString(":oid"), QVariant(query.value(0).toString()));
    133. 					queryInsert.bindValue(QString(":cid"), QVariant(query.value(1).toString()));
    134. 					queryInsert.bindValue(QString(":articleDesc"), QVariant(query.value(2).toString()));
    135. 					queryInsert.bindValue(QString(":articleNr"), QVariant(query.value(3).toString()));
    136. 					queryInsert.bindValue(QString(":ordered"), QVariant(query.value(4).toString()));
    137. 					queryInsert.bindValue(QString(":suspectedOn"), QVariant(query.value(5).toString()));
    138. 					queryInsert.bindValue(QString(":delivered"), QVariant(query.value(6).toString()));
    139. 					queryInsert.bindValue(QString(":customized"), QVariant(query.value(7).toString()));
    140. 					queryInsert.bindValue(QString(":deadline"), QVariant(query.value(8).toString()));
    141. 					queryInsert.bindValue(QString(":totalAmount"), QVariant(query.value(9).toString()));
    142. 					queryInsert.bindValue(QString(":prepayment"), QVariant(query.value(10).toString()));
    143. 					queryInsert.bindValue(QString(":previousStatus"), QVariant(query.value(11).toString()));
    144. 					queryInsert.bindValue(QString(":currentStatus"), QVariant(query.value(12).toString()));
    145.  
    146. 					//cannot commit query
    147. 					if(!queryInsert.exec()){
    148.  
    149. 						QMessageBox::critical(this, tr("Woops"), tr("Cannot commit query, backup orders failed"));
    150. 						return false;
    151. 					}
    152.  
    153.  
    154. 			}
    155.  
    156.  
    157. 	//close database
    158.  
    159. 	backup.close();
    160.  
    161.  
    162. 	//remove the items from the current database
    163. 	this->connect();
    164.  
    165. 	if( this->isConnected){
    166.  
    167. 		//scrollback in the result list
    168. 		while(query.previous()){
    169.  
    170. 			QSqlQuery queryDEL;
    171.  
    172. 			//debug
    173. 			QMessageBox::critical(this, tr("Debug"), query.value(0).toString());
    174.  
    175. 			queryDEL.prepare("DELETE FROM `orders` WHERE oid = :oid");
    176. 			queryDEL.bindValue(QString(":oid"), QVariant(query.value(0).toString()));
    177.  
    178. 			if(!queryDEL.exec()){
    179. 					QMessageBox::critical(this, tr("Woops"), tr("Cannot commit query, removing items from the current database failed"));
    180. 					//debug
    181. 					QSqlError error = queryDEL.lastQuery();
    182. 					QString sError = error.text();
    183. 					QMessageBox::critical(this, tr("SQL error"), sError);
    184. 					return false;
    185. 			}
    186.  
    187. 		}
    188.  
    189. 	}
    190.  
    191. 	this->closeConnection();
    192. 	return true;
    193.  
    194. }
    To copy to clipboard, switch view to plain text mode 

    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:repare: 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
    Last edited by cyberboy; 30th March 2008 at 15:54. Reason: updated contents
    Reply With Quote Reply With Quote
  2. 30th March 2008, 16:14 #2
    jpn
    jpn is offline Guru
    Nokia Certified Qt Developer
    Join Date
    Feb 2006
    Location
    Oslo, Norway
    Posts
    6,264
    Thanks
    36
    Thanked 1,519 Times in 1,389 Posts
    Qt products
    Qt4
    Platforms
    MacOS X Unix/X11 Windows Symbian S60 Maemo/MeeGo

    Default Re: Multiple database connections

    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.
    J-P Nurmi
    Reply With Quote Reply With Quote
  3. 30th March 2008, 16:48 #3
    cyberboy
    cyberboy is offline Novice
    Join Date
    Jan 2008
    Posts
    58
    Thanks
    3
    Qt products
    Qt4
    Platforms
    MacOS X

    Default Re: Multiple database connections

    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?
    Reply With Quote Reply With Quote
  4. 30th March 2008, 16:56 #4
    jpn
    jpn is offline Guru
    Nokia Certified Qt Developer
    Join Date
    Feb 2006
    Location
    Oslo, Norway
    Posts
    6,264
    Thanks
    36
    Thanked 1,519 Times in 1,389 Posts
    Qt products
    Qt4
    Platforms
    MacOS X Unix/X11 Windows Symbian S60 Maemo/MeeGo

    Default Re: Multiple database connections

    Quote Originally Posted by cyberboy View Post
    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.
    J-P Nurmi
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Similar Threads

  1. Threads and database connection
    By probine in forum Qt Programming
    Replies: 9
    Last Post: 7th August 2013, 08:30
  2. Qt on X11 with multiple display connections?
    By grenoble in forum Qt Programming
    Replies: 1
    Last Post: 25th February 2008, 12:44
  3. Database Master-Detail Entry Form
    By Phan Sin Tian in forum Newbie
    Replies: 4
    Last Post: 3rd February 2008, 14:31
  4. Multiple connections to one method
    By davisjamesf in forum Newbie
    Replies: 4
    Last Post: 16th November 2007, 20:11
  5. Filling combobox from database
    By Philip_Anselmo in forum Qt Programming
    Replies: 3
    Last Post: 11th May 2006, 17:53

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules

Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.