Routine call

Calls a stored procedure or function on a database and emits its result: the return value, output parameters, or the rows of a table-valued / pipelined result. Input and output types are generated from the routine signature. Works inside a Sql transaction scope.

Pins

Pin	Kind	Types	Description
Input	input	generated <flow namespace>.<TypePrefix>_<schema>_<routine>_Input	One property per input (and in/out) parameter.
Output	output	generated <TypePrefix>_<schema>_<routine>_Return or the routine's user-defined return type	Return value and output parameters, or one record per returned row.
Continue	continue output	same as input	Called when the call produced nothing for Output (see Behaviour).

Configuration

Field	Type	Default	Description
ConnectionString	database link name		Required. Name of a repository database link (link selector). Looked up by exact name; a raw connection string or #name prefix is not resolved.
TypePrefix	string	Db	Prefix of the generated type names (an _ is appended if missing).
ProcedureName	string (database explorer)		Required. Routine name, name, schema.name or schema.package.name (Oracle). Without a schema the link's default schema is used. Triggers reconfigure.
CommandTimeOut	integer (seconds)	20	Command timeout.
KeepOriginalNames	boolean	false	true keeps parameter / field names; false converts them to C# names.
BatchMode	dropdown: 1 Auto, 2 Always, 3 Never	1	Whether the rows of one call (result set or returned collection) form a batch for batch consumers (Write file, Bulk insert, Build array, ...). Auto: only if the input record is not already in a batch (Timer, Http listener); a call per record of a batch keeps that batch. Always: a child batch per call. Never: no batch.

The hidden field OutputTypeName is filled by reconfigure with the full name of the return type, and cleared when the routine has none.

Behaviour

• Reconfigure (changing ProcedureName) reads the routine signature fresh from the database (never cached, so re-selecting the routine picks up changes), removes the types the component generated before and generates the types below. <schema>_<routine> is the qualified routine name with dots replaced by _ (Oracle: <schema>_<package>_<routine>), converted to a C# name unless KeepOriginalNames. A database without stored routines shows an error: use Sql query there.
  • ..._Input with one property per input and output parameter (database name kept as SQL name), set as the only type of Input;
  • for a scalar return value ..._Return with property ReturnValue plus one property per output parameter; for a user-defined (object/record) return type that type itself; for procedures with output parameters only a _Return type with those parameters;
  • <TypePrefix>_<type schema>_<type> for each user-defined (object / record) parameter type, nested types included. The return type is set on Output. A component with its default name (or a CAL_ name) is renamed to CAL_<ProcedureName>.
• Changing TypePrefix or KeepOriginalNames does not regenerate the types: re-select the routine.
• Per input record the routine is called once; parameter values are read from the input properties by their SQL name (null is sent as DBNull). A user-defined (object) parameter is sent as its record, field by field by SQL name, nested records included; a null record is sent as null.
• Results:
  • scalar return value: one Output record with ReturnValue and output parameters;
  • object return: one Output record mapped from the returned structure;
  • collection or table/pipelined result: one Output record per element/row; the rows form a batch (BatchMode) that ends after the last row was processed downstream and before Continue, an error fails it;
  • rows were streamed and output parameters have values: an additional Output record with the output parameters.
• Continue is only called in two cases: the routine has no generated return type (OutputTypeName empty) and returned nothing - Continue gets a NoInputData record; or rows were streamed and no output parameter has a value - Continue gets the input record. It is not a general "finished" signal.

Notes

• The input record must have a property for every routine parameter (matched by SQL name); use the generated _Input type, for example built with a Data mapper. A missing property fails the call.
• When a routine returns no rows and has a return type, the result is handled as a return value: an Output record (possibly with empty values) is emitted instead of calling Continue. Procedures that only have output parameters go through this path too; test them before relying on the result shape.

Example

{ "ConnectionString": "MainDb", "ProcedureName": "dbo.GetCustomerBalance", "TypePrefix": "Db" }

Http listener → Data mapper (to Db_..._Input) → Routine call → Http response