problems with SQLExecDirect (again)
Posted: Tue Jan 08, 2008 5:04 pm
Hi,
I read the good postings on SQLExecDirect, they gave me some good things to try, but I'm still not fixing our problem. High level bits - our ODBC against Oracle 10g is working, I can import metadata. When we try execute an Oracle command with SQLExecDirect in a "Before" routine, it fails. The DB connect commands seem to work. Any kind of of Oracle command seems to fail with the same error message (100, I think this is an ODBC.H error message) in SQLExecDirect, selects, exec's, desc's, commands with bad syntax, commands with good syntax that run in Toad. The code in full debug mode is shown below. Some important entries from the log file are shown directly below:
Reply from calling SQLAllocStmt
test..BeforeJob (Reporting): Successfully called SQLExecDirect
Reply from analyzing the Error return code
test..BeforeJob (): status of SQL100
Reply from analyzing the SQLExecDirect Call
est..BeforeJob (Reporting): Error calling SQLExecDirect.
*** the Loop analyzing the return code gave nothing ***
Here is the code in full debug mode. The Oracle command we wanted to run was the update statement:
status = SQLAllocStmt(hConn, hStmt)
Begin Case
Case status = SQL.SUCCESS
Call DSLogInfo("Successfully called SQLExecDirect", "Reporting")
Case status = SQL.SUCCESS.WITH.INFO
Msg = "Successfully called SQLExecDirect with information."
Temp = SQLError(hEnv,hConn,hStmt,SQLState,DBMSCode,ErrText)
Msg<-1> = "SQL State = " : SQLState : ", DBMS code = " : DBMSCode
Msg<-1> = ErrText
Call DSLogInfo(Msg, "Reporting")
Case @TRUE
Msg = "Error calling SQLExecDirect."
Temp = SQLError(hEnv,hConn,hStmt,SQLState,DBMSCode,ErrText)
Loop
While Temp <> SQL.NO.DATA.FOUND And Temp <> SQL.ERROR
Msg<-1> = "SQL State = " : SQLState : ", DBMS code = " : DBMSCode
Msg<-1> = ErrText
Repeat
Call DSLogWarn(Msg, "Reporting")
End Case
** Stmt= "UPDATE " :schema:".PS_NW_EPM_JOB_STAT SET JOB_START_DTTM = SYSDATE, NW_ETL_JOB_STATUS = 'P' WHERE ETL_JOB_NAME =" :"'":jobName:"'"
** Stmt = "select * from sysadm.ps_ledger where rownum < 2"
Stmt = "exec sysadm.NW_PS_NW_DBALEA_F_SR('27NOV2007')"
Call DSLogInfo("request SQL: " : Stmt, "NWETLJobStartTom")
status = SQLExecDirect(hStmt, Stmt)
ErrorCode=SQLError(hEnv,hConn,hStmt,SQLstate,DBMSCode,ErrText)
Call DSLogInfo("status of SQL":SQLError(hEnv,hConn,hStmt,SQLstate,DBMSCode,ErrText),SQLstatus)
Begin Case
Case status = SQL.SUCCESS
Call DSLogInfo("Successfully called SQLExecDirect", "Reporting")
Case status = SQL.SUCCESS.WITH.INFO
Msg = "Successfully called SQLExecDirect with information."
Temp = SQLError(hEnv,hConn,hStmt,SQLState,DBMSCode,ErrText)
Msg<-1> = "SQL State = " : SQLState : ", DBMS code = " : DBMSCode
Msg<-1> = ErrText
Call DSLogInfo(Msg, "Reporting")
Case @TRUE
Msg = "Error calling SQLExecDirect."
Temp = SQLError(hEnv,hConn,hStmt,SQLState,DBMSCode,ErrText)
Loop
While Temp <> SQL.NO.DATA.FOUND And Temp <> SQL.ERROR
Msg<-1> = "SQL State = " : SQLState : ", DBMS code = " : DBMSCode
Msg<-1> = ErrText
Repeat
Call DSLogWarn(Msg, "Reporting")
End Case
I read the good postings on SQLExecDirect, they gave me some good things to try, but I'm still not fixing our problem. High level bits - our ODBC against Oracle 10g is working, I can import metadata. When we try execute an Oracle command with SQLExecDirect in a "Before" routine, it fails. The DB connect commands seem to work. Any kind of of Oracle command seems to fail with the same error message (100, I think this is an ODBC.H error message) in SQLExecDirect, selects, exec's, desc's, commands with bad syntax, commands with good syntax that run in Toad. The code in full debug mode is shown below. Some important entries from the log file are shown directly below:
Reply from calling SQLAllocStmt
test..BeforeJob (Reporting): Successfully called SQLExecDirect
Reply from analyzing the Error return code
test..BeforeJob (): status of SQL100
Reply from analyzing the SQLExecDirect Call
est..BeforeJob (Reporting): Error calling SQLExecDirect.
*** the Loop analyzing the return code gave nothing ***
Here is the code in full debug mode. The Oracle command we wanted to run was the update statement:
status = SQLAllocStmt(hConn, hStmt)
Begin Case
Case status = SQL.SUCCESS
Call DSLogInfo("Successfully called SQLExecDirect", "Reporting")
Case status = SQL.SUCCESS.WITH.INFO
Msg = "Successfully called SQLExecDirect with information."
Temp = SQLError(hEnv,hConn,hStmt,SQLState,DBMSCode,ErrText)
Msg<-1> = "SQL State = " : SQLState : ", DBMS code = " : DBMSCode
Msg<-1> = ErrText
Call DSLogInfo(Msg, "Reporting")
Case @TRUE
Msg = "Error calling SQLExecDirect."
Temp = SQLError(hEnv,hConn,hStmt,SQLState,DBMSCode,ErrText)
Loop
While Temp <> SQL.NO.DATA.FOUND And Temp <> SQL.ERROR
Msg<-1> = "SQL State = " : SQLState : ", DBMS code = " : DBMSCode
Msg<-1> = ErrText
Repeat
Call DSLogWarn(Msg, "Reporting")
End Case
** Stmt= "UPDATE " :schema:".PS_NW_EPM_JOB_STAT SET JOB_START_DTTM = SYSDATE, NW_ETL_JOB_STATUS = 'P' WHERE ETL_JOB_NAME =" :"'":jobName:"'"
** Stmt = "select * from sysadm.ps_ledger where rownum < 2"
Stmt = "exec sysadm.NW_PS_NW_DBALEA_F_SR('27NOV2007')"
Call DSLogInfo("request SQL: " : Stmt, "NWETLJobStartTom")
status = SQLExecDirect(hStmt, Stmt)
ErrorCode=SQLError(hEnv,hConn,hStmt,SQLstate,DBMSCode,ErrText)
Call DSLogInfo("status of SQL":SQLError(hEnv,hConn,hStmt,SQLstate,DBMSCode,ErrText),SQLstatus)
Begin Case
Case status = SQL.SUCCESS
Call DSLogInfo("Successfully called SQLExecDirect", "Reporting")
Case status = SQL.SUCCESS.WITH.INFO
Msg = "Successfully called SQLExecDirect with information."
Temp = SQLError(hEnv,hConn,hStmt,SQLState,DBMSCode,ErrText)
Msg<-1> = "SQL State = " : SQLState : ", DBMS code = " : DBMSCode
Msg<-1> = ErrText
Call DSLogInfo(Msg, "Reporting")
Case @TRUE
Msg = "Error calling SQLExecDirect."
Temp = SQLError(hEnv,hConn,hStmt,SQLState,DBMSCode,ErrText)
Loop
While Temp <> SQL.NO.DATA.FOUND And Temp <> SQL.ERROR
Msg<-1> = "SQL State = " : SQLState : ", DBMS code = " : DBMSCode
Msg<-1> = ErrText
Repeat
Call DSLogWarn(Msg, "Reporting")
End Case