DSXchange

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

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.

DSguru2B wrote: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.

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

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)

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

ray.wurlod wrote:... 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.

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.