Stored Procedure call to MySQL DB 5.7

[Former Member]

Hallo

I tried several ways to get a single value back of a MII-SQL Query.

Query types I tried:

static, withOutput and command.

This works in MySQL Workbench:

CALL storedProcedure1(@val);

SELECT @val;

Stored procedure definition on MySQL Server:

CREATE DEFINER=`me`@`localhost` PROCEDURE `storedProcedure1`(

OUT js_txt_file LONGTEXT

)

BEGIN

SET js_txt_file = "bla";

END

All solutions for MS-SQL and Oracle seem not to work.

Thank you for your support!

[Former Member · Accepted solution]

Solution was:

Don't put both statements into on SQL Query.

But make two SQL query boxes that contain exactly one statement (One for the call and one with the "select @val")

Then copy the "Result" Output of the second to your prefered target.

[Former Member]

I am assuming that your query is complex so you wanted to use a stored procedure instead of a select statement directly within an MII SQLQuery. Maybe the following will help you with this issue.

Create an MII SQLQuery which uses a "Fixed query" that executes your stored procedure. "exec storedProcedure1" (I am not sure what your @val) is supposed to do, it is not needed when calling a stored procedure from within an MII SQLQuery.)

Here is an example select statement in the stored procedure:

  select distinct value from [database name].[dbo].[table name].[column name] where [select from column name] = 'VALUE_TO_SELECT'

The MII SQLQuery "Fixed Query Details" would look like this:

     exec storedProcedure1

In your MII Transaction: Drag your new MII SqlQuery into an empty Sequence block. The next Sequence block should contain an action block where you  will assign the results of the preceding block, thus storing the results from the executed stored procedure in either a local or transaction variable.

When setting up the Transaction Variable make sure you select that it is an "Output Parameter"  data type "String" or whatever you are returning.

In the Assignment block directly following your SQLQuery block use LinkType=Assign Value and the assignment will look similar to the below.

     Transaction.WSServer

     SQL_Select_server_name.Results{/Rowsets/Rowset/Row/value}

Thanks, Rene