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.
The following table describes the available options for the Call DB Procedure step:
|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
|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|
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.
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;