Retrieving single value from Stored Procedure

1

Hi,

I have a stored procedure in a MySQL database that has an OUT parameter.
The procedure is the first step in a custom process I have created, and returns a batch id that will be used in nearly every subsequent step. Is there a way to receive this value from the out parameter of the procedure into a parameter of the project?

Thanks,

Steve

2

Hi Steve,

You can't assign any value from a database to a parameter in the current project, but you can create a separate project which will:

  1. Load value from the out parameter with Import from DB transformation;
  2. Use Iterate transformation to run your initial project with some parameter assigned to the loaded value.

In the first step you should use Import from DB transformation in Custom SQL mode with an SQL statement similar to the following one:

CALL testproc(@a);
SELECT @a;

This statement with not work with default settings neither with built-in MySQL connector nor with ODBC MySQL connector.

If you are using EasyMorph's build-in MySQL connector, please go to Custom properties tab in connector's configuration and add AllowUserVariables property with value: true.

If you are using MySQL ODBC driver, please go to DSN properties and turn on "Allow multiple statements" setting.

Here is an example project: LoadParameterFromMySQL.zip (4.2 KB)

It has a MySQL connector with empty settings so it will not work "from the box". In order to make it to work please actual credentials to MySQL connector or replace it with your actual connector.