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 the operation, a parameterised query, a stored procedure call, or a composite of inserts and selects, is configured on the send port; the SQL Authentication paired with the adapter carries the connection string and credentials.

Query types

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

Parameterised SINGLE STATEMENT SELECT * FROM Orders WHERE Region = @region AND Status = @status Named parameters @param => variable / property SELECT / INSERT / UPDATE / DELETE Stored procedure NAMED CALL EXEC dbo.SubmitOrder @order_id = ?, @customer = ?, @total = ? OUT 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:

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

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. The Authentication carries the connection string, credentials, and the SQL dialect that the runtime targets. Supported dialects (verify against product): SQL Server, Azure SQL, PostgreSQL, MySQL, Oracle. 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.

Query Type, required; one of Parameterised, Stored Procedure, or Composite.
Statement, required when Query Type is Parameterised or Composite; the SQL text. Named parameters use @name syntax.
Procedure Name, required when Query Type is Stored Procedure; schema-qualified name.
Parameter Bindings, required when parameters are present; one row per parameter mapping the parameter name to a variable, message property, or xPath expression. 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.
Authentication, 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.