MySQL Forums
Forum List  »  Connector/C++

Prepared Statements not getting closed
Posted by: James Barajas
Date: August 02, 2012 02:19PM

Using MySQL Connector C++ 1.1.0
MySQL server 5.5

I'm having a problem with prepared statements not getting closed every time even though I explicitly close the prepared statement in my method.
The method gets called back-to-back three times every 50ms.
When it is called back-to-back, the prepared statement only gets closed once out of the three times. so eventually I get an exception that I have exceeded the maximum number of prepared statements.

query: show global status like '%Com_Stmt%'; confirms that only about 1/3 of all
prepared statements created get closed.

The MySQL log show the following pattern:
Prepare SELECT * FROM myTbl WHERE ID = ? and name = ?
Execute SELECT * FROM myTbl WHERE ID = '1' and name = 'John'
Prepare SELECT * FROM myTbl WHERE ID = ? and name = ?
Execute SELECT * FROM myTbl WHERE ID = '2' and name = 'Josh'
Prepare SELECT * FROM myTbl WHERE ID = ? and name = ?
Execute SELECT * FROM myTbl WHERE ID = '3' and name = 'Mike'
Close stmt
.....

so the log confirms that the statement does not get closed every time.

but when I run my program my print statements show the following pattern:
creating prepared stmt
executing prepared stmt
closing prepared stmt
creating prepared stmt
executing prepared stmt
closing prepared stmt
......
and there is NO exception thrown other then:

"can't create more than max_prepared_stmt_count statements (current value: 16382)"
after a few minutes. So I don't know what's going on.


Here is the code I'm running:

ResultSet * DBMgr::getRecords( list<string> values) {

ResultSet * res;
PreparedStatement *pstmt;
SQLString query = "SELECT * FROM myTbl WHERE ID = ? and name = ? ";
string val;

cout << " creating prepared stmt\n";
pstmt = m_mySqlConnection -> prepareStatement(query);
int i = 1;
BOOST_FOREACH(val, values)
{
pstmt->setString(i,val);
i++;
}
try {
cout << "executing prepared stmt\n";
res = pstmt->executeQuery();
cout << " closing prepared stmt\n;"
pstmt->close();
delete pstmt;
}
catch (SQLException &e) {
cout << "ERROR: SQLException in " << __FILE__;
cout << " (" << __func__<< ") on line " << __LINE__ << endl;
cout << "ERROR: " << e.what();
cout << " (MySQL error code: " << e.getErrorCode();
}
return res;
}

hope someone can advise.

Thanks!

-James

Options: ReplyQuote


Subject
Views
Written By
Posted
Prepared Statements not getting closed
12418
August 02, 2012 02:19PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.