We have created a multithreaded application that reads result sets from MS SQL Server (both 2000 and 2005). The MS SQL Server app and our app reside on the same machine. We are using the latest version of ODBC32.DLL (3.526.1830.0) and SQLSRV32.DLL (2000.86.1830). Our application is written in C++ using the Visual C++ 6.0 compiler and libraries. Our app runs as a service, therefore the apparent memory leak is a real problem. Our app needs to run on a server in a closet without human intervention.
We are kicking off many user threads that each can read from the database tables. Each of the reads from the database occurs within a critical section to minimize the threads stepping on each other. The ODBC interface class follows all the steps defined in the ODBC application developers documention (see code below).
We see or app memory steadily increasing over time (we used PerfMon to monitor Private Bytes and Virtual Bytes, per ODBC documentation). If we terminate the threads which are retreiving the result sets, the memory drops back to the level noted prior to starting the threads.
The code below is admittedly inefficient, however, it should not leak memory when accessing the database. Note that it works very well, returns the result sets that we expect exactly.
SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc = SQL_NULL_HDBC;
SQLHSTMT hstmt1 = SQL_NULL_HSTMT;
SQLRETURN retCode;
SQLSMALLINT sColCount = 0;
CODBCInterfaceColumnList lColumnList;
CString strDSN;
CString strUID = _T("AccountName");
CString strPWD = _T("Password");
CString strServer;
CString strDatabase;
CString strDebugMsg;
if(!CreateDSN( pRecordList->m_strDefaultODBCConnect,
strDSN,
strDatabase,
strUID,
strPWD,
strServer))
{
strExceptionMsg.Format( _T("Cannot create DSN from connect string %s"),
pRecordList->m_strDefaultODBCConnect);
bReturn = true;
return bReturn;
}
if(pRecordList->m_strDefaultODBCConnect.IsEmpty())
{
bReturn = true;
return bReturn;
}
// Allocate the environment handle
retCode = SQLAllocHandle(SQL_HANDLE_ENV,NULL, &henv);
if(retCode != SQL_ERROR && retCode != SQL_INVALID_HANDLE)
{
// Set the environment to ODBC Version 3.0
retCode = SQLSetEnvAttr(henv,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3,
SQL_IS_INTEGER);
if(retCode != SQL_SUCCESS)
{
GetErrorMsgs(hdbc, SQL_HANDLE_ENV, strDebugMsg);
}
if(retCode == SQL_SUCCESS || retCode == SQL_SUCCESS_WITH_INFO)
{
// Allocate a ODBC connection handle handle
retCode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if(retCode == SQL_SUCCESS || retCode == SQL_SUCCESS_WITH_INFO)
{
retCode = SQLConnect( hdbc,
(UCHAR*)(LPCTSTR)strDSN,
SQL_NTS,
(UCHAR*)(LPCTSTR)strUID,
SQL_NTS,
(UCHAR*)(LPCTSTR)strPWD,
SQL_NTS);
if(retCode != SQL_SUCCESS)
{
GetErrorMsgs(hdbc, SQL_HANDLE_DBC, strDebugMsg);
OutputDebugString(strDebugMsg);
}
if(retCode == SQL_SUCCESS || retCode == SQL_SUCCESS_WITH_INFO)
{
// Allocate a statement handle
retCode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt1);
if(retCode == SQL_SUCCESS || retCode == SQL_SUCCESS_WITH_INFO)
{
// Fixup the select statement
retCode = SQLPrepare( hstmt1,
(UCHAR*)(LPCTSTR)pRecordList->m_strSelectStatement,
pRecordList->m_strSelectStatement.GetLength());
if(retCode == SQL_SUCCESS)
{
retCode = SQLExecute(hstmt1);
if(retCode == SQL_SUCCESS || retCode == SQL_SUCCESS_WITH_INFO)
{
retCode = SQLNumResultCols(hstmt1,&sColCount);
if(retCode == SQL_SUCCESS || retCode == SQL_SUCCESS_WITH_INFO)
{
if(sColCount > 0)
{
if(lColumnList.DescribeColumns(hstmt1, strExceptionMsg))
{
if(lColumnList.BindColumns(hstmt1, strExceptionMsg))
{
int i = 0;
while((retCode = SQLFetch(hstmt1)) != SQL_NO_DATA)
{
if(retCode != SQL_SUCCESS)
{
GetErrorMsgs(hstmt1, SQL_HANDLE_STMT, strExceptionMsg);
break;
}
else
{
// Class to hold the result set.
CTableColumnList* pColList = new CTableColumnList();
lColumnList.PopulateColumnListObject(pColList);
pRecordList->AddColumnListRecord(pColList);
}
}
}
}
}
}
}
else
{
GetErrorMsgs(hstmt1, SQL_HANDLE_STMT, strExceptionMsg);
}
}
else
{
GetErrorMsgs(hstmt1, SQL_HANDLE_STMT, strExceptionMsg);
}
retCode = SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
}
else
{
GetErrorMsgs(hstmt1, SQL_HANDLE_STMT, strExceptionMsg);
}
retCode = SQLDisconnect(hdbc);
}
else
{
GetErrorMsgs(hstmt1, SQL_HANDLE_STMT, strExceptionMsg);
}
}
retCode = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
}
retCode = SQLFreeHandle(SQL_HANDLE_ENV, henv);
We are at our wits end, not sure what to do next. Any help in sorting this out will be very, VERY much appreciated.