HOW TO FETCH THE QUERY RESULT

[angelovaira]

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)

[chulett]

Ugh. Why do all that when a simple job with an OCI or ODBC stage will easily do the same thing?

[angelovaira]

Ugh. Why do all that when a simple job with an OCI or ODBC stage will easily do the same thing? ...

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.

[kumar_s]

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.

[kumar_s]

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.

[angelovaira]

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 ...

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.

[chulett]

Perhaps this (rather long) thread might have the info you are looking for?

viewtopic.php?t=115619

[angelovaira]

Perhaps this (rather long) thread might have the info you are looking for?

viewtopic.php?t=115619

OK, thanks a lot, I will try...