Subject: Re: Issues with multi-queries 2007-11-07 - By Paul Martin
Back
I tried pasting the whole cpp file, then parts of it... no go. I'll try
again with less data:
Multiquery code- modified multiquery example from 2.3.2. Pretty
self-explanatory with comments at the bottom for the Sleep function... note
that you must add a 'dbtest' database and a user called 'TestApp' using
'password' with rights to that db.
#include "conio.h" // For getch()
...
Connection con; // Global
...
static void
print_multiple_results(Query& query)
{
if(con.ping())
{
cout << "Connection fried... ";
Sleep(10);
if(!con.connect("dbtest","127.0.0.1","TestApp","password"))
{
cout << "reconnect failed\n";
return;
}
con.set_option(Connection::opt_multi_statements, true);
cout << "reconnect successful\n";
}
try {
// Execute query and print all result sets
Result res = query.store();
res.purge();
}
catch (Exception& err) {
// Something bad happened....
cerr << "Multi-query failure: " << err.what() << endl;
cout << "Program halted by error\n";
exit(1);
}
}
...
// Connect to database
if(!con.connect("dbtest","127.0.0.1","TestApp","password"))
return 1;
...
// Set up db table
Query query = con.query();
query << "BEGIN WORK;" << endl;
query << "SET FOREIGN_KEY_CHECKS=0;" << endl;
query << "DROP DATABASE IF EXISTS dbtest;" << endl;
query << "CREATE DATABASE dbtest;" << endl;
query << "use dbtest;" << endl;
query << "CREATE TABLE `status` (" << endl;
query << " `StatusID` int(10) unsigned NOT NULL," << endl;
query << " `ObjectID` smallint(5) unsigned default NULL," << endl;
query << " `StationID` smallint(5) unsigned default NULL," << endl;
query << " `IsCurrent` tinyint(1) NOT NULL default '0'," << endl;
query << " `LastSeen` datetime default NULL," << endl;
query << " `FirstSeen` datetime default NULL," << endl;
query << " `Duration` varchar(10) NOT NULL default '00:00:00'," <<
endl;
query << " `RSSI` tinyint(3) unsigned default NULL," << endl;
query << " `GroupID` tinyint(3) unsigned default NULL," << endl;
query << " PRIMARY KEY (`StatusID`)," << endl;
query << " KEY `FK1` (`ObjectID`)," << endl;
query << " KEY `FK2` (`StationID`)," << endl;
query << " KEY `FK3` (`GroupID`)" << endl;
query << ") ENGINE=InnoDB DEFAULT CHARSET=latin1;" << endl;
query << "COMMIT;";
print_multiple_results(query);
query.reset();
cout << "Database table created\n";
Sleep(1000); // Pause before inserts begin
query << "BEGIN WORK;" << endl;
query << "INSERT INTO Status
VALUES(1,29,2,1,'20071106111629','20071106111629',DEFAULT,180,0);" << endl;
query << "INSERT INTO Status
VALUES(2,16,2,1,'20071106111629','20071106111629',DEFAULT,186,0);" << endl;
// 9 more INSERTs with similar data
...
query << "COMMIT;";
print_multiple_results(query);
query.reset();
cout << "Inserts done\n";
Sleep(1000); // Pause before query loop begins
// Set up query with multiple queries.
for(;;)
{
Query query = con.query();
query << "BEGIN WORK;" << endl;
query << "UPDATE Status SET
LastSeen='20071106084146',Duration='00:00:03',RSSI=194,GroupID=0,IsCurrent=1
WHERE StatusID=1;" << endl;
query << "UPDATE Status SET
LastSeen='20071106084146',Duration='00:00:03',RSSI=178,GroupID=0,IsCurrent=1
WHERE StatusID=2;" << endl;
query << "UPDATE Status SET
LastSeen='20071106084146',Duration='00:01:41',RSSI=197,GroupID=0,IsCurrent=1
WHERE StatusID=3;" << endl;
query << "UPDATE Status SET IsCurrent=0 WHERE TagID=4;" << endl;
query << "UPDATE Status SET
LastSeen='20071106084212',Duration='00:02:07',RSSI=179,GroupID=0,IsCurrent=1
WHERE StatusID=4;" << endl;
// 105 more UPDATEs with similar data
...
query << "COMMIT;";
cout << "Query #" << ++count << " executed successfully\n";
// Execute statement and display all result sets.
print_multiple_results(query);
// The above call will throw an exception and give "Lost
Connection to MySQL Server during Query" error
// The Sleep value below determines if/when it will fail:
// Sleep(0)- Happens right away, usually after only 2 queries
// Sleep(1)- Happens after 1-500 or so queries... one test was
244,6,114,17,4
// Sleep(2)- Happens after 1-500 or so queries... one test was
274,228,278,88,19
// Larger values take more loops, and the problem may not happen
at all.
Sleep(1);
if(kbhit())
{
cout << "Program halted by user\n";
while(kbhit())
char a=getch();
exit(0);
}
}
return 0;
}
--
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe: http://lists.mysql.com/plusplus?unsub=mysql@(protected)
|
|
