Sql query

Runs a parameterized SQL statement against a database link and emits the result rows as typed records (or as one dynamic result set). Parameter values are taken from the input record, the message metadata or the flow configuration. Works inside a Sql transaction scope. Use it for custom selects, joins and DML statements.

Pins

Pin	Kind	Types	Description
Input	input	any	Triggers the query; supplies parameter values. A Viaducta.Database.Types.Shaping.DbShapeRequest fills the shaping placeholders.
Output	output	generated <flow namespace>.<TypePrefix><OutputTypeName> or Viaducta.Plugins.Database.Types.DynamicResultset	One record per row, or one DynamicResultset with all rows when DynamicOutput is true. For a statement without result columns, one record with the output parameters (if it has any).
NoData	no-data output	same as input	Called with the input record when no row was read, or the statement affected 0 rows.
Continue	continue output	same as input	Always called with the input record after all rows (and after NoData).

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.
SqlText	SQL (editor)	comment template	The statement, with parameter placeholders (below). Triggers reconfigure.
ParamTypeName	string		Optional. Name of a flow type to create/extend with one property per input parameter.
TypePrefix	string	Db	Prefix added to OutputTypeName (unless it already starts with it).
NoOutput	boolean	false	true runs the statement as non-query and emits nothing; Output must then have no connections (start fails otherwise).
DynamicOutput	boolean	false	true emits a DynamicResultset instead of a generated type. Triggers reconfigure.
OutputTypeName	string		Name of the generated result type. Required when the statement returns columns or has output parameters, unless DynamicOutput or NoOutput is set.
CommandTimeOut	integer (seconds)	20	Command timeout.
KeepOriginalNames	boolean	false	true keeps column names as property names; false converts them (and the type name) to C# names.
BatchMode	dropdown: 1 Auto, 2 Always, 3 Never	1	Whether the rows of one execution form a batch for batch consumers (e.g. Write file). Auto: only if the input record is not already in a batch (Timer, Http listener); a lookup per record of a batch (e.g. per row of a read file) keeps that batch. Always: a child batch per execution. Never: no batch.

Parameters

Placeholders in curly braces outside strings and comments become driver parameters (@Name, :Name on Oracle):

• {Name} - value of property Name of the input record.
• {Name|Type}, {Name|Type|default}, {Name|Type|default|out} - Type is a .NET type name (Int32, String, DateTime?, ...); default is sent when the value is null (parsed with the invariant culture); out/output makes it an input-output parameter.
• {input.Name} / {inputData.Name} - input record (same as {Name}); {meta.Name} / {metadata.Name} - message metadata field; {config.Name} (conf, flowconfig, configdata) - flow configuration value.
• The same name used several times is one parameter. A null value without a default is sent as DBNull.

Shaping placeholders, filled from a DbShapeRequest input (Projections, Filter, Sort, Skip, Take): {projection}, {where}, {and where}, {or where}, {order by}, {fetchnext} (case-insensitive, first occurrence of each only). With any other input, or a shape request without filter/sort/paging, they get neutral values: * for the projection, 1 = 1 for {and where}, nothing for the others.

Behaviour

• Reconfigure (changing SqlText or DynamicOutput) extracts the parameters, inspects the statement on the database and generates the output type TypePrefix + OutputTypeName (stored back into OutputTypeName) with one property per column plus output parameters, and sets it on Output. With DynamicOutput the pin gets DynamicResultset and the previously generated type is removed. If ParamTypeName is set, that type is created or extended with the input parameter properties (types from the placeholders where the input type doesn't have them yet). NoData/Continue get the current input type.
• Changing OutputTypeName, ParamTypeName, TypePrefix or KeepOriginalNames alone does not regenerate types: touch SqlText afterwards.
• When a batch is opened (BatchMode), it ends after the last row was processed downstream and before NoData/Continue are called, so components behind them see the committed output. An error during the query or the downstream processing fails the batch.
• Per input record the statement is executed once. The result boundaries are the batch (BatchMode).
• If SqlText was changed at runtime (pre-execution script), the plugin re-prepares the statement before running.

Notes

• Parameter types are resolved from the input type's properties (or the metadata / flow configuration type), so at start the input record type must contain every referenced input property. At reconfigure a missing property is allowed if the placeholder declares a Type.
• A statement without result columns (insert, update, delete) runs as non-query. NoData is called when it affected 0 rows; when the row count is unknown (-1, e.g. SET NOCOUNT ON or DDL) it is not.
• Output parameters are emitted reliably only for statements without result columns. On a query that returns rows they are set on every row only if the driver provides them before the reader is closed - SQL Server doesn't, so they are null there.
• The dynamic result set is emitted even when empty. Typed rows are read but not emitted when Output has no connections.
• Prefer a database link over credentials in SQL; never concatenate user input into SqlText, use placeholders.

Example

{ "ConnectionString": "MainDb", "SqlText": "SELECT * FROM dbo.Orders WHERE CustomerId = {CustomerId|Int32} {and where} {order by}", "OutputTypeName": "OrderRow", "TypePrefix": "Db" }

Http listener → Json deserializer → Sql query → (Output) Build array → Http response