Stored Procedure makes operations through a database connection and returns procedure data as a single JSON object.

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

Take a look at the configuration parameters of the component:

  • Account: account to be used by the component.

  • Database URL: connection string to the database.

  • SQL Statement: SQL statement to be executed.

  • Fail On Error: if the option is activated, the pipeline with error execution will be suspended; otherwise, the pipeline execution continues, but the result will show a false value for the success property.

  • 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.

  • Advanced: advanced configurations.

  • 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.

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}

IMPORTANT: it's still 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}

INOUT

  • :?{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)

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

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
}

Did this answer your question?