---
id: database--database-query
title: "Sql query"
summary: "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."
audience: user
keywords: "input, output, nodata, continue, connectionstring, sqltext, paramtypename, typeprefix, nooutput, dynamicoutput, outputtypename, commandtimeout, keeporiginalnames, batchmode"
---
# 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

```json
{ "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