Stored Procedure

Discover more about the Stored Procedure component and how to use it on the Digibee Integration Platform.

Stored Procedure makes operations through a database connection and returns procedure data as a single JSON object. To see all the databases supported by this component, read the Supported databases documentation.

Important: be careful with the memory consumption for large datasets. BLOB and CLOB aren't supported yet.

Parameters

Take a look at the configuration parameters of the component. Parameters supported by Double Braces expressions are marked with (DB).

Parameter	Description	Default value	Data type
Account	Account to be used by the component. Supported accounts: Basic.	N/A	String
Database URL	Connection string to the database.	jdbc:mysql://ec2-54-233-172-214.sa-east-1.compute.amazonaws.com/digibee	String
SQL Statement	SQL statement to be executed.	call proc_test(:?{in;var1},:?{in;var2},:?{out;result;VARCHAR},:?{out;now;VARCHAR})	String
Fail On Error	If the option is enabled, the execution of the pipeline with error will be interrupted; otherwise, the pipeline execution proceeds, but the result will show a false value for the “success” property.	False	Boolean
Custom Connection Properties	User-defined and database-specific Connection Properties.	N/A	String
Keep Connections	If activated, the option will keep the connections to the database for 30 minutes maximum; otherwise, it will be kept for 5 minutes only.	True	Boolean
Advanced	Advanced configurations.	False	Boolean
Connection Test Query	SQL statement to be used before each connection is established. This is an optional parameter and must be applied to databases that don't have reliable information about the connection status.	N/A	String

Messages flow

Input

{
    "parameters": {
        "variable1": "value"
    }
}

• :?{in;variable1} .

• in: parameter type (mandatory).

• variable1: name of the variable that came with the JSON input in the request body (mandatory).

Example

Let's say you want to make a call to a procedure with an input parameter (Oracle, MySql, etc.):

• call proc (:?{in; variable})

• SQL Server:

• exec proc :?{in; variable}

Currently, it's not possible to set the IN type (VARCHAR, FLOAT, INTEGER, etc.).

Output

• :?{out;propertyToOutput;Type;java_type_library} .

• out: parameter type (mandatory).

• propertyToOutput: name of the property the component will show in the result (mandatory).

• Type: CURSOR, VARCHAR, NUMERIC, FLOAT, etc. (mandatory).

• java_type_library: if you need to use a procedure in which OUT is an Oracle CURSOR, it will be necessary to specify the following Library: oracle.jdbc.OracleTypes (optional).

Example

Let's say you want to make a call to a procedure with an input and output parameter (Oracle, MySql, etc.):

• call proc (:?{in; variable}, :?{out; nameResultOut;VARCHAR})

SQL Server

• exec proc :?{in; variable}, :?{out; nameResultOut;VARCHAR}

In Out

• :?{in; variable|out;propertyToOutput;Type;java_type_library} .

• in: parameter type (mandatory).

• variable1: name of the variable that came with the JSON input in the request body (mandatory).

• out: parameter type (mandatory).

• propertyToOutput: name of the property the component will show in the result (mandatory).

• Type: CURSOR, VARCHAR, NUMERIC, FLOAT, etc. (mandatory).

• java_type_library: if you need to use a procedure in which OUT is an Oracle CURSOR, it will be necessary to specify the following Library: oracle.jdbc.OracleTypes (optional).

Example

Let's say you want to make a call to a procedure with an input and output parameter (Oracle, MySql, etc.):

• call proc (:?{in; variable|out; nameResultOut;VARCHAR})

SQL Server

• exec proc :?{in; variable|out; nameResultOut;VARCHAR}

Input

The component waits for a message in the following format:

{
    "parameters": {
        "name": "value"
        ...
    }
}

Output

{
	"out": {
		"rs-1": [
			{
				"column1": "admin",
				"column2": 1
			},
			{
				"column1": "jose",
				"column2": 2
			}
		]
	},
	"success": true
}

Output with error

{
	"success": false,
	"error": error_message,
	"message": cause_of_the_error
}