Memory Leak in SQLExecuteDirect()

[apoorva_raje]

hi This is Apoorva raje.I am facing a problem with SqlExecuteDirect() function, my piece of code is doing deleting all the recods from 12 different table and then insert new records in those tables. each table will have approximate 100 to 1,00,000 records .For deletion i copy the query into a char array and then first allocate a statement handle execute it by SqlExecuteDirect()function and then free the statement handle but even we are freeing the statement handle the memory is not freed and its accumulating around 12mb. the code is given below: //delete ARCDataSet //Same code for deleting data for 12 other tables rc = SQLAllocHandle(SQL_HANDLE_STMT,ConHandle,&StmtHandle); memset(szSQL,0,1024); sprintf(szSQL,"delete from ARCDataSet"); rc = SQLExecDirect(StmtHandle,(SQLCHAR*)szSQL,strlen(szSQL)); rc = SQLFreeHandle(SQL_HANDLE_STMT,StmtHandle); // rc = SQLAllocHandle(SQL_HANDLE_STMT,ConHandle,&StmtHandle); memset(szSQL,0,1024); int m_Dsq =0; m_Dsq = atoi(((ARCAdminDlg*)(AfxGetMainWnd()))->mp_ARCSecDb->m_chDSQ ); sprintf(szSQL,"INSERT INTO ARCDS (ARCDSQ,DBServer,DBSuperUser,DBSuPwd,DBSignature,DBComments,nDsq,noCats,noTabs,noCols,noSTC,noScopes,noDataSets,noEntrances,noEntSets,noPermits) values (\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',%d ,%d , %d, %d,%d ,%d , %d,%d ,%d, %d );" rc = SQLExecDirect(StmtHandle,(SQLCHAR*)szSQL,strlen(szSQL)); rc = SQLFreeHandle(SQL_HANDLE_STMT,StmtHandle); // 3.1 end parameters writing into ARCDS ------------------ user details start ------------------------------------*/ // 3.2 begin Isert into Security db , user details rc = SQLAllocHandle(SQL_HANDLE_STMT,ConHandle,&StmtHandle); unsigned int nUsq = ((ARCAdminDlg*)(AfxGetMainWnd()))->mp_ARCSecDb->m_noUsers; int iVal=0; for ( int nIdx = 0; nIdx < 35000 ; nIdx++) { iVal=0; memset(szSQL,0,1024); sprintf(szSQL,"insert into ARCdstc (type,dstcSeq,ARCdstcName) values (\'%s\',%d,\'%s\')", chType, ((ARCAdminDlg*)(AfxGetMainWnd()))->mp_ARCSecDb-mp_arcTables[nIdx]->tsq , ((ARCAdminDlg*)(AfxGetMainWnd()))->mp_ARCSecDb->mp_arcTables[nIdx]->tableName ); rc = SQLExecDirect(StmtHandle,(SQLCHAR*)szSQL,strlen(szSQL)); } rc = SQLFreeHandle(SQL_HANDLE_STMT,StmtHandle); // 3.2 end Isert into Security db , user details //Same code to insert data into 10 other different tables. Thanks in advance , i will be thankfull to all.

[James R Twine]

How is the memory for szSQL managed?  I cannot see where it is allocated and freed.    Some optimization points, take them or leave them (ignoring buffer overrun protection that should be added):      1: You do not need to memset(...) the string buffer before you use it - sprintf(...) will NUL-terminiate it for you      2: Do not use sprintf(...) as a replacement for strcpy(...) (in fact, that static string does not really need to be copied to a buffer to be executed)      3: sprintf(...) returns the number of characters it put into the buffer, excluding the terminating NUL so you can use that value to eliminate the calls to strlen(...)      4: I would cache the result of ((ARCAdminDlg*)(AfxGetMainWnd()))->mp_ARCSecDb->mp_arcTables[nIdx] within the loop and not evaluate it multiple times.    In your loop body, implementing the tips in #1 and #3 will eliminate 70000 operations from your loop.    Peace!

-=- James

---

If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Avoid driving a vehicle taller than you and remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
DeleteFXPFiles & CheckFavorites (Please rate this post!)

[apoorva_raje]

Thanks a lot for ur reply szSQL is a character array declared as char szSQL[1024]={0}; i have tried both the option but not much improvement in the memory accumulation. i suppose the problem is with SQLFreeHandle(SQL_HANDLE_STMT,StmtHandle),it does not free the full memory which is been allcated by rc = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&EnvHandle).