RE: Lost connection on second stored procedure call

[Jim Wallace]

I was having disconnects because many calls to SPs return > 1 result set
(which is why you use multi_statements).  You must process them to
continue.  Here's a function I call after each SP call.  Note that in
each case I have found that there are 2 result sets, but the second one
is always empty, but must be processed.

HTH,
Jim W.

static void checkMultipleResultSets( Query &query, Logger &logger,
LPCTSTR name )
{
	for (int i = 1; query.more_results(); ++i) 
	{
		Result res = query.store_next();
		int num_results = res.size();
		if (res && (num_results > 0)) 
		{
			LOG4CPLUS_INFO( logger, "Got > 1 result set in "
<< name << " index " << i << " with row count of " <<
res.num_rows() );	
		}
		else 
		{
			LOG4CPLUS_TRACE( logger, "Got > 1 result set in
" << name << " index " << i << " and it's empty" );	
		}
	}
} 

-----Original Message-----
From: Urscheler, Roger (COM Chantry CA)
[mailto:roger.urscheler@stripped] 
Sent: Thursday, December 14, 2006 3:30 PM
To: MySQL++ Mailing List
Subject: Lost connection on second stored procedure call

Hi,

I have a simple stored procedure that works the first time, but when
calling the second time I get "Lost connection to MySQL server during
query". Does anybody have an idea what I am doing wrong?

When replacing the query from the SP call query << "CALL sp_get_3()"; to
query << "SELECT 3"; everything works fine.

Thanks,
Roger

SP:
DELIMITER //
DROP PROCEDURE sp_get_3 //
CREATE PROCEDURE sp_get_3()
COMMENT 'Get 3'
BEGIN 
    SELECT 3;
END //
DELIMITER ;

Code:
m_con->set_option(Connection::opt_multi_statements, true);
m_con->connect(m_args.db_name, m_args.db_hostname, m_args.db_user,
m_args.db_password, m_args.db_port);

    // first call
    try
    {       
        mysqlpp::Query query = m_con->query();
        query << "CALL sp_get_3()";
        query.parse();
        cout << query.preview() << endl;
        mysqlpp::Result res = query.store();
        if (res) 
        {  
            int num_results = res.size();
            for (int i = 0; i < num_results; ++i) 
            {
                mysqlpp::Row row = res.fetch_row();
                if (!row.at(0).is_null())
                {
                    cout << "Result: " << row.at(0) << endl;
                }
            }
        }
    }
    catch (const mysqlpp::EndOfResults& er) 
    {
        TRACE_FULL("query succeeded: %s", er.what());
    }
    catch (exception& er) 
    {
        TRACE_ERROR("query failed: %s", er.what());
    }

    // second call, same code as above
    try
    {       
        mysqlpp::Query query = m_con->query();
        query << "CALL sp_get_3()";
        query.parse();
        cout << query.preview() << endl;
        mysqlpp::Result res = query.store();
        if (res) 
        {  
            int num_results = res.size();
            for (int i = 0; i < num_results; ++i) 
            {
                mysqlpp::Row row = res.fetch_row();
                if (!row.at(0).is_null())
                {
                    cout << "Result: " << row.at(0) << endl;
                }
            }
        }
    }
    catch (const mysqlpp::EndOfResults& er) 
    {
        TRACE_FULL("query succeeded: %s", er.what());
    }
    catch (exception& er) 
    {
        TRACE_ERROR("query failed: %s", er.what());
    }



Output:
CALL sp_get_3()
Result: 3
CALL sp_get_3()
query failed: Lost connection to MySQL server during query 


__________________________________________________
roger.urscheler@stripped <mailto:roger.urscheler@stripped>  ::
[phone] x 6446 Chantry Networks, Siemens AG ::
www.chantrynetworks.com <http://www.chantrynetworks.com>