Re: SQLFetch causes errors
Quote:
Originally Posted by
Technocrat
I have a program that is fetching data using the odbc32.lib sql functions.
Show us your code, not code from the runtime library. There is little to no chance that errors are in the runtime code, so show us what your code is doing to trigger this exception.
Quote:
Its part of my reusable code that I have used for some time. The only difference is that I recently went up to VS 2010. So I am unsure what is going on. I am looking for some suggestions.
If it's C++, programs with hidden bugs can run for years until that bug is discovered, whether by adding code, upgrading the compiler, using different compiler options, etc.
Regards,
Paul McKenzie
Re: SQLFetch causes errors
Quote:
Originally Posted by
Technocrat
After some commenting out I have found it is this line:
status = SQLFetch(this->g_hStatement);
That is causing it.
If this is not a valid pointer, then you will probably get an access violation. So the question is what state that object is in? Was that object dynamically allocated, and then deleted at some point, and now you're using this invalid object? A non-NULL this pointer does not mean that the pointer is valid.
Regards,
Paul McKenzie
Re: SQLFetch causes errors
Wow Paul you are still here. You helped me out a ton a couple of years ago with some issues. :thumb:
ODBC class:
Code:
ODBC_CL::ODBC_CL()
{
g_sUserName = "";
g_sPassword = "";
g_sODBCName = "";
g_hEnv = NULL;
g_hDBC = NULL;
g_bConnected = FALSE;
}
/*##############################################################################################*/
/*==============================================================================================
Function: ODBC_CL::ODBC_CL()
In: None
Return: None
Notes: Constructor for ODBC_CL
Example: N/A
================================================================================================*/
ODBC_CL::~ODBC_CL()
{
CleanUp();
}
/*##############################################################################################*/
/*==============================================================================================
Function: ODBC_CL::Connect()
In: None
Return: int
0 - Connected OK
-1 - Failed To Connect
-2 - Missing Something
Notes: Connects To The Database Using ODBC
Example: N/A
================================================================================================*/
int ODBC_CL::Connect()
{
/*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
SQLRETURN _retcode;
/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
if(g_sUserName == "")
{
return -2;
}
else if(g_sPassword == "")
{
return -2;
}
else if(g_sODBCName == "")
{
return -2;
}
try
{
if(!g_bConnected)
{
_retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &g_hEnv);
if (SQL_SUCCESS == _retcode)
{
_retcode = SQLSetEnvAttr(g_hEnv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
if (SQL_SUCCESS == _retcode)
{
_retcode = SQLSetEnvAttr(g_hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
if (SQL_SUCCESS == _retcode)
{
_retcode = SQLAllocHandle(SQL_HANDLE_DBC, g_hEnv, &g_hDBC);
if (SQL_SUCCESS == _retcode)
{
_retcode = SQLConnectW(g_hDBC,
(SQLWCHAR*) g_sODBCName.c_str(),
(SQLSMALLINT) g_sODBCName.size(),
(SQLWCHAR*) g_sUserName.c_str(),
(SQLSMALLINT) g_sUserName.size(),
(SQLWCHAR*) g_sPassword.c_str(),
(SQLSMALLINT) g_sPassword.size());
if ( (SQL_SUCCESS == _retcode) || (SQL_SUCCESS_WITH_INFO == _retcode) )
{
g_bConnected = TRUE;
_retcode = 0;
}
else
{
SQLINTEGER sqlNativeCode = 0;
SQLSMALLINT messageLength = 0;
SQLCHAR state[SQL_MAX_MESSAGE_LENGTH + 1];
SQLCHAR description[SQL_MAX_MESSAGE_LENGTH + 1];
int i = 1;
SQLGetDiagRec(SQL_HANDLE_DBC, g_hDBC, i, (SQLWCHAR*) state, &sqlNativeCode, (SQLWCHAR*) description, SQL_MAX_MESSAGE_LENGTH, &messageLength);
AfxTrace((LPCTSTR)state);
AfxTrace(_T("\n"));
AfxTrace((LPCTSTR)description);
AfxTrace(_T("\n"));
AfxMessageBox(_T("SQLConnectW() Failed!\n") + (CString)description, MB_ICONERROR);
return -1;
}
}
else
return -1;
}
else
return -1;
}
else
return -1;
}
else
return -1;
}
}
catch(...)
{
return -1;
}
return 0;
}
/*##############################################################################################*/
/*==============================================================================================
Function: ODBC_CL::CleanUp()
In: None
Return: BOOL
TRUE - Clean Up Ok
FALSE - Clean Up Failed
Notes: Connects To The Database Using ODBC
Example: N/A
================================================================================================*/
BOOL ODBC_CL::CleanUp()
{
try
{
if (g_hEnv != NULL)
{
if (g_hDBC != NULL)
{
if (g_bConnected)
{
if(SQL_SUCCESS != (SQLDisconnect(g_hDBC)))
{
return FALSE;
}
g_bConnected = FALSE;
}
if(SQL_SUCCESS != (SQLFreeHandle(SQL_HANDLE_DBC, g_hDBC)))
{
return FALSE;
}
g_hDBC = NULL;
}
if(SQL_SUCCESS != (SQLFreeHandle(SQL_HANDLE_ENV, g_hEnv)))
{
return FALSE;
}
g_hEnv = NULL;
g_bConnected = FALSE;
}
}
catch(...)
{
return FALSE;
}
return TRUE;
}
SQL class:
Code:
SQL_CL::SQL_CL()
{
/*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
this->g_iNumberOfCols = 0;
this->g_iCurrentRecords = 0;
this->g_hStatement = NULL;
this->g_bBottomRecord = true;
this->g_bCleanUp = false;
this->CODBC = new ODBC_CL;
this->CODBC->g_sODBCName = "Search";
this->CODBC->g_sPassword = "xxxxx";
this->CODBC->g_sUserName = "xxxx";
}
/*##############################################################################################*/
/*==============================================================================================
Function: SQL_CL::~SQL_CL()
In: None
Return: None
Notes: Constructor for SQL_CL
Example: N/A
================================================================================================*/
SQL_CL::~SQL_CL()
{
/*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
SQLFreeHandle(SQL_HANDLE_STMT, this->g_hStatement);
this->g_hStatement = NULL;
delete this->CODBC;
}
/*##############################################################################################*/
/*==============================================================================================
Function: SQL_CL::SQL()
In: None
Return: BOOL
true - Completed OK
false - FAILED
Notes: Actual SQL Work
Example: N/A
================================================================================================*/
BOOL SQL_CL::SQL()
{
return true;
}
/*##############################################################################################*/
/*==============================================================================================
Function: SQL_CL::Prep()
In: None
Return: BOOL
true - Completed OK
false - FAILED
Notes: Prep SQL
Example: N/A
================================================================================================*/
BOOL SQL_CL::Prep()
{
/*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
if(this->CODBC->Connect() < 0)
{
return false;
}
if(SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, (SQLHDBC)this->CODBC->g_hDBC, &this->g_hStatement))
{
return false;
}
return true;
}
/*##############################################################################################*/
/*==============================================================================================
Function: SQL_CL::Execute
In: const char* p_cStatement
SQL Statment To Execute
Return: BOOL
true - Completed OK
false - FAILED
Notes: Execute A SQL Statement
Example: N/A
================================================================================================*/
BOOL SQL_CL::Execute(const wchar_t* p_cStatement)
{
/*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
SQLRETURN _nRet;
/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
if(p_cStatement == NULL)
{
return false;
}
if(SQL_SUCCESS != (_nRet = SQLExecDirectW(this->g_hStatement, (SQLWCHAR*) p_cStatement, SQL_NTS)))
{
return false;
}
this->g_bCleanUp = true;
return true;
}
/*##############################################################################################*/
/*==============================================================================================
Function: SQL_CL::SQL_CL()
In: None
Return: BOOL
true - Completed OK
false - FAILED
Notes: Fetch
Example: N/A
================================================================================================*/
BOOL SQL_CL::Fetch()
{
/*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
if(this->g_hStatement == NULL)
{
return false;
}
SQLRETURN status;
status = SQLFetch(this->g_hStatement);
if (status != SQL_SUCCESS && status != SQL_SUCCESS_WITH_INFO)
{
if (status == SQL_NO_DATA_FOUND) {
AfxMessageBox(_T("No data found!"), MB_ICONERROR);
}
this->g_bBottomRecord = true;
}
else
{
this->g_bBottomRecord = false;
}
this->g_iCurrentRecords++;
return true;
}
/*##############################################################################################*/
/*==============================================================================================
Function: SQL_CL::Bind()
In: None
Return: BOOL
true - Completed OK
false - FAILED
Notes: Bind
Example: N/A
================================================================================================*/
BOOL SQL_CL::Bind()
{
/*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
int _iCount = 0;
int _retcode = 0;
SWORD _Cols;
/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
if(!this->CODBC->g_bConnected)
{
return false;
}
if(this->g_hStatement == NULL)
{
return false;
}
_retcode = SQLNumResultCols(this->g_hStatement, &_Cols);
this->g_iNumberOfCols = _Cols;
if (_Cols == 0)
{
return false;
}
for(_iCount=0; _iCount < _Cols; _iCount++)
{
SQLBindCol(this->g_hStatement, (UWORD)(_iCount+1), SQL_C_CHAR,
m_Data[_iCount], MAXDATALEN, &m_DataLen[_iCount]);
}
this->g_iCurrentRecords = 0;
return true;
}
/*##############################################################################################*/
/*==============================================================================================
Function: SQL_CL::GetColDescription()
In: int iCol, string* sDesc
Return: BOOL
true - Completed OK
false - FAILED
Notes: Gets The Col Description
Example: N/A
================================================================================================*/
BOOL SQL_CL::GetColDescription(int iCol, string* sDesc)
{
/*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
SQLRETURN _nRet;
SQLSMALLINT nNameLength;
SQLSMALLINT nDataType;
SQLUINTEGER nColumnSize;
SQLSMALLINT nDecimalDigits;
SQLSMALLINT nNullable;
SQLWCHAR sColumnName[ 1024 ];
/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
if(!this->CODBC->g_bConnected)
{
return false;
}
if(this->g_hStatement == NULL)
{
return false;
}
_nRet = SQLDescribeCol( this->g_hStatement,
iCol+1,
sColumnName,
1024,
&nNameLength,
&nDataType,
&nColumnSize,
&nDecimalDigits,
&nNullable
);
if ( _nRet != 0 )
{
return false;
}
strcpy_s((char*)sDesc->c_str(), sizeof((char*)sDesc->c_str()), (char*)sColumnName);
return true;
}
/*##############################################################################################*/
/*==============================================================================================
Function: SQL_CL::Reset()
In: None
Return: BOOL
true - Completed OK
false - FAILED
Notes: Resets The Statement Pointer
Example: N/A
================================================================================================*/
BOOL SQL_CL::Reset()
{
/*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/
/*<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
if(g_hStatement == NULL)
{
return true;
}
if (!this->g_bCleanUp) {
return true;
}
this->g_bCleanUp = false;
if(SQL_SUCCESS != SQLCloseCursor(this->g_hStatement))
{
return false;
}
if(SQL_SUCCESS != SQLFreeStmt(this->g_hStatement, SQL_UNBIND))
{
return false;
}
if(SQL_SUCCESS != SQLFreeHandle(SQL_HANDLE_STMT, this->g_hStatement))
{
return false;
}
this->g_hStatement = NULL;
if(SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, this->CODBC->g_hDBC, &this->g_hStatement))
{
return false;
}
return true;
}
Calling function:
Code:
void CHTEFuelConverterView::sqlFM100() {
int i = 0;
FM100AP _temp;
if(!CSQL.Reset())
{
AfxMessageBox(_T("sqlFM100() CSQL->Reset() Failed!"), MB_ICONERROR);
return;
}
CString sql;
sql = _T("SELECT * FROM ") + htedb + _T(".FM100ap WHERE fmaicd = 'A' ORDER BY FMEQNO");
wchar_t* mychar = new wchar_t[sql.GetLength()+1];
_tcscpy(mychar, sql);
if(!CSQL.Execute(mychar))
{
AfxMessageBox(_T("sqlFM100() CSQL->Execute() Failed!"), MB_ICONERROR);
return;
}
if(!CSQL.Bind())
{
AfxMessageBox(_T("sqlFM100() CSQL->Bind() Failed!"), MB_ICONERROR);
return;
}
if(!CSQL.Fetch())
{
AfxMessageBox(_T("sqlFM100() CSQL->Fetch() Failed!"), MB_ICONERROR);
return;
}
while (!CSQL.g_bBottomRecord)
{
_temp.FMCONO = CSQL.m_Data[0];
_temp.FMEQNO = CSQL.m_Data[1];
_temp.FMAICD = CSQL.m_Data[2];
_temp.FMREFN = CSQL.m_Data[3];
_temp.FMMACH = CSQL.m_Data[4];
_temp.FMEQYR = CSQL.m_Data[5];
_temp.FMEQM = CSQL.m_Data[6];
_temp.FMMODN = CSQL.m_Data[7];
_temp.FMDESC = CSQL.m_Data[8];
_temp.FMIDNO = CSQL.m_Data[9];
_temp.FMTAGN = CSQL.m_Data[10];
_temp.FMRDCD = CSQL.m_Data[11];
_temp.FMAPSC = CSQL.m_Data[12];
_temp.FMPODY = CSQL.m_Data[13];
_temp.FMPODM = CSQL.m_Data[14];
_temp.FMPODD = CSQL.m_Data[15];
_temp.FMPONO = CSQL.m_Data[16];
_temp.FMPRDY = CSQL.m_Data[17];
_temp.FMPRDM = CSQL.m_Data[18];
_temp.FMPRDD = CSQL.m_Data[19];
_temp.FMCOST = CSQL.m_Data[20];
_temp.FMESTL = CSQL.m_Data[21];
_temp.FMSALV = CSQL.m_Data[22];
_temp.FMRPLC = CSQL.m_Data[23];
_temp.FMRFYR = CSQL.m_Data[24];
_temp.FMREQN = CSQL.m_Data[25];
_temp.FMRDY = CSQL.m_Data[26];
_temp.FMRDM = CSQL.m_Data[27];
_temp.FMRDD = CSQL.m_Data[28];
_temp.FMINSB = CSQL.m_Data[29];
_temp.FMPPGS = CSQL.m_Data[30];
_temp.FMPFT = CSQL.m_Data[31];
_temp.FMPFC = CSQL.m_Data[32];
_temp.FMPMUM = CSQL.m_Data[33];
_temp.FMPOR = CSQL.m_Data[34];
_temp.FMPFDY = CSQL.m_Data[35];
_temp.FMPFDM = CSQL.m_Data[36];
_temp.FMPFDD = CSQL.m_Data[37];
_temp.FMPFNG = CSQL.m_Data[38];
_temp.FMPFPR = CSQL.m_Data[39];
_temp.FMPFCR = CSQL.m_Data[40];
_temp.FMFCRD = CSQL.m_Data[41];
_temp.FMPMCR = CSQL.m_Data[42];
_temp.FMPMCY = CSQL.m_Data[43];
_temp.FMPMCM = CSQL.m_Data[44];
_temp.FMPMCD = CSQL.m_Data[45];
_temp.FMPMCS = CSQL.m_Data[46];
_temp.FMGWT = CSQL.m_Data[47];
_temp.FMFTIR = CSQL.m_Data[48];
_temp.FMRTIR = CSQL.m_Data[49];
CSQL.Fetch();
i++;
}
}
Re: SQLFetch causes errors
I moved:
Code:
SQL_CL CSQL;
if(!CSQL.Prep()) {
AfxMessageBox(_T("Could not connect to SQL"), MB_ICONERROR);
return;
}
Into the sqlFM100() just to see. Same result.
That pointer should be valid. I see no reason for it to not be. I am not deleting or messing with it any where else. This is the only function I am using it right now.
Re: SQLFetch causes errors
Your code has a memory leak, and depending on the length of that string, plus the number of times you loop, that leak is very large.
Code:
void CHTEFuelConverterView::sqlFM100() {
int i = 0;
FM100AP _temp;
if(!CSQL.Reset())
{
AfxMessageBox(_T("sqlFM100() CSQL->Reset() Failed!"), MB_ICONERROR);
return;
}
CString sql;
sql = _T("SELECT * FROM ") + htedb + _T(".FM100ap WHERE fmaicd = 'A' ORDER BY FMEQNO");
wchar_t* mychar = new wchar_t[sql.GetData() + 1];
_tcscpy(mychar, sql);
if(!CSQL.Execute(mychar))
{
AfxMessageBox(_T("sqlFM100() CSQL->Execute() Failed!"), MB_ICONERROR);
return;
}
//...
If you exit that function, you get a memory leak since that memory was never deallocated.
Why did you need to create another buffer? Why not just do this?
Code:
sql = _T("SELECT * FROM ") + htedb + _T(".FM100ap WHERE fmaicd = 'A' ORDER BY FMEQNO");
if( !CSQL.Execute((LPCTSTR)sql) )
//...
Regards,
Paul McKenzie
Re: SQLFetch causes errors
I was playing with it for another reason. I changed it back. Same result.
Re: SQLFetch causes errors
Code:
CHTEFuelConverterView
Where is this created? If this is not valid, then any members that it has are also not valid either, including the CSQL object.
We need to see the flow of how these objects are created. Just showing classes doesn't reveal where or when these objects exist.
Regards,
Paul McKenzie
Re: SQLFetch causes errors
Its an MFC SDI. That's created in the CntrItem.h
Code:
// CntrItem.h : interface of the CHTEFuelConverterCntrItem class
//
#pragma once
class CHTEFuelConverterDoc;
class CHTEFuelConverterView;
class CHTEFuelConverterCntrItem : public CRichEditCntrItem
{
DECLARE_SERIAL(CHTEFuelConverterCntrItem)
// Constructors
public:
CHTEFuelConverterCntrItem(REOBJECT* preo = NULL, CHTEFuelConverterDoc* pContainer = NULL);
// Note: pContainer is allowed to be NULL to enable IMPLEMENT_SERIALIZE
// IMPLEMENT_SERIALIZE requires the class have a constructor with
// zero arguments. Normally, OLE items are constructed with a
// non-NULL document pointer
// Attributes
public:
CHTEFuelConverterDoc* GetDocument()
{ return reinterpret_cast<CHTEFuelConverterDoc*>(CRichEditCntrItem::GetDocument()); }
CHTEFuelConverterView* GetActiveView()
{ return reinterpret_cast<CHTEFuelConverterView*>(CRichEditCntrItem::GetActiveView()); }
// Implementation
public:
~CHTEFuelConverterCntrItem();
#ifdef _DEBUG
virtual void AssertValid() const;
virtual void Dump(CDumpContext& dc) const;
#endif
};
Re: SQLFetch causes errors
This is definitely no good:
Code:
strcpy_s((char*)sDesc->c_str(), sizeof((char*)sDesc->c_str()), (char*)sColumnName);
You cannot do this. You are assuming that std::string has a buffer you can just write into like that. This code is ill-formed, and probably the reason you have a crash now.
Regards,
Paul McKenzie
Re: SQLFetch causes errors
Though I see that as being an issue now that I look at it, that function never gets called. :(
Re: SQLFetch causes errors
ODBC uses TCHAR (or SQLTCHAR's) as the character type. Secondly, depending on your compilation switches, a std::string is not compatible with TCHAR, unless you compiled as MBCS (non-Unicode). If you compiled with UNICODE, then std::string isn't going to help. The right string class is actually std::wstring for UNICODE, but then, you have to keep track of all of these things.
So that one line of code does three erroneous things:
1) Overwrites or attempts to write into a std::string.
2) You are attempting to "cast" from one string type (SQLTCHAR/TCHAR) to another (char). String types cannot be casted away like this -- they must be converted from one type to another.
3) More exactly, an offshoot of 2) -- You are assuming that std::string is the correct type to use for retrieval/setting SQL TCHAR strings. Only if compiled with MBCS is std::string compatible with SQLTCHAR.
Instead of all of this and trying to figure out the string type to use, you can define a SQL type that holds characters and is writable, regardless of whether the build is UNICODE or not (neither std::string or std::wstring is writable):
Code:
#include <vector>
//...
typedef std::vector<SQLTCHAR> SQLCHARArray;
Since you have a vector of SQLTCHAR, you now no longer worry about the compilation settings (UNICODE/MBCS). Also, vectors are writable, so that problem is taken care of. Now, you just use the vector functions such as resize(), push_back(), etc. to resize the vector. In functions that require a pointer to a TCHAR, then supply the address of the first element:
Code:
void SomeFunction(SQLTCHAR* p)
{
}
//...
SQLCHARArray array(100); // fits 100 characters
SomeFunction(&array[0]); // call the function
The only thing you don't have when you use vector are the string functions for concatenation, substrings, etc.. But these are easily implemented using the vector functions, plus STL algorithms functions.
Regards,
Paul McKenzie
Re: SQLFetch causes errors
Quote:
Originally Posted by
Technocrat
Though I see that as being an issue now that I look at it, that function never gets called. :(
That may be just an indication of other issues. It needs to be changed, and basically your entire approach to character handling and SQL needs to change also (read my previous post).
Regards,
Paul McKenzie
Re: SQLFetch causes errors
Ok I will look at it tomorrow. Thanks for your help. I will see if I can get this knocked out.
Re: SQLFetch causes errors
I had the same crash. After upgrading to VS 2013 I got more relevant info.
Apparently I could not call the Windows COM interface that I was using through the Message Loop (A toolbar command like yours).
This was an INTERMITTENT error which made it difficult to debug.
If you encounter this issue, you will need to create a new interface for COM communication outside of the message pump.
Regards.
