------_=_NextPart_001_01C71FBE.9D2D40B7
Content-Type: text/plain;
charset="iso-8859-1"

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:pt_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 (AT) siemens (DOT) com <mailto:roger.urscheler (AT) siemens (DOT) com> :: (905)
363-6400 x 6446
Chantry Networks, Siemens AG :: www.chantrynetworks.com
<http://www.chantrynetworks.com>


------_=_NextPart_001_01C71FBE.9D2D40B7--

------=_Part_117671_8580666.1166137860942
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I think your problem may be that you are creating two instances of the Query
object in the same function - not sure is that not allowed or not. Try to
reuse the same instance and your problem will go away.

--Thanks,
--Zeddy

On 12/14/06, Urscheler, Roger (COM Chantry CA) <roger.urscheler (AT) siemens (DOT) com>
wrote:
------=_Part_117671_8580666.1166137860942--

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 =3D 1; query.more_results(); ++i)=20
{
Result res =3D query.store_next();
int num_results =3D res.size();
if (res && (num_results > 0))=20
{
LOG4CPLUS_INFO( logger, "Got > 1 result set in "
<< name << " index " << i << " with row count of " << res.num_rows() );=09
}
else=20
{
LOG4CPLUS_TRACE( logger, "Got > 1 result set in
" << name << " index " << i << " and it's empty" );=09
}
}
}=20

-----Original Message-----
From: Urscheler, Roger (COM Chantry CA)
[mailto:roger.urscheler (AT) siemens (DOT) com]=20
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=20
SELECT 3;
END //
DELIMITER ;

Code:
m_con->set_option(Connection:pt_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
{ =20
mysqlpp::Query query =3D m_con->query();
query << "CALL sp_get_3()";
query.parse();
cout << query.preview() << endl;
mysqlpp::Result res =3D query.store();
if (res)=20
{ =20
int num_results =3D res.size();
for (int i =3D 0; i < num_results; ++i)=20
{
mysqlpp::Row row =3D res.fetch_row();
if (!row.at(0).is_null())
{
cout << "Result: " << row.at(0) << endl;
}
}
}
}
catch (const mysqlpp::EndOfResults& er)=20
{
TRACE_FULL("query succeeded: %s", er.what());
}
catch (exception& er)=20
{
TRACE_ERROR("query failed: %s", er.what());
}

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


__________________________________________________
roger.urscheler (AT) siemens (DOT) com <mailto:roger.urscheler (AT) siemens (DOT) com> ::
(905) 363-6400 x 6446 Chantry Networks, Siemens AG ::
www.chantrynetworks.com <http://www.chantrynetworks.com> =20




--
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe: http://lists.mysql.com/plusplus?unsu...ie.nctu.edu.tw