Call DB Procedure

Description

The Call DB Procedure step allows you to execute a database procedure (or function) and get the result(s) back. With MySQL and JDBC, it is not possible to retrieve the result set of a stored procedure. Stored procedures and functions can only return values through their function arguments and those arguments must be defined in the Parameters section of the DB Procedure Call configuration.

Options

The following table describes the available options for the Call DB Procedure step:

Option	Description
Step name	Name of the step; this name has to be unique in a single transformation
Connection	Name of the database connection on which the procedure resides
Proc-name	Name of the procedure or function to call
Find it	Click to search on the specified database connection for available procedures and functions (Oracle and SQL Server only)
Enable auto	In some instances you want to perform updates
commit	in the database using the specified procedure. In that case you can either have the changes performed using auto-commit or not. If auto-commit is disabled, a single commit is being performed after the last row is received by this step.
Result name	Name of the result of the function call; leave blank if this is a procedure
Result type	Type of the result of the function call; not used in case of a procedure.
Parameters	List of parameters that the procedure or function needs Field name: Name of the field. Direction: Can be either IN (input only), OUT (output only), INOUT (value is changed on the database). Type: Used for output parameters so that Kettle knows what returns
Get Fields	Fills in all the fields in the input streams to make your life easier; delete the lines you don't need and re-order the remaining lines

FAQ

The Call DB Procedure step doesn't do anything (< version 3.1)

Q: The Call DB Procedure step doesn't do anything, my transformation finishes without doing anything and without issuing errors. How do I make it work?

A:The Call DB Procedure needs to be triggered. Use a Row generator step generating e.g. 1 empty row and link that with a hop to the Call DB Procedure step.

Which permissions are required for MySQL 5.X?

Before PDI executes the stored procedure named in the Proc-name field, it first submits a SHOW CREATE PROCEDURE. This will fail, and cause PDI to report a Null Pointer Exception, unless permission has been granted. Of course, the user must also have permission to execute the procedure. Example statements for granting the needed permissions are:

grant select on mysql.proc to U;

grant execute on procedure P to U;