Skip to content
Art2link ESB v2.02 LTS HomeDocumentationBlogContact
Core concepts/Adapters/SQL adapter
Adapter

SQL adapter

A send-side adapter that runs a SQL statement, stored procedure, or composite query. In two-way mode the result set is republished to the bus as a payload message.

ADAPTER · SEND SQL Runs a query, stored procedure, or composite statement against a SQL database Shipping DIRECTION ↑ Send MODES One-way · Two-way AUTHENTICATION SQL Authentication PORT Send port RESPONSE Payload

The SQL adapter takes a message off the bus and uses it to issue a database operation, the shape of which is configured on the send port. The SQL Authentication paired with the adapter carries the connection string and credentials.

Command types

Three operation shapes are supported. The choice is the Command Type on the send port; what you configure underneath changes per type.

Parameterised SINGLE STATEMENT SELECT * FROM Orders WHERE … INPUT PARAMETERS Region ← {{Promoted.Order.Region}} Status ← {{Variable.Status}} Input Parameters Name ← {{expr}}, one row each SELECT / INSERT / UPDATE / DELETE Stored procedure NAMED CALL dbo.SubmitOrder INPUT PARAMETERS OrderId ← {{Promoted.Order.Id}} CustomerId ← {{Variable.CustId}} In/out parameters OUT values returned as columns use when the DB owns the logic Composite MULTI-STATEMENT · ONE TX INSERT INTO OrderHdr ... INSERT INTO OrderLine ... UPDATE Inventory ... SELECT @@IDENTITY Single transaction all-or-nothing rollback good for multi-table writes
Response shape

In two-way mode the result set is materialised, wrapped, and republished to the bus as a payload message. The format of that payload is decided by the SELECT, not the adapter: a plain SELECT with no FOR clause returns JSON, a SELECT that ends in FOR XML returns XML, and FOR JSON returns JSON explicitly. The diagram below shows the XML envelope a FOR XML query produces:

RESULT SET id | total | status 7831 | 124.50 | New 7832 | 89.00 | New 7833 | 240.00 | New columns × rows wrap BUS MESSAGE MT: SqlCallerResult <Result> <Row> <id>7831</id> <total>124.50</total> <status>New</status> </Row> <!-- … more rows --> </Result> publish Bus picked up by any subscriber downstream port
Match the map to what the SELECT emits. The result format follows the query, not the adapter: a plain SELECT returns JSON, and only a FOR XML clause makes it XML. If a downstream map expects XML but the query returned JSON (or the reverse), the map matches nothing and republishes an empty document. Decide the format in the SELECT, then give the map a source whose format agrees with it.

One-way mode runs the statement and discards the result. The runtime checks the statement's success or failure but does not republish anything.

Authentication

The SQL adapter pairs with a SQL Authentication built on the SQL Server Connection Definition; its Database Config carries one field, the Connection String, credentials included. SQL Server is the supported engine today; further engines arrive as Definitions of their own. The connection is opened from a pool maintained per Authentication object; the adapter does not own the connection lifecycle.

What you configure on the SQL adapter

Placeholder field set, verify against the running product.

Command Type, required; one of Parameterised, Stored Procedure, or Composite.
Command Text, required; the SQL text when Command Type is Parameterised or Composite, the schema-qualified procedure name when it is Stored Procedure.
SQL Request; the request the runtime issues, Command Text with the Input Parameters applied.
Input Parameters, required when parameters are present; a key/value table, one row per parameter as Name ← {{expr}}. The value side is a plain string with {{…}} bindings: a promoted property such as {{Promoted.Order.Total}}, a variable, or a constant. Body extractions are not evaluated here; promote the value on the message type or capture it into a variable first. Includes a direction column (In, Out, InOut) for stored procedures.
Command Timeout, optional; how long the database has to return before the statement is cancelled.
Isolation Level, optional; Read Committed, Snapshot, Serializable, etc. Defaults to the database's default.
Return Empty Result Behaviour, optional; when no rows are returned, choose Publish empty Result envelope, Suppress publication, or Raise as exception.
Auth Config, required; picker filtered to SQL Authentication objects in the same Application.

That is the SQL adapter

Send-side database access. Pick the query shape, bind the parameters, and the result, in two-way, rides back onto the bus as a payload message. Back to Adapters.