Question Store query output in variable/parameter

Hi Sir,

I have few question
1: I want to load tables from sql server using windows authentication/ AD authentication.

2: Is it possible to store field value of column in variable.
I running one sql operation which has query like

select City,Region,State from Country where flag=1;

It will return only 1 row.
I want to store Value of City ,Region and State in variable varCity, varRegion and varState respectively.
Reason behind doing this is I want to use those variable in different actions at different level.

If yes then Please guide me to achieve it.

Let me know if any query.

Thanks and Regards,
Prashant Sangle

Hi,

1) Can you please open another topic if this question is not linked to this topic ?

2) You have several solutions

a. SQLToParameters
You can use the SQLToParameter action that is designed for that.
It creates process parameters from SQL requests, but your request must return only one column/one row
So you'll need to do multiple requests.

Example:

For more information, please refer to the documentation of the action, that can be found in the designer or here:
stambia.org/di-stbar

b. SQL Operation and Scripting Action
You can bind the result of the SQL Operation to use the values, like explained in this article:
stambia.org/doc/104-process/actions/sql/...-and-sql-action-type

To publish variables from the bind result, you can use a scripting action with the following context function:
__ctx__.publishVariable("variablePath","variableValue");

Example:

The advantage of this solution is that you execute the query only once

Does this help ?

Hi,

thanks for response, both solution working as per requirement. 2nd solution is more better for me.
Just one small query if i want use those variable again in another action then How can use it.

because if use syntax %{variable_name}% in another process.
I am getting error
"Program Information" has encountered a problem.
Error during Compilation (compilation.compilationD) Watch console for more details
javax.xml.transform.TransformerConfigurationException: Failed to compile stylesheet. 1 error detected.

Console log suggest :
Fatal Error! XPath syntax error at char 6 on line 6 in {$State/tech:columnName}:
[xslt] Variable $State has not been declared

Please guide me to resolve it.

Thanks and Regards,
Prashant Sangle

Hi,

The %{variable}% syntax is for Metadata Variables and can only be used for it.
In your case it is Session Variables so the syntax to use is ${path_to_session_variable}$
You have an overview of all the syntaxes in this article if needed:
stambia.org/doc/85-variables-and-paramet...f-different-syntaxes