Re: Newbie connection question

[Rick Gutleber]

Just to throw more fuel on the fire, I have a similar function I use to 
verify my connection to MySQL from within my program.  All my database 
activity goes through a class called DBConnection.  

When a database operation is required, anything in DBConnection first 
calls verifyConnection( ) which creates a mysqlpp::Connection if 
necessary and connects it to the database, if needed.  If something 
throws an exception, I set a flag to reconnect the next time.  
Reconnecting means deleting the mysqlpp::Connection and creating a new 
one.    This works well and has been running in production for more than 
a year.  The mysqlpp::Connection member of the DBConnection class is 
called "con" below.  There is also logic to reconnect after a certain 
amount of time of disuse and error logging in the case of thrown 
exceptions, and I removed some app-specific stuff that is irrelevant to 
your needs.

There's a lot here, but having solid functions to do the fundamentals 
makes it easier in the long run because you don't have to worry about 
all these details every time you need to connect, and of course, once 
you are connected, you'll stay connected unless there's a problem.

//**********************************************************************
//
//  DBConnection::verifyConnection
//
//  We can't issue event records because if there's an error, it means
//  we aren't connected.
//
//**********************************************************************

bool DBConnection::verifyConnection( ) {
   TimeVal now( TimeVal::Now );

   // if we have a connection, check to see if it's "stale"
   if ( con && ( now >= connectionExpires ) ) {
       logMDebug1( ) << "verifyConnection( ):  connection expired at " <<
            connectionExpires.getAsString( ) << std::endl;

       delete con;
       con = NULL;
   }

   // check to see if a reconnect has been requested
   if ( reconnect ) {
       logMDebug1( ) << "verifyConnection( ):  reconnecting..." << 
std::endl;

       delete con;
con = NULL;
   }

   // finally, check the connection and if it's OK, we're cool
   if ( isConnected( ) ) {
       connectionExpires = now + getStaleConnectionThreshold( );

//          logMDebug1( ) << "verifyConnection( ):  setting connection 
expiry at " <<
//                  connectionExpires.getAsString( ) << std::endl;

       return true;
   }

   try {
       if ( !con ) {
           con = new mysqlpp::Connection( true );  // 'true' means throw 
exceptions on errors
       }

       logMDebug2( ) << "host:  " << getHostName( ) << ", name:  "
<< 
getDBName( ) <<
                ", user:  " << getUserName( ) << std::endl;

       bool rc = con->connect( getDBName( ).c_str( ), getHostName( 
).c_str( ),
                                getUserName( ).c_str( ), getPassword( 
).c_str( ) );

       if ( !rc ) {
           logMError( ) << "verifyConnection( ):  con->connect( ) " <<
                   "failed but no exception was thrown! (host:  " << 
getHostName( ) << ")" << std::endl;

           return false;
       }

       if ( !con->connected( ) ) {
           logMError( ) << "verifyConnection( ):  con->connected( ) " <<
                   "failed but no exception was thrown! (host:  " << 
getHostName( ) << ")" << std::endl;

           return false;
       }

       connectionExpires = now + getStaleConnectionThreshold( );

//          logMDebug1( ) << "verifyConnection( ):  setting connection 
expiry at " <<
//                  connectionExpires.getAsString( ) << std::endl;

       logMDebug2( ) << "MySQL info:  " << con->ipc_info( ) <<
std::endl;
   } catch ( mysqlpp::BadQuery & er ) {
       error = er.what( );

       logMError( ) << "MySQL BadQuery Connection error:  '" << error <<

"' (host:  " <<
               getHostName( ) << ")" << std::endl;

       reconnect = true;

       return false;
   } catch ( const mysqlpp::Exception & er ) {
       error = er.what( );

       logMError( ) << "MySQL Connection error:  '" << error << "' 
(host:  " <<
               getHostName( ) << ")" << std::endl;

       return false;
   }

   reconnect = false;

   return true;
}


Sebastián Salazar Molina. wrote:
> Thank you for your response.
>
> I'm working on a project where time is the critical factor, I don't have many 
> concurrent clients ( < 30 ) and I need the minimal time of response, so open 
> and close connection is a bad choice for me, this is my first project with 
> mysql++ and I looking to make some similar to mysql_pconnect of PHP, 
> I'm going to tried to follow your suggestion
>
> Thank you a lot.
>
>