JDBC stored procedure parameters

You can use Orchestration to run a stored procedure on MySQL, Oracle DB, and MS-SQL databases.

There is support of multiple data types, with the following limitations:
  • Only one result-set is returned.
  • The order of input and output data types in the stored procedure parameters must match the activity definition.
  • Binary, Blob, Varbinary, and LongBinary should be base64 encoded.
  • Date, Time, and Timestamp have a specific format:
    Date format: yyyy-mm-dd
    yyyy-mm-dd is the supported format.
    Time format: hh:mm:ss[.sss]
    hh:mm:ss[.sss] is the supported format. Precision is in milliseconds only, microseconds or nanoseconds cannot be handled.
    Note: 10:30:59 and 10:30:59.999000 are correct but 10:30:59. is incorrect.
    Timestamp format: yyyy-mm-dd hh:mm:ss[.ffffff]
    yyyy-mm-dd hh:mm:ss[.ffffff] is the supported format. Precision is to microseconds.
  • ResultSet is the first result set coming back from database server.
  • MS-SQL does not support INOUT parameters. If you use INOUT parameters, the Activity Template transparently maps them to OUT parameters.
Note: If you do not want to do a validation of data types in stored procedure parameters, like the legacy behavior, you set the glide.stored_proc.data_type.validation as false. See MID Server properties for instructions.
Table 1. Stored procedure parameters
Field Description
Mode Type of stored procedure parameter. Supported parameters:
  • IN
  • OUT
  • INOUT
Sql Type A SQL data type. Supported data types:
  • ARRAY
  • BIGINT
  • BINARY
  • BIT
  • BLOB
  • CHAR
  • CLOB
  • DATE
  • DECIMAL
  • DISTINCT
  • DOUBLE
  • FLOAT
  • INT
  • INTEGER
  • JAVA_OBJECT
  • LONGVARBINARY
  • LONGVARCHAR
  • NULL
  • NUMERIC
  • OTHER
  • REAL
  • REF
  • REF_CURSOR
  • SMALLINT
  • STRUCT
  • TIME
  • TIMESTAMP
  • TINYINT
  • VARBINARY
  • VARCHAR
Name/Value Name-value pairs to pass to the host. You can create these parameters manually, or drag and drop input variables into the parameter fields and assign a value.
Note: Not all database providers support the entire realm of JDBC data types. Reference your database reference manual to understand the supported datatypes for your installation.