Execute SQL query

Executes an SQL query as a prepared statement and stores the result at a destination path.

Runs a SQL statement against a database and places the result in your data at a location you choose. Use it to look up records in the middle of a route, for example fetching stock levels or customer details to enrich the data flowing through, or to run an UPDATE or INSERT and record how many rows it changed. You write the statement once with named parameters such as :sku and supply the values separately, so values from your data are never pasted into the SQL text. This keeps the query safe when a value contains quotes or other special characters. This is the successor of the previous Execute SQL query transformer, which built the SQL text from placeholders.

Fields

Sample data

The rows you get back depend on the data in your database. In the example below the products table holds a product with SKU SHIRT-RED-M, and the transformer looks it up by the incoming sku.

Field	Value
Database connection	The configured database connection
Query	SELECT sku, name, price, stock FROM products WHERE sku = :sku
Parameters	sku = &{sku}
Destination	matches

Input:

{
    "sku": "SHIRT-RED-M"
}

Output:

{
    "sku": "SHIRT-RED-M",
    "matches": [
        {
            "sku": "SHIRT-RED-M",
            "name": "Red T-Shirt M",
            "price": 19.95,
            "stock": 12
        }
    ]
}

A statement that changes data stores the number of affected rows instead of a list. This example lowers the stock of the incoming product by the incoming quantity.

Field	Value
Query	UPDATE products SET stock = stock - :qty WHERE sku = :sku AND stock >= :qty
Parameters	sku = &{sku}, qty = &{qty}
Destination	result

For an input of { "sku": "SHIRT-RED-M", "qty": 2 } the output is:

{
    "sku": "SHIRT-RED-M",
    "qty": 2,
    "result": {
        "affectedRows": 1
    }
}