DSXchange

Posted: **Tue Feb 26, 2008 1:00 pm**

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!

Posted: **Tue Feb 26, 2008 1:05 pm**

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!

Posted: **Tue Feb 26, 2008 1:43 pm**

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

Posted: **Tue Feb 26, 2008 1:47 pm**

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

Posted: **Tue Feb 26, 2008 1:50 pm**

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.

Posted: **Tue Feb 26, 2008 1:54 pm**

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

Posted: **Tue Feb 26, 2008 2:16 pm**

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.

Posted: **Tue Feb 26, 2008 2:30 pm**

Done. Thank you!
Excelent help as usual.

DSXchange

Carrying value from B/Job routine to Job transform stage

Carrying value from B/Job routine to Job transform stage