Hitachi Vantara Pentaho Community Wiki
Child pages
  • Call DB Procedure
Skip to end of metadata
Go to start of metadata

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;

  • No labels