Search code examples
sql-serverstored-procedurestalend

How to use output of tMSSqlSP on a tJava element

I have a tMSSqlSP element calling a procedure that receives two params and returns two values. If I connect a tLogRow right after that, I can see that the procedure is being called correctly and returning the correct values. However, I need to use those values in a tJava element, which means calling them explicitly somehow.

The schema for the tMSSqlSP looks like:

Column     Db Column
entityTIN   EntityTIN  (IN)
qtdDoc      QtdDoc     (IN)
nextMsg     NextMsg    (OUT)
nextDoc     NextDoc    (OUT)

The only global variable tMSSqlSP has is an error message, and I tried checking the "Is function" box and setting "Return result in" to nextMsg, but trying to use this variable on tJava results in compilation errors.

Any idea how I would go about accessing these values explicitly?

EDIT: Here's a screenshot of this section of the job. The tLogRow connected to the tMSSqlSPprints correctly the values of the two input variables and the two output variables, but the two deactivated components on the right are only able to see null values when connected.

enter image description here

Solution

  • You can link your tMSSqlSP component to a tSetGlobalVar, and add 2 global variables to it (NextMsg and NextDoc) that take their values from the column of your input flow (rowX.column).

    You can then reference them in the next subjob by referencing them in a tJava like so : (String)globalMap.get("NextMsg")

    If you need to process them in the same subjob as tMSSqlSP, you can instead connect tMSSqlSP to a tJavaFlex or a tJavaRow (as tJava doesn't handle input main flows) and just use something like row18.NextMsg.