我在开发一个电信应用程序,该程序需要对呼叫进行详细记录,每个呼叫可能需要访问数据库(都是 INSERT、UPDATE)至少5次,我是用“短连接”的方式来访问数据库的,所谓“短连接”就是每个 SQL 语句都创建一个新的连接,用完了就关闭之。我用 MySQL 5.0.22、ODBC 来连接数据,但是发现句柄数一直在增长,一晚上能长 10万左右,非常恐怖,我将程序的日志打开,发现每个连接都关闭了,对象也释放了,内存没有增长/泄露,但是句柄一直长,不知何故。具体到代码实现,我是这样做的:
从 CDatabase 继承一个类:SQLTask,并重写 BindParameters 函数,每次需要访问数据库时,创建一个 SQLTask 对象,并放到一个列表中,然后发一个消息给 SQL 线程,SQL线程得到消息后,从列表中取出该 SQLTask,然后 OpenDatabase--->ExecuteSQL,执行完毕后,再发送一个消息到主线程,主线程收到该消息后把 SQLTask 释放掉
下面是我的部分代码:
//################################### SQLTask.h ###########################
// MFC 数据库
#include <afxdb.h>#include "Task.h"
#include "SQLParameter.h"class SQLUser;class SQLTask : public Task , public CDatabase
{
public:
static SQLTask * GetAndRemoveFirstSQLTask (void);
static void AddSQLTask (SQLTask *pSQLTask);
bool OpenDatabase ();
void CloseDatabase (void);
bool DoExecuteSQL (); int SetReturnParameter (SQLParameter * pParam);
SQLParameter * GetReturnParameter (void);
int AddParameter (SQLParameter & param);
int AddParameter (SQLParameter * pParam);
int SetParameters (CArray <SQLParameter *, SQLParameter *> & arrayInputOutputParameters);
int AddParameters (CArray <SQLParameter *, SQLParameter *> & arrayInputOutputParameters);
CArray <SQLParameter *, SQLParameter *> & GetInputOutputParameters (void); void BindParameters (HSTMT hStmt);
SQLRETURN GetSQLDiagnosticInformation_Record (SQLSMALLINT HandleType, SQLHANDLE hHandle, SQLRETURN returnCode);
//SQLRETURN GetSQLDiagnosticInformation_Field (SQLSMALLINT HandleType, SQLHANDLE hHandle, SQLRETURN returnCode); void SetSQLUser (SQLUser *pSQLUser);
SQLUser * GetSQLUser (void);
bool IsTaskOver (void);
SQLTask & operator =(SQLTask &originalSQLTask);
SQLTask (SQLTask & originalSQLTask);
SQLTask (
SQLUser *pSQLUser,
const char *strConnectionString,
const char *strSQLStatement,
SQLParameter * pReturnParameter,
CArray<SQLParameter *, SQLParameter *> & arrayIOParameters
);
SQLTask();
virtual ~SQLTask();protected:
// 连接字符串
char m_strConnectionString[256]; // SQL 语句
char m_strSQLStatement[1024]; // 执行该 SQL 所需要的输入、输出参数(不包括返回参数)
CArray <SQLParameter *, SQLParameter *> m_ArrayAllInputOutputParameters; // 执行该 SQL 所需要返回参数
SQLParameter * m_pReturnParameter;private:
void FreeReturnParameter (void);
void FreeInputOutputParameters (void);
void FreeAllParameters (void); void CopyProperties (SQLTask &originalSQLTask);
void Init (void); SQLUser * m_pSQLUser;
};
从 CDatabase 继承一个类:SQLTask,并重写 BindParameters 函数,每次需要访问数据库时,创建一个 SQLTask 对象,并放到一个列表中,然后发一个消息给 SQL 线程,SQL线程得到消息后,从列表中取出该 SQLTask,然后 OpenDatabase--->ExecuteSQL,执行完毕后,再发送一个消息到主线程,主线程收到该消息后把 SQLTask 释放掉
下面是我的部分代码:
//################################### SQLTask.h ###########################
// MFC 数据库
#include <afxdb.h>#include "Task.h"
#include "SQLParameter.h"class SQLUser;class SQLTask : public Task , public CDatabase
{
public:
static SQLTask * GetAndRemoveFirstSQLTask (void);
static void AddSQLTask (SQLTask *pSQLTask);
bool OpenDatabase ();
void CloseDatabase (void);
bool DoExecuteSQL (); int SetReturnParameter (SQLParameter * pParam);
SQLParameter * GetReturnParameter (void);
int AddParameter (SQLParameter & param);
int AddParameter (SQLParameter * pParam);
int SetParameters (CArray <SQLParameter *, SQLParameter *> & arrayInputOutputParameters);
int AddParameters (CArray <SQLParameter *, SQLParameter *> & arrayInputOutputParameters);
CArray <SQLParameter *, SQLParameter *> & GetInputOutputParameters (void); void BindParameters (HSTMT hStmt);
SQLRETURN GetSQLDiagnosticInformation_Record (SQLSMALLINT HandleType, SQLHANDLE hHandle, SQLRETURN returnCode);
//SQLRETURN GetSQLDiagnosticInformation_Field (SQLSMALLINT HandleType, SQLHANDLE hHandle, SQLRETURN returnCode); void SetSQLUser (SQLUser *pSQLUser);
SQLUser * GetSQLUser (void);
bool IsTaskOver (void);
SQLTask & operator =(SQLTask &originalSQLTask);
SQLTask (SQLTask & originalSQLTask);
SQLTask (
SQLUser *pSQLUser,
const char *strConnectionString,
const char *strSQLStatement,
SQLParameter * pReturnParameter,
CArray<SQLParameter *, SQLParameter *> & arrayIOParameters
);
SQLTask();
virtual ~SQLTask();protected:
// 连接字符串
char m_strConnectionString[256]; // SQL 语句
char m_strSQLStatement[1024]; // 执行该 SQL 所需要的输入、输出参数(不包括返回参数)
CArray <SQLParameter *, SQLParameter *> m_ArrayAllInputOutputParameters; // 执行该 SQL 所需要返回参数
SQLParameter * m_pReturnParameter;private:
void FreeReturnParameter (void);
void FreeInputOutputParameters (void);
void FreeAllParameters (void); void CopyProperties (SQLTask &originalSQLTask);
void Init (void); SQLUser * m_pSQLUser;
};
解决方案 »
- 庆祝下 - 提前完成 今年的计划 (转型第一年)散分
- 请问用VC++ 2005写的C++程序,运行时要不要安装.NET Framework呢?
- CMainFrame怎么在创建时设置成TopMost
- 内存分配问题急.
- vc2005编写的带有浮点操作的程序用exe压缩软件压缩后运行出现floating point not loaded错误
- 怎样获得密码框内的字符串?
- ★★★救急了!!!VC 6.0 HtmlView打开网页失败,BeforeNavigate执行后没进入NavigateComplete,会执行什么函数吗?为什么?
- 有一个考试问题????
- 在MDI窗体中,怎样对应不同的菜单,打开不同的formview?并且每种formview只能打开一个?
- VC编程的入门书籍!
- 关于vc读取oracle 数据集的问题。oracle的日期格式转化成vc可以识别的类型
- GetModuleHandle(0)的真正意思?
{
logger.Log (LL_TRACE, "SQLTask %s::BindParameters: Binding...", GetName()); int i, paramNO=0, inputOutputParameterCount;
inputOutputParameterCount = m_ArrayAllInputOutputParameters.GetSize ();
SQLRETURN rc;
SQLParameter *pParam; if (m_pReturnParameter)
{ // 有返回参数
paramNO ++; // paramNO = 1; pParam = m_pReturnParameter;
rc = SQLBindParameter (
hStmt, // 语句的 Handle
paramNO, // 参数编号,以 1 开始
pParam->GetParamType(), // 参数的输入输出类型
pParam->GetValueType(), // C 语言的参数类型
pParam->GetParamDataType(), // 参数类型
pParam->GetColumnSize(), // 列宽
pParam->GetDecimalDigits(), // 小数位数
pParam->GetParamValue(), // 输出缓冲区/参数值的指针
pParam->GetBufferLength(), // 输出缓冲区的大小
pParam->GetValueLengthPointer() // 值的大小/字符串长度/数据长度等等
); logger.Log (LL_TRACE, "\tBind Parameter %d (%d:%-s): return %d",
paramNO,
pParam->GetParamType (),
pParam->GetParamTypeDescription (),
rc
); ASSERT ( (rc == SQL_SUCCESS) || (rc == SQL_SUCCESS_WITH_INFO) );
if ( (rc == SQL_ERROR) || (rc == SQL_SUCCESS_WITH_INFO))
{
GetSQLDiagnosticInformation_Record (SQL_HANDLE_STMT, hStmt, rc);
}
}
else
{ // 无返回参数
} //
for (i=0; i<inputOutputParameterCount; i++)
{
paramNO ++; pParam = m_ArrayAllInputOutputParameters[i];
rc = SQLBindParameter (
hStmt, // 语句的 Handle
paramNO, // 参数编号,以 1 开始
pParam->GetParamType(), // 参数的输入输出类型
pParam->GetValueType(), // C 语言的参数类型
pParam->GetParamDataType(), // 参数类型
pParam->GetColumnSize(), // 列宽
pParam->GetDecimalDigits(), // 小数位数
pParam->GetParamValue(), // 输出缓冲区/参数值的指针
pParam->GetBufferLength(), // 输出缓冲区的大小
pParam->GetValueLengthPointer() // 值的大小/字符串长度/数据长度等等
); logger.Log (LL_TRACE, "\tBind Parameter %d (%d:%-s:%s): return %d",
paramNO,
pParam->GetParamType (),
pParam->GetParamTypeDescription (),
(
((pParam->GetParamType () == SQL_PARAM_INPUT) || (pParam->GetParamType () == SQL_PARAM_INPUT_OUTPUT)) ? pParam->GetParamValue() : ""
),
rc
); ASSERT ( (rc == SQL_SUCCESS) || (rc == SQL_SUCCESS_WITH_INFO) );
if ( (rc == SQL_ERROR) || (rc == SQL_SUCCESS_WITH_INFO))
{
GetSQLDiagnosticInformation_Record (SQL_HANDLE_STMT, hStmt, rc);
}
} logger.Log (LL_TRACE, "SQLTask %s::BindParameters: Bind DONE !", GetName());
}/*
描述:
打开数据库/建立数据库连接
(该 SQL 操作是在 SQL 线程中执行的)
说明:
*/
bool SQLTask::OpenDatabase()
{
bool rc = false;
BOOL rc2; try
{
if (CDatabase::IsOpen ()) // add by LiuYan; 2006.06.27; 如果已经打开,则不要重复打开之,否则可能造成句柄增加
{
logger.Log (LL_INFO, "SQLTask::OpenDatabase: Database (%s) is already opened.", m_strConnectionString);
rc = true;
}
else
{
logger.Log (LL_DEBUG, "SQLTask::OpenDatabase: Opening database (%s)...", m_strConnectionString);
//SetLoginTimeout (5); // time out
//SetQueryTimeout (10); // time out
rc2 = CDatabase::OpenEx (m_strConnectionString, CDatabase::noOdbcDialog);
// Nonzero if the connection is successfully made;
// otherwise 0 if the user chooses Cancel when presented a dialog box asking for more connection information.
// In all other cases, the framework throws an exception.
if (! rc2)
{
logger.Log (LL_WARNING, "SQLTask::OpenDatabase: Open database failed ! ConnectionString=%s", m_strConnectionString);
rc = false;
return rc;
}
else
{
rc = true;
logger.Log (LL_DEBUG, "SQLTask::OpenDatabase: Open database DONE !");
}
}
}
catch (CDBException *pException)
{
//pException->GetErrorMessage ();
pException->m_nRetCode;
pException->m_strError;
pException->m_strStateNativeOrigin; logger.Log (LL_WARNING, "SQLTask::OpenDatabase: CDBException occur! ConnectionString=\n%s\nSQL=\n%s\nRetCode=%d, Error=%s, StateNativeOrigin=%s",
m_strConnectionString,
m_strSQLStatement,
pException->m_nRetCode,
pException->m_strError,
pException->m_strStateNativeOrigin
); pException->Delete ();
rc = false;
return rc;
}
catch (CMemoryException *pException)
{
//pException->GetErrorMessage (
TCHAR strError[255];
pException->GetErrorMessage (strError, sizeof(strError));
logger.Log (LL_WARNING, "SQLTask::OpenDatabase: CMemoryException occur ! ConnectionString=\n%s\nSQL=\n%s\nError=%s",
m_strConnectionString,
m_strSQLStatement,
strError
); pException->Delete ();
rc = false;
return rc;
}
catch (...)
{
logger.Log (LL_WARNING, "SQLTask::OpenDatabase: Exeception occur ! ConnectionString=\n%s\nSQL=\n%s",
m_strConnectionString,
m_strSQLStatement
); rc = false;
return rc;
} return rc;
}
void SQLTask::CloseDatabase()
{
if (CDatabase::IsOpen())
{
logger.Log (LL_TRACE, "SQLTask %s::CloseDatabase: Closing...", GetName());
CDatabase::Close ();
}
}
/*
描述:
执行 SQL 操作
(该 SQL 操作是在 SQL 线程中执行的)
说明:
*/
bool SQLTask::DoExecuteSQL()
{
bool rc = false;
try
{
if (CDatabase::IsOpen())
{
logger.Log (LL_DEBUG, "SQLTask::DoExecuteSQL: Executing SQL (%s)...", m_strSQLStatement);
CDatabase::ExecuteSQL (m_strSQLStatement);
logger.Log (LL_DEBUG, "SQLTask::DoExecuteSQL: Executing SQL DONE !"); // 设置标志为“成功”
m_bTaskSuccessful = true;
rc = true;
}
else
{
logger.Log (LL_WARNING, "SQLTask::DoExecuteSQL: Database is not opened !");
}
}
catch (CDBException *pException)
{
//pException->GetErrorMessage ();
pException->m_nRetCode;
pException->m_strError;
pException->m_strStateNativeOrigin; logger.Log (LL_WARNING, "SQLTask::DoExecuteSQL: CDBException occur! ConnectionString=\n%s\nSQL=\n%s\nRetCode=%d, Error=%s, StateNativeOrigin=%s",
m_strConnectionString,
m_strSQLStatement,
pException->m_nRetCode,
pException->m_strError,
pException->m_strStateNativeOrigin
); pException->Delete ();
rc = false;
return rc;
}
catch (CException *pException)
{
//pException->GetErrorMessage (
TCHAR strError[255];
pException->GetErrorMessage (strError, sizeof(strError));
logger.Log (LL_WARNING, "SQLTask::DoExecuteSQL: CException occur ! ConnectionString=\n%s\nSQL=\n%s\nError=%s",
m_strConnectionString,
m_strSQLStatement,
strError
); pException->Delete ();
rc = false;
return rc;
}
catch (...)
{
logger.Log (LL_WARNING, "SQLTask::DoExecuteSQL: Exeception occur ! ConnectionString=\n%s\nSQL=\n%s",
m_strConnectionString,
m_strSQLStatement
); rc = false;
return rc;
} return rc;
}
BOOL SQLUser::RequestExecuteSQL()
{
BOOL rc = FALSE;
DWORD errorCode; m_pCurrentSQLTask =
new SQLTask (
this,
m_strConnectionString,
m_strSQLStatement,
m_pReturnParameter,
m_ArrayAllInputOutputParameters
); if (m_pCurrentSQLTask)
{
SQLTask::AddSQLTask (m_pCurrentSQLTask); rc = PostThreadMessage (
theApp.GetSQLThreadId(),
WM_BEGIN_EXECUTE_SQL,
0,
(LPARAM)this
);
}
else
{
}
logger.Log (LL_TRACE, "SQLUser::RequestExecuteSQL: PostThreadMessage return %d. (ThreadID=%d, SQLThreadID=%d)", rc, GetCurrentThreadId (), theApp.GetSQLThreadId()); if (! rc)
{
errorCode = GetLastError ();
}
ASSERT (rc);
if (! rc)
{
logger.Log (LL_ERROR, "SQLUser::RequestExecuteSQL: PostThreadMessage failed! ErrorCode=%u, (ThreadID=%d, SQLThreadID=%d)", errorCode, GetCurrentThreadId (), theApp.GetSQLThreadId()); OnSQLTaskOver ();
} return rc;
}
DWORD WINAPI SQLThreadProc (LPVOID lpParam)
{
SQLTask *pSQLTask = NULL;
//SQLUser *pSQLUser = NULL;
BOOL rc;
MSG msg; bool bOpened; // In the thread to which the message will be posted,
// call PeekMessage(&msg, NULL, WM_USER, WM_USER, PM_NOREMOVE)
// to force the system to create the message queue
PeekMessage (&msg, NULL, WM_USER, WM_USER, PM_NOREMOVE); // Set the event, to indicate that the thread is ready to receive posted messages
rc = SetEvent (theApp.GetSQLEvent()); // 激活主线程的 WaitForSingleObject ()
ASSERT (rc); while (true)
{
rc = GetMessage (&msg, NULL, 0, 0);
if (rc == 0) // If the function retrieves the WM_QUIT message, the return value is zero.
{ // 退出
break;
}
else if (rc == -1)
{ // 出现严重的程序错误
rc = GetLastError();
return 1;
} //
if (msg.message == WM_BEGIN_EXECUTE_SQL)
{ logger.Log (LL_TRACE, "SQLThreadProc(ThreadID=%u), WM_BEGIN_EXECUTE_SQL: Executing SQL...", GetCurrentThreadId()); // 处理 SQL while (! g_listAllPendingSQLTasks.IsEmpty())
{
pSQLTask = SQLTask::GetAndRemoveFirstSQLTask ();
if (pSQLTask)
{
if (!pSQLTask->IsCancelled())
{
bOpened = pSQLTask->OpenDatabase ();
if (!pSQLTask->IsCancelled()) // 因为打开数据库需要一定的时间,所以,为了防止主线程取消这个 SQL 任务而不执行必要的操作时,就需要再判断一下……
{
if (bOpened)
{ // 打开数据库成功
pSQLTask->DoExecuteSQL ();
}
else
{ // 打开数据库失败
} if (!pSQLTask->IsCancelled()) // 因为执行 SQL 需要一定的时间,所以,为了防止主线程取消这个 SQL 任务而不执行必要的操作时,就需要再判断一下……
{
// 通知主线程:执行结束。(主线程需要判断执行的结果)
rc = eventSinkWindow.PostMessage (WM_EXECUTE_SQL_ENDED, 0, (long)pSQLTask);
ASSERT (rc); continue; // 重要!!!一定要,否则 SQL 任务将被视作已经被取消
}
else
{
logger.Log (LL_DEBUG, "SQLThreadProc: SQLTask %s is cancelled after DoExecuteSQL()", pSQLTask->GetName());
}
}
else
{
logger.Log (LL_DEBUG, "SQLThreadProc: SQLTask %s is cancelled after OpenDatabase() and before DoExecuteSQL()", pSQLTask->GetName());
}
}
else
{
logger.Log (LL_DEBUG, "SQLThreadProc: SQLTask %s is cancelled before OpenDatabase()", pSQLTask->GetName());
} // SQLTask 已经被取消,删除之……
ASSERT (pSQLTask->IsCancelled());
delete pSQLTask;
}
else
{
ASSERT (false);
}
}
}
else if ((msg.message == 0) || (msg.message == WM_QUIT))
{ // 退出
break;
}
} return 0;
}