MySQL Forums
Forum List  »  Connector/C++

Re: Prepared Statements not getting closed
Posted by: James Barajas
Date: August 03, 2012 01:00PM

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 ----------------------------------

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Prepared Statements not getting closed
3824
August 03, 2012 01:00PM


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.