Skip to main content

Stored Procedures transfer object

The transfer object “Stored Procedures” calls a stored procedure using a relational database plug-in. Input parameters, return parameters, and, if applicable, result sets can be used as destinations or sources within the flow.

Dialog Overview​

Dialog for the Stored Procedures transfer object

General Properties​

PropertyDescription
DB ConnectionAll relational database connections created in the Plug-ins section are available for selection here. If your desired database connection is not listed, you can configure it in the Plug-ins section as Microsoft SQL Server, Oracle, SAP HANA, ODBC, OLE DB, MySQL, or PostgreSQL.
ProcedureName of the stored procedure. Depending on the database system, a schema specification may also be required.
Available ParametersInput and return parameters of the stored procedure that are not yet in use.
Used ParametersParameters that are displayed in the transfer object and used within the flow.

The distinction between available and used parameters serves to provide clarity in the configuration area. Move parameters by double-clicking the parameter name or using the <, >, <<, and >>.

Special Features in OPC Router​

The transfer object for stored procedures has several special features in OPC Router:

  • The procedure can provide input parameters, return parameters, and, depending on the database system, result sets as well.
  • The OPC Router reads the available parameters and makes them available in the transfer object as usable elements.
  • When the “Retrieve Result Set” command is executed in the OPC Router, the procedure is called once to determine the structure of the result set.
  • This call takes place within a transaction that is subsequently rolled back.
  • To determine the structure, the OPC Router first calls the procedure with NULL values. If a parameter does not allow NULL values, the OPC Router uses default values for the respective data type instead—for example, 0 for numeric parameters or an empty string for strings.
  • The procedure must therefore be transaction-safe and must not return any errors when called for structure detection.

Notes​

The general properties of the transfer object apply to relational databases in the OPC Router. Database-specific details—such as syntax, result sets, or SQL dialects—are documented on the respective plug-in pages.

note

Not every relational database plug-in or driver used supports stored procedures in the same way.