SQL toolkit 2.2 Stored Procedure Memory Leaks

[pat.sac]

Hi

we are using CVI 2012 and SQL Toolkit 2.2. My db is "MySql 5.5.28" and I use "MySQL Connector/ODBC 5.2(w)"

I use only stored procedure (with and without the output parameters).  If I call continuously a stored procedure,

with Sql toolkit code, I have memory leaks!! 

My code (without error handler) is:

// iDbConnId is the handle of DBConnect() called when program starts

iStmt = DBPrepareSQL(iDbConnId, "spGetPrData");

DBSetStatementAttribute(iStmt, ATTR_DB_STMT_COMMAND_TYPE, DB_COMMAND_STORED_PROC);
    
DBExecutePreparedSQL(iStmt);

DBBindColLongLong(iStmt, 1, &llPrId, &lStatus1);
DBBindColInt(iStmt, 2, &iIpPort, &lStatus2);
                        
while(DBFetchNext(iStmt) != DB_EOF)
{
    //get data
}
    
DBClosePreparedSQL(iStmt);

DBDiscardSQLStatement(iStmt);

If I call the same stored procedure by sql code ("CALL spProcedure()")
I don't have memory leaks!!

The code is (without error handler):

// iDbConnId is the handle of DBConnect() called when program starts
iStmt = DBActivateSQL(iDbConnId, "CALL spGetPrData();");

DBBindColLongLong(iStmt, 1, &llPrId, &lStatus1);
DBBindColInt(iStmt, 2, &iIpPort, &lStatus2);
                        
while(DBFetchNext(iStmt) != DB_EOF)
{
    //get data
}

DBDeactivateSQL (iStmt)

It seems to me that the DBDeactivateSQL function free the memory correctly,

while the functions DBClosePreparedSQL and DBDiscardSQLStatement do not release properly the memory,

(or I use improperly the library functions!!!)

I also have a question to ask:

Can I open and close the connection every time I connect to the database or is it mandatory to open and close it just one time?

How can I check the status of the database connection, if is mandatory to open and close the db connection just one time?

Thanks for your help.

Best Regards

Patrizio

[m.amoruso]

Hi,

one of the functions "DBClosePreparedSQL and DBDiscardSQLStatement" is a known problem. In fact, there is a CAR (Corrective Action Request 91490) reporting the problem. What version of the toolkit are you using?

About this function you refer to the manual:

http://digital.ni.com/manuals.nsf/websearch/D3DF4019471D3A9386256B3C006CDC78

Where functions are described. To avoid memory leaks DBDeactivateSQL must be used. DBDeactivateSQL is equivalent to calling DBCloseSQLStatement, then DBDiscardSQLStatement. DBDeativateSQL will work at all for parameterized SQL, as well.

Regarding the connection mode advice to you is to open the connection once and close it at the end of your operations. What do you mean with "state of the database connection"? Remember that if you have connection problems or something goes wrong, the errors appears in any point in your code where you query the database. Bye

Mario