sql statement execution from Routine.

Aquilis · Post by **Aquilis** » Fri Aug 17, 2007 2:47 am

Hi all,
acording to business logic ,if its a full refresh load then i need to truncate the Table Before loading.Otherwise i need to upsert the data into the table.So for truncating the table we wrote a routine. which is as given below. Before it was working fine, but now its giving execution failure.
all connections to DB like DSN,user,Passwd,happens good,but statement execution fails.
so plz let me know where i went wrong.

$INCLUDE DSINCLUDE JOBCONTROL.H
$INCLUDE UNIVERSE.INCLUDE ODBC.H
* Database Environment Settings

henv = SQL.NULL.HENV
hConn = SQL.NULL.HDBC
hStmt = SQL.NULL.HSTMT

** Connect to DataSource

status = SQLAllocEnv(hEnv)
status = SQLAllocConnect(hEnv, hConn)
status = SQLConnect(hConn, DbName, DbUName ,DbPassword )

If status = SQL.ERROR
Then
Call DSLogInfo("Connection KO " , "ExecSql")
ErrorCode = -1
Goto Finally
End Else
Call DSLogInfo("Connection OK " , "ExecSql")
ErrorCode = 1
End

** Execution Statement

status = SQLAllocStmt(hConn, hStmt)
Call DSLogInfo("request SQL:":Stmt,"ExecSql")
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)

If status<>SQL.SUCCESS AND status<>SQL.SUCCESS.WITH.INFO
Then
Call DSLogInfo("Request KO ", "ExecSql")
ErrorCode = -1
Goto Finally
End Else
Call DSLogInfo("Request OK ", "ExecSql")
ErrorCode = 1
End

** Free Allocated Resources
Ans = ErrorCode

Finally:
status = SQLFreeStmt(hStmt, SQL.CLOSE)
status = SQLFreeStmt(hStmt, SQL.DROP)
status = SQLDisconnect(hConn)
status = SQLFreeConnect(hConn)
status = SQLFreeEnv(hEnv)
Ans = ErrorCode

ray.wurlod · Post by **ray.wurlod** » Fri Aug 17, 2007 2:56 am

What's the error message?
Where do you initialize the variable containing the SQL statement (Stmt)?
You really need to strip ALL error information from the handle - loop executing SQLError() until it returns SQL.NO.DATA.FOUND.

Aquilis · Post by **Aquilis** » Fri Aug 17, 2007 3:25 am

actually we are executing 3 statements truncate,delete and update.
As a runtime 'Stmt' will have the sql statements(It might be Truncate or update or delete).This Routine is invoked in the job control code,which processes the different jobs in sequence.

1. If its job1 fails .. then delete whole data from table.

2. after job1 success... job2 is invoked ,so which is a truncate and load.

ray.wurlod · Post by **ray.wurlod** » Fri Aug 17, 2007 6:02 am

So, what's the error message?

Aquilis · Post by **Aquilis** » Fri Aug 17, 2007 6:30 am

Hi Ray,

Error message happens to be:

"Variable "CallerName" previously undefined. Empty string used."

ray.wurlod · Post by **ray.wurlod** » Fri Aug 17, 2007 2:59 pm

Was this the "Connection KO" message, the "request SQL" message or the "status of SQL" message?

Aquilis · Post by **Aquilis** » Sat Aug 18, 2007 12:57 am

As its connection seems successful..."Connection OK".

Its SQL status message.

But statement execution failure :
i .e "Request KO"..
DSLogInfo("Request KO ", "ExecSql")

ray.wurlod · Post by **ray.wurlod** » Sat Aug 18, 2007 2:33 am

Good so far. What precisely was the SQL statement - that is, the value of the Stmt variable - when this error occurred? And to what kind of data base is the connection being made (e.g. UniVerse, Oracle, Informix...)?

JoshGeorge, the expired license will give a different error message.

Aquilis · Post by **Aquilis** » Sun Aug 19, 2007 8:05 am

Hi ray:

when the routine was compiled and tested,that time it will throw error. And i am using oracle DB.

Hi Josh:

I think if it would have been driver license related error,this will throw license error as a part of ERRTEXT.

correct me if i am wrong.

ray.wurlod · Post by **ray.wurlod** » Sun Aug 19, 2007 3:06 pm

You still have not reported what the value of the Stmt variable was. Also, what was logged as the "status of SQL" message? Note that you need to call SQLError() to remove information from the handle even if the status is SQL.SUCCESS.WITH.INFO - that is, you still have a "dirty handle" even if it's only informational message attached to it. That is, you need to change the AND status <> SQL.SUCCESS.WITH.INFO logic.

Code: Select all

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

Aquilis · Post by **Aquilis** » Thu Aug 30, 2007 5:10 am

DB Used: Oracle
Status Sql: SQL100

Error message when tested:

Arg1 = XYZ
Arg2 = DFLD
Arg3 = abc
Arg4 = select count(*) from DFLD.DFREJECT;

Test completed.

DSLogInfo called from : ExecSql
Message to be logged is...
> Connection OK
DSLogInfo called from : ExecSql
Message to be logged is...
> request SQL:select count(*) from DFLD.DFREJECT;
Program "DSLogInfo": Line 46, Variable "CallerName" previously undefined. Empty string used.
DSLogInfo called from :
Message to be logged is...
> status of SQL100
DSLogInfo called from : ExecSql
Message to be logged is...
> Request KO

Result = -1