List:MySQL++« Previous MessageNext Message »
From:Paul Martin Date:November 7 2007 9:05pm
Subject:Re: Issues with multi-queries
View as plain text  
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;

}

Thread
Issues with multi-queriesPaul Martin7 Nov
  • Re: Issues with multi-queriesWarren Young7 Nov
  • Re: Issues with multi-queriesPaul Martin7 Nov
    • Re: Issues with multi-queriesWarren Young7 Nov
  • Re: Issues with multi-queriesPaul Martin8 Nov
    • Re: Issues with multi-queriesWarren Young8 Nov
      • Re: Issues with multi-queriesMaarten Schrijvers8 Nov
  • Re: Issues with multi-queriesPaul Martin8 Nov
    • Re: Issues with multi-queriesWarren Young10 Nov
  • Re: Issues with multi-queriesPaul Martin10 Nov
    • Re: Issues with multi-queriesWarren Young10 Nov
  • RE: Issues with multi-queriesIan Daysh12 Nov
    • Re: Issues with multi-queriesWarren Young13 Nov