Stored Procedure

Know the component and how to use it.
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 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.
Custom Connection Properties: user defined and database specific Connection Properties.
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)
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
}
Session Management
Stream DB V3
Last modified 2mo ago