Using CODBC class, unable to retrieve error information Exedirect

[Eigil Dingsor]

Hi,
Using Jose`s ODBC class against Oracle database. When I execute a SQL statement that I know has error with pStmt->ExecDirect (zSQLstring) and want to retrieve error information with pstmt->GetErrorInfo I only get "Function sequence error" as error information back.
When I log on to the same database with native ODBC functions and run the same sql query with wrong date format I get the more verbose "Not a valid month" error information back as expected.
using this function in native ODBC for getting error info:
FUNCTION DBaddErr(Stmt AS sqlhandle, sError AS STRING) AS LONG

   dim iLenRet  AS short
   dim iRecNum  AS short
   dim iErr     AS LONG
   dim zState   AS zstring*6
   dim zMsg     AS zstring * 255
   dim ret as long
   iRecNum = 1
   ret = SQLGetDiagRec(SQL_HANDLE_STMT, Stmt, iRecNum,@zState,@iErr,@zMsg,sizeof(zMsg),@iLenRet)
   
   sError = sError + "Error " + str(iErr) + "  " + TRIM(zState) + " " + TRIM(zMsg)
   function = ret
END FUNCTION

When I look at Jose's COdbcStmt.Geterrorinfo code it seems to be doing the same as I do, but the results are different.
Anyone experienced the same as me?

Eigil

[José Roca]

No idea of why you get different results when both are calling SQLGetDiagRec.

[Eigil Dingsor]

Hi again.
I understand there was little to act upon without some code. here's two examples. One with CODBC class and one with native ODBC.
Error messages differ with exact same(wrong) sql statement using SQLExedirect!

CODBC syntax.

Code Select Expand

#include once "Afx/COdbc/COdbc.inc"
USING Afx

' // Error callback procedure
SUB ODBC_ErrorCallback (BYVAL nResult AS SQLRETURN, BYREF wszSrc AS WSTRING, BYREF wszErrorMsg AS WSTRING)
   PRINT "Error: " & STR(nResult) & " - Source: " & wszSrc
   IF LEN(wszErrorMsg) THEN PRINT wszErrorMsg
END SUB

' // Create a connection object and connect with the database
DIM wszConStr AS WSTRING * 260 = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\northwind\biblio.mdb"
DIM pDbc AS CODBC = wszConStr
IF pDbc.Handle = NULL THEN PRINT "Unable to create the connection handle" : SLEEP : END
dim ret as long
' // Set the address of the error callback for the connection object
pDbc.SetErrorProc(@ODBC_ErrorCallback)

' // Allocate an statement object
DIM pStmt AS COdbcStmt = @pDbc
IF pStmt.Handle = NULL THEN PRINT "Unable to create the statement handle" : SLEEP : END

' // Generate a result set
ret = pStmt.ExecDirect ("SELECT gg FROM Authors ORDER BY Author")

if ret = sql_error then
   print "Exedirect error:", pstmt.geterrorinfo
   
end if
sleep
end

Native ODBC

Code Select Expand

#include once "Afx/COdbc/COdbc.inc"
USING Afx
 
  FUNCTION DBaddErr(Stmt AS sqlhandle, sError AS STRING) AS LONG

   dim iLenRet  AS short
   dim iRecNum  AS short
   dim iErr     AS LONG
   dim zState   AS wstring*6
   dim zMsg     AS wstring * 255
   dim ret as long
   iRecNum = 1
   ret = SQLGetDiagRecW(SQL_HANDLE_STMT, Stmt, iRecNum,@zState,@iErr,@zMsg,sizeof(zMsg),@iLenRet)
   
   sError = sError + "Error " + str(iErr) + "  " + TRIM(zState) + " " + TRIM(zMsg)
   function = ret
END FUNCTION
   


Dim As SQLHANDLE Ptr hEnv,hConn,hStmt
  Dim As SQLRETURN h
  Dim As wString*1024 sConnectString = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\northwind\biblio.mdb"
 
  Dim As Integer iStrLen
  dim sError as string
  dim sConn as wstring* 1048
  dim ret as long
' // Create a connection object and connect with the database
'connect
h = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, @hEnv)
'set env attributes
   h = SQLSetEnvAttr(hEnv,SQL_ATTR_ODBC_VERSION, Cast(Any Ptr,SQL_OV_ODBC3),0)
' // alloc conn handle
h = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, @hConn)
'connect to driver
h=SQLDriverConnectW(hConn, 0, @sConnectString, sizeof(sConnectString), @sConn, Sizeof(sConn),Cast(Any Ptr,@iStrLen), SQL_DRIVER_COMPLETE)
'Alloc stmt handle
SQLAllocHandle(SQL_HANDLE_STMT,hConn,@hStmt)
  dim zSqlString as wstring*250
  zSqlString = "SELECT gg FROM Authors ORDER BY Author"
' // Generate a result set

  ret =  SQLExecDirect(hStmt,@zSqlString,Len(zSqlString))
if ret = sql_error then
     DBaddErr(hStmt, sError)
   print "Exedirect error:", serror
   
end if
sleep
end


[José Roca]

In the ExecDirect method I was retrieving the column names after the call to SQLExecDirectW, but I should not do that if the call has failed, so I have added a check: IF m_Result = SQL_ERROR THEN RETURN m_Result. Otherwise, the call to this.NumResultCols is changing the diagnosis record.

Code Select Expand


' ========================================================================================
' Executes the specified statement.
' ========================================================================================
PRIVATE FUNCTION COdbcStmt.ExecDirect (BYREF wszSqlStr AS WSTRING) AS SQLRETURN
   m_Result = SetResult(SQLExecDirectW(m_hStmt, @wszSqlStr, SQL_NTS), "COdbcStmt.ExecDirect", SQL_HANDLE_STMT, m_hStmt)
   ' // Retrieve the names of the fields (columns)
   IF m_Result = SQL_ERROR THEN RETURN m_Result
   ERASE rgColNames
   DIM numCols AS SQLSMALLINT = this.NumResultCols
   IF numCols THEN
      REDIM rgColNames(1 TO numCols)
      FOR idx AS LONG = 1 TO numCols
         rgColNames(idx) = this.ColName(idx)
      NEXT
   END IF
   RETURN m_Result
END FUNCTION
' ========================================================================================


BTW, in the code that you posted you must change SQLExecDirect to SQLExecDirectW.

Thanks for reporting it. In te attached file I have added the error check in the ExecDirect and Execute methods.

[Eigil Dingsor]

Many thanks Josè!
As always, the devil is in the details.
Eigil