Re: Prepared Statements not getting closed
Andrii,
Here is a stand alone program that demonstrates the problem.
Just change the host, user, passwd info.
The program creates a test db and table and insert sample data.
Then queries using prepared statements and crashes when the max prepared stmts is reached even though there an explicit close() call for the prepared statement.
This program crashes within 20 seconds when running on my machine.
Thanks,
---------------------------- begin code ----------------------
#include <stdlib.h>
#include <resultset.h>
#include <connection.h>
#include <mysql_driver.h>
#include <statement.h>
#include <prepared_statement.h>
#include <exception.h>
#include <ctime>
using namespace std;
sql::Connection * con;
sql::ResultSet* getRecords(){
sql::ResultSet * res;
sql::PreparedStatement *pstmt;
sql::SQLString query;
query = "SELECT * FROM testTbl WHERE ownerId = ? AND num = ?";
pstmt = con->prepareStatement(query);
pstmt->setString(1,"2");
pstmt->setString(2,"3");
try
{
cout << "executing prepared stmt: " << endl;
res = pstmt->executeQuery();
cout << "closing prepared stmt: " << endl;
pstmt->close();
delete pstmt;
}
catch (sql::SQLException &e) {
cout << "ERROR: SQLException in " << __FILE__;
cout << " (" << __func__<< ") on line " << __LINE__ << endl;
cout << "ERROR: " << e.what();
cout << " (MySQL error code: " << e.getErrorCode();
cout << ", SQLState: " << e.getSQLState() << ")" << endl;
cout << "closing prepared stmt" << endl;
if (pstmt != NULL)
{
pstmt->close();
delete pstmt;
}
}
return res;
}
int main(int argc, char *argv[]) {
sql::mysql::MySQL_Driver *driver;
sql::SQLString query;
sql::Statement *stmt;
sql::ResultSet *res;
const int timeFrame = 1; // seconds
// connect to server and create db
try {
driver = sql::mysql::get_mysql_driver_instance();
con = driver -> connect("tcp://127.0.0.1:3306", "user", "");
stmt = con->createStatement();
// create db
query = "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'testDb'";
res = stmt->executeQuery(query);
if (res->rowsCount() == 0) {
cout << " creating db testTbl\n";
query = "CREATE DATABASE testDb";
stmt->execute(query);
}
con->setSchema("testDb");
}
catch (sql::SQLException &e) {
cout << "ERROR: SQLException in " << __FILE__;
cout << " (" << __func__<< ") on line " << __LINE__ << endl;
cout << "ERROR: " << e.what();
cout << " (MySQL error code: " << e.getErrorCode();
cout << ", SQLState: " << e.getSQLState() << ")" << endl;
}
// create table
try {
query = "SHOW TABLES LIKE 'testTbl'";
res = stmt->executeQuery(query);
if (res->rowsCount() == 0) {
cout << " creating testTbl \n";
query = "CREATE TABLE testTbl (id VARCHAR(20) NOT NULL, ownerId VARCHAR(20), num INT, PRIMARY KEY(id))";
stmt->execute(query);
}
}
catch (sql::SQLException &e) {
cout << "ERROR: SQLException in " << __FILE__;
cout << " (" << __func__<< ") on line " << __LINE__ << endl;
cout << "ERROR: " << e.what();
cout << " (MySQL error code: " << e.getErrorCode();
cout << ", SQLState: " << e.getSQLState() << ")" << endl;
}
// insert sample data
try {
query = "INSERT INTO testTbl SET id = '1', ownerId = '2', num = '3'";
stmt->execute(query);
query = "INSERT INTO testTbl SET id = '2', ownerId = '2', num = '3'";
stmt->execute(query);
query = "INSERT INTO testTbl SET id = '3', ownerId = '2', num = '3'";
stmt->execute(query);
query = "INSERT INTO testTbl SET id = '4', ownerId = '3', num = '3'";
stmt->execute(query);
}
catch (sql::SQLException &e) {
cout << "ERROR: SQLException in " << __FILE__;
cout << " (" << __func__<< ") on line " << __LINE__ << endl;
cout << "ERROR: " << e.what();
cout << " (MySQL error code: " << e.getErrorCode();
cout << ", SQLState: " << e.getSQLState() << ")" << endl;
}
if (stmt != NULL)
{
stmt->close();
delete stmt;
}
bool timerExpired = false;
std::time_t myTimer = std::time( NULL );
while (true)
{
timerExpired = ( std::time( NULL ) - myTimer ) >= timeFrame;
if (timerExpired)
{
cout << " timer expired querying db\n";
// sending 1000 back-to back calls so that it crashes sooner
// my real program sends only up to 20 back-to-back calls so it takes a few minutes to crash
// this one takes about 20 seconds on my box
for (int i=0; i<1000; i++)
{
res = getRecords();
// process results
}
myTimer = std::time( NULL );
}
}
return 0;
}
-----------------------------end code ----------------------------------