I made the following transform function in datastage that execute an oracle query. It works, but I don't know how to catch the result of the query. In this case it should be the system date.
Please can you help me ?
Thanks to everybody...
RoutineName = 'ExecOracleStatement'
henv = SQL.NULL.HENV
hdbc = SQL.NULL.HDBC
hstmt = SQL.NULL.HSTMT
Connected = @FALSE
Res = SQLAllocEnv(henv)
if Res <> SQL.SUCCESS then
Message = "Error in henv":status
Call DSLogWarn(Message,RoutineName)
Ans = 1
End Else
Res = SQLAllocConnect(henv,hdbc)
if Res <> SQL.SUCCESS then
Message = "Error in hdbc":status
Call DSLogWarn(Message,RoutineName)
Ans = 2
End Else
status = SQLConnect(hdbc, TDbName , TUserId , TPsw )
if status <> SQL.SUCCESS then
Message = "SQLConnect failed ":status
Call DSLogWarn(Message,RoutineName)
Ans = 3
End Else
status = SQLSetConnectOption(hdbc,SQL.TX.PRIVATE,SQL.TX.PRIVATE.ON)
status = SQLSetConnectOption(hdbc,SQL.AUTOCOMMIT,SQL.AUTOCOMMIT.ON)
status = SQLSetConnectOption(hdbc,1041,"ivvm.lic")
status = SQLSetConnectOption(hdbc,1042,"DATASTAGEQQQQQQQQQQQQQQQ")
SQLStat = "SELECT sysdate FROM DUAL"[/b]
Connected = @TRUE
Res = SQLAllocStmt(hdbc,hstmt)
Res = SQLExecDirect(hstmt,SQLStat)
if Res <> SQL.SUCCESS then
Message = "Error executing SQLStatement ":SQLStat:" (Status = ":status:")"
Call DSLogWarn(Message,RoutineName)
Ans = 4
End
Else
Ans = Res
End
End
End
End
status = SQLFreeStmt(hstmt,SQL.DROP)
status = SQLDisconnect(hdbc)
status = SQLFreeConnect(hdbc)
status = SQLFreeEnv(henv)
HOW TO FETCH THE QUERY RESULT
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 16
- Joined: Fri Mar 28, 2003 4:36 am
- Location: Italy - Roma
-
- Participant
- Posts: 16
- Joined: Fri Mar 28, 2003 4:36 am
- Location: Italy - Roma
Because the simple select that you see is just an example, but I have to do a more difficult thing. I have an oracle package that does a lot of calculation depending on the parameters that I pass to it. These parameters are coming from an input table with thousand of rows. For each row I have to call the transform function, pass to it the parameters, get the result and write it to another table.chulett wrote:Ugh. Why do all that when a simple job with an OCI or ODBC stage will easily do the same thing? ...
Its again one and the same. To write a procedure where you do all the necessary calls and loads. Extract the loaded temp table to datastage via OCI stage and pass it through the transformer and load it to another table for further operations.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Its again one and the same. To write a procedure where you do all the necessary calls and loads. Extract the loaded temp table to datastage via OCI stage and pass it through the transformer and load it to another table for further operations.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 16
- Joined: Fri Mar 28, 2003 4:36 am
- Location: Italy - Roma
Please, if you know how, can you tell me how can I get the result of the query and return it from the function that I wrote ? I think I need the to use the SQLFetch() function but I don't know how to use it. Thanks.kumar_s wrote:Its again one and the same. To write a procedure where you do all the necessary calls and loads. Extract the loaded temp table to datastage via OCI stage and pass it through the transformer and load i ...
-
- Participant
- Posts: 16
- Joined: Fri Mar 28, 2003 4:36 am
- Location: Italy - Roma
OK, thanks a lot, I will try...chulett wrote:Perhaps this (rather long) thread might have the info you are looking for?
viewtopic.php?t=115619