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
No idea of why you get different results when both are calling SQLGetDiagRec.
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.
#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
#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
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.
' ========================================================================================
' 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.
Many thanks Josè!
As always, the devil is in the details.
Eigil