How to call a stored procedure in a routine

[kosmos1981]

Hi,


I don't know Datastage Basic for develop my own routine. I need help about us.
I would like to call a stored procedure (SQL Server) in my own routine. How ?

Thx

[DSguru2B]

If your not well versed in writing routines then I would suggest a workaround. Call the stord proc. in a before/after sql tab of DRS stage or from STP stage. You dont need to write a custom routine to do this.

[kosmos1981]

If your not well versed in writing routines then I would suggest a workaround. Call the stord proc. in a before/after sql tab of DRS stage or from STP stage. You dont need to write a custom routine to do this.

I need a custom routine because i would like to call it in each job Master (before/after job) for log. All log will be in table SQL and alimentate by my stored procedure.

[DSguru2B]

Read ray.wurlord's first reply here.He mentiones about Basic API's to connect to database using odbc connection.

[kosmos1981]

thx you. I found response in searching on the Internet and it work :

***************************************************
** FUNCTION ExecSQLProc(dataSource,userName,password)
==> RETURN (Ans)
***************************************************

$INCLUDE UNIVERSE.INCLUDE ODBC.H

** Initialise parameters ODBC

Ans = -1

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, dataSource, userName , password )

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


** Execution request (here, my stored procedure )

status = SQLAllocStmt(hConn, hStmt)

Stmt= "exec MyStoredProc ;"

Call DSLogInfo("request SQL: " : Stmt, "ExecSQLProc")

status = SQLExecDirect(hStmt, Stmt)

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


** If my stored proc return a result , i put it in 'res' (=> Ans)

status = SQLBindCol(hStmt, 1, SQL.B.DEFAULT, res)
status = SQLFetch(hStmt)

If status<>SQL.SUCCESS AND status<>SQL.SUCCESS.WITH.INFO
Then
Call DSLogInfo("Result KO", "ExecSQLProc")
Ans = -1
Goto Finally
End Else
Call DSLogInfo("Result OK ", "ExecSQLProc")
Call DSLogInfo("The result is " : res, "ExecSQLProc")
Ans = 0
End



** Free allocated ressources

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

[ray.wurlod]

... except that the error handling is abysmal, so you had better hope that you never get any errors.

[kosmos1981]

... except that the error handling is abysmal, so you had better hope that you never get any errors. ...

Exactly ! if I have errors, I see it only in Datastage logs. Perhaps you can interpreted the routine's answers ....

But I wanted just to show how to create a connexion SQL in Datastage Basic.

[Nisusmage]

Heheh, Easy Tiger ..

You code was very useful to me, I based my routine on that original code above.

No need to be touchy about your code.