Results 1 to 4 of 4

Thread: Multiple database connections

Threaded 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
« 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.