////
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements.  See the NOTICE file
distributed with this work for additional information
regarding copyright ownership.  The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License.  You may obtain a copy of the License at
  http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied.  See the License for the
specific language governing permissions and limitations
under the License.
////
:documentationPath: /pipeline/transforms/
:language: en_US
:description: The Call DB Procedure transform allows you to execute a database procedure (or function) and get the result(s) back.

= image:transforms/icons/dbproc.svg[Call DB procedure transform Icon, role="image-doc-icon"] Call DB procedure

[%noheader,cols="3a,1a", role="table-no-borders" ]
|===
|
== Description

The Call DB Procedure transform 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.

|
== Supported Engines
[%noheader,cols="2,1a",frame=none, role="table-supported-engines"]
!===
!Hop Engine! image:check_mark.svg[Supported, 24]
!Spark! image:check_mark.svg[Supported, 24]
!Flink! image:check_mark.svg[Supported, 24]
!Dataflow! image:check_mark.svg[Supported, 24]
!===
|===

== Options

[options="header"]
|===
|Option|Description
|Transform name|Name of the transform; this name has to be unique in a single pipeline
|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 transform.
|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 a|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 Hop 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
|===