Carrying value from B/Job routine to Job transform stage

[yserrano]

Hello all, I need your help again
This time with a Before-Job Subroutine.
I customized some code found in this forum to access an Oracle table from my routine, this is an excerpt of the code:

Code: Select all

Stmt= "select max(":field:") from ":table: " ;"
Call DSLogInfo("Query SQL: " : Stmt, "ExecSQLProc") 
ErrCode1 = SQLBindCol(hStmt, 1, SQL.B.DEFAULT, res1)
status = SQLExecDirect(hStmt, Stmt) 

This is working nice, and what I need now is to carry on the value assigned to res1 to a job tranformer stage. Is it possible? How? Are there different methods to acomplish this?

I intensely searched the forum for some solution before I posted this but only found workarounds for similar situations involving job sequences, which is not my case.
I tried with environment variables without success.


P.D. Want to thank you for the sample code regarding SQLExecDirect and ODBC usage, good job!

[kcbland]

How about not using the before-job routine and using the initialize stage variable functionality? When the Transformer starts, the stage variables initialize themselves. This can include running a process such as a Function, which can in turn do exactly the same steps as the before routines. The best part is that they are Functions, which means that cool Test button works!

[chulett]

In other words, you can't do what you want from 'before job'.

[yserrano]

When the Transformer starts, the stage variables initialize themselves. This can include running a process such as a Function...

With this approach the function will be called once for every row processed by the job, right? Impacting job performance directly.

[chulett]

No - Ken is suggesting the work be done in the Initial Value section of the stage variable's definition, which means it will only happen once per run.

[kcbland]

Try it - you'll like it. Initialize stage variables is amazingly powerful functionality.

[chulett]

Exactly, it's very cool when it all clicks for you. You then leave the derivation blank in the Transformer so that the value stays static over the course of the run.

[yserrano]

Done. Thank you!
Excelent help as usual.