Skip to content

SQL Functions

Overview

Martini's SQL Functions let you run SQL statements, open streaming cursors, and manage JDBC connections and transactions directly from Groovy code inside a service or workflow. They give you full control over the SQL you send to a database and how the results are consumed, which is useful when a query, its parameters, or its output shape need to be decided at runtime.

What You Will Learn

  • When to reach for SQL Functions instead of a SQL Service or the workflow SQL Database Node
  • How to obtain a JDBC connection or a Groovy Sql instance for a Martini database connection
  • How to run SELECT, INSERT, UPDATE, and DELETE statements with executeSelect and executeWriteSql
  • How to stream large result sets and batch writes using SQL cursors
  • How to retrieve generated keys from a batch insert
  • How to control JTA transactions with startTransaction, commitTransaction, and rollbackTransaction

When To Use This

Use SQL Functions instead of a dedicated SQL Service or the workflow SQL Database Node when:

  • The SQL statement text, its structure, or the number of statements to run must be determined at runtime rather than configured ahead of time. For a fixed statement with runtime values, use named parameters in an SQL Service or the SQL Database Node.
  • You need to create or manipulate a cursor directly from Groovy code — for example, to pass it between functions or combine it with other scripting logic — rather than have a SQL Service or SQL Database Node manage the cursor for you.
  • You need explicit control over a JTA transaction — for example, running several write operations across multiple statements or services that must commit or roll back together.
  • You're already writing Groovy in a Function Step or script and want to run SQL statements inline, without switching to a separate SQL Service or SQL Database Node.

If your use case is a single, well-known query or update that doesn't change at runtime, a SQL Service or the workflow SQL Database Node is usually simpler to build, test, and maintain.

Prerequisites

Connecting to a Database with SQL Functions

Most SQL Functions accept the name of a database connection already configured in Martini, rather than raw connection details, so you don't need to manage credentials or driver classes in your Groovy code.

SQL Functions Key Terms

Term Definition
databaseConnectionName The name of a database connection configured in Martini. Most SQL Functions use this to look up connection details automatically.
sqlStatement The literal SQL text to execute. Supports named query parameters (for example, :firstName) that are resolved from sqlParameters.
sqlParameters A data model whose properties supply the values for named parameters referenced in sqlStatement.
cursor A memory-efficient object for reading or writing database rows one at a time. See Martini Cursors.

Getting a Groovy Sql Instance

The sql function returns a Groovy Sql instance bound to a Martini database connection, giving you access to the full Groovy SQL API — eachRow, rows, execute, and more — instead of Martini's own SQL Functions.

flowchart LR
    a["`Get a **Sql** instance: **'my-connection-pool'.sql()**`"]
    b["`Iterate matching rows: **sql.eachRow('SELECT * FROM PROJECT') { row -> ... }**`"]
    c["`Release the connection: **sql.close()**`"]
    a --> b --> c

Because the returned Sql instance holds an open database connection, it's your responsibility to close it after use — call sql.close() in a finally block, or use the closure-based overload described next, which closes it for you.

Expected result: The Groovy Sql instance connects using the JDBC driver, URL, username, and password already configured for the my-connection-pool connection in Martini, without you having to supply any of those details.

Getting a Groovy Sql Instance Without Manual Cleanup

Pass a closure to sql to have Martini open the connection, hand you a Sql instance, and automatically close it once your closure finishes — including if it throws an exception.

1
2
3
4
5
'my-connection-pool'.sql() { sql ->
    sql.eachRow('SELECT * FROM PROJECT') { row ->
        // process each row
    }
}

The function returns whatever value your closure returns, so you can use it to compute and hand back a result without leaking an open connection.

Getting a Raw JDBC Connection

Use getDatabaseConnection when you need a plain java.sql.Connection instead of a Groovy Sql wrapper — for example, to pass it to another library or to manage its transaction state yourself with commitTransaction and rollbackTransaction. Two overloads are available:

  • getDatabaseConnection(databaseConnectionName: 'my-connection-pool') — resolves a connection from a database connection already configured in Martini by name.
  • getDatabaseConnection(connectionParameters: {...}, autoCommit: true) — opens a connection directly from a driverClassName, connectionUrl, username, and password you supply, without requiring a saved Martini connection. autoCommit defaults to true.

Always close a connection you obtained this way with closeConnection once you're finished with it. For a connection resolved by databaseConnectionName, this releases it back to Martini's connection pool; for a connection opened directly from connectionParameters, it closes the underlying JDBC connection.

flowchart LR
    a["`**Function Node:** Open the connection with **getDatabaseConnection** and map to _myConnection_`"]
    b["`**Try/Catch Node**`"]
    c["`Use _myConnection_`"]
    d["`**Function Node:** Release it with **closeConnection(connection: myConnection)**`"]
    a --> b -- try --> c
    b -- finally --> d

Common pitfall: Forgetting to call closeConnection (or sql.close()) leaks a database connection for every invocation, which can exhaust your connection pool under load. Always close connections in a finally block or use the closure-based sql overload, which handles this for you.

Executing SQL Statements with SQL Functions

For one-off statements where you don't need a cursor or a Sql instance, use executeSelect and executeWriteSql. Both accept a databaseConnectionName, an sqlStatement, and optional sqlParameters for named placeholders in the statement.

Reading a Single Row with executeSelect

executeSelect runs a SELECT statement and returns only the first row of the result set as a data model, named firstRow. Use it when you expect at most one matching row, such as a lookup by primary key.

executeSelect(databaseConnectionName: 'my-connection-pool', sqlStatement: 'SELECT * FROM CUSTOMER WHERE id = :customerId', sqlParameters: [customerId: 42]) returns the matching row as firstRow.

Expected result: firstRow is a data model whose properties match the columns of the matching CUSTOMER row, or null if no row matched.

If you need every matching row instead of just the first, use getCursorForSelectStatement instead.

Writing Data with executeWriteSql

executeWriteSql runs a write statement — INSERT, UPDATE, DELETE, or a DDL statement such as CREATE, ALTER, or DROP — against the specified connection.

executeWriteSql(databaseConnectionName: 'my-connection-pool', sqlStatement: 'UPDATE CUSTOMER SET STATUS = :status WHERE id = :customerId', sqlParameters: [status: 'ACTIVE', customerId: 42]) returns the number of affected rows as updateCount.

Expected result: updateCount holds the number of rows affected by a DML statement (INSERT, UPDATE, or DELETE), or 0 for statements that don't return a row count, such as DDL statements.

Deprecated: The older executeSql function performs the same write operation but has a less descriptive name. It still works, but new services and workflows should use executeWriteSql instead.

Reading and Writing Large Result Sets with SQL Cursors

executeSelect and executeWriteSql are convenient for single rows or single statements, but they aren't suitable for large datasets because they don't stream data. When you need to read many rows or write many records without loading them all into memory at once, use a cursor instead.

Reading Large Result Sets with getCursorForSelectStatement

getCursorForSelectStatement runs a SELECT statement and returns a cursor, named selectCursor, that your workflow or service can iterate one row at a time. By default the cursor is read-only; set updateable to true (see below) to update the underlying result set through the cursor itself.

getCursorForSelectStatement(databaseConnectionName: 'my-connection-pool', sqlStatement: 'SELECT * FROM CUSTOMER WHERE status = :status', sqlParameters: [status: 'ACTIVE']) returns a cursor named selectCursor.

Shape the structure of each cursor entry with one of the following, evaluated in this order of precedence:

  • modelReferenceName — the fully qualified namespace of an existing data model to use for each entry
  • modelTemplate — a populated data model instance whose non-null properties define each entry's structure
  • If neither is provided, Martini derives the entry structure from the result set's column names

Set updateable to true if you need to update the underlying result set through the cursor itself; it defaults to false.

Expected result: selectCursor is a data model array you can iterate with a Repeat Node or Iterate Step, reading one CUSTOMER row per iteration without buffering the entire result set in memory.

Writing Large Batches with getCursorForInsertUpdateStatement

getCursorForInsertUpdateStatement returns a write-only cursor, named insertUpdateCursor, for streaming a large number of records into a write statement without holding them all in memory.

getCursorForInsertUpdateStatement(databaseConnectionName: 'my-connection-pool', sqlStatement: 'INSERT INTO CUSTOMER (FIRSTNAME, LASTNAME, EMAIL) VALUES (:firstName, :lastName, :email)', batchSize: 100) returns a write-only cursor named insertUpdateCursor.

Use model to shape the cursor entry structure the same way modelTemplate does for read cursors. batchSize controls how many entries are buffered before Martini sends them to the database as a single batch operation; it defaults to -1, which sends every entry individually.

Expected result: insertUpdateCursor is a data model array. Appending an entry to it — for example, from a Repeat Node processing a source dataset — queues a row for insertion, and Martini flushes queued rows to the database once batchSize entries have accumulated.

Retrieving Generated Keys from a Batch Insert

getCursorForInsertUpdateStatement automatically executes its INSERT statement with the RETURN_GENERATED_KEYS flag enabled, so getInsertedKeysCursor can read back the keys the database generated for each inserted row — for example, auto-incremented primary keys.

getInsertedKeysCursor(batchCursor: insertUpdateCursor) returns a read-only cursor of generated keys named insertedKeysCursor.

This function throws an SQLException if the provided batchCursor isn't a batch cursor returned by getCursorForInsertUpdateStatement, or if it has already been closed.

Expected result: insertedKeysCursor is a read-only cursor you can iterate to retrieve the generated key for each row inserted through insertUpdateCursor, in the same order the rows were written.

Managing Transactions with SQL Functions

SQL Functions can participate in Martini's JTA transaction manager, letting you group several write operations — potentially across multiple statements, cursors, or services — so they either all commit or all roll back together.

Starting a JTA Transaction with startTransaction

startTransaction begins a new JTA transaction using the Martini transaction manager, subject to the propagationType you choose. This only works in running services and workflows — it has no effect while debugging.

startTransaction(autoCommit: false, timeout: 60, propagationType: 'REQUIRED') returns a TransactionFacade representing the active transaction.

  • autoCommit — when true, Martini attempts to commit the transaction automatically once the service or workflow finishes, even if an exception was thrown; defaults to false
  • timeout — the number of seconds to wait before the transaction times out; defaults to 60
  • propagationType — how this transaction relates to any transaction already in progress. SUPPORTS (the default) joins an existing transaction but doesn't start one of its own, so a call with no transaction already in progress runs non-transactionally. Use REQUIRED to guarantee a transaction is always started or joined. MANDATORY and NEVER are also supported

Expected result: A TransactionFacade object representing the active transaction, which you can pass to commitTransaction or rollbackTransaction to control its outcome explicitly. A startTransaction overload without propagationType is also available for simpler cases where you only need autoCommit and timeout.

Committing and Rolling Back Transactions

Once you're done with a unit of work, commit it to make the changes permanent, or roll it back to discard them. Each function has an overload for the current JTA transaction and an overload that targets a specific connection or TransactionFacade:

  • commitTransaction() — commits the current JTA transaction
  • commitTransaction(connection: myConnection) — commits changes made through a specific JDBC connection
  • commitTransaction(transaction: myTransaction) — commits a specific TransactionFacade returned by startTransaction
  • rollbackTransaction() — rolls back the current JTA transaction
  • rollbackTransaction(connection: myConnection) — rolls back changes made through a specific JDBC connection
  • rollbackTransaction(transaction: myTransaction) — rolls back a specific TransactionFacade returned by startTransaction

Transferring funds atomically across two write statements:

flowchart LR
    a["`**Function Node:** Start the transaction with **startTransaction** and map to _myTransaction_`"]
    b["`**Try/Catch Node**`"]
    c["`**Function Node:** Debit the source account with **executeWriteSql**`"]
    d["`**Function Node:** Credit the destination account with **executeWriteSql**`"]
    e["`**Function Node:** **commitTransaction(transaction: myTransaction)**`"]
    f["`**Function Node:** **rollbackTransaction(transaction: myTransaction)**`"]
    a --> b -- try --> c --> d --> e
    b -- catch --> f

Expected result: Both UPDATE statements commit together, or neither does — the account balances never end up in a partially updated state.

Troubleshooting

Problem Detection Cause Fix
SQLException: connection with the provided name could not be found Function call fails immediately databaseConnectionName doesn't match a connection configured in Martini Verify the exact connection name in SQL Database Connection and correct any typos
Connection pool runs out of available connections Later calls hang or fail under load A connection obtained from getDatabaseConnection (or a Sql instance from sql) was never closed Always call closeConnection or sql.close(), or use the closure-based sql overload
getInsertedKeysCursor throws an SQLException Exception thrown right after a batch insert The batch insert wasn't executed with the RETURN_GENERATED_KEYS flag, or the batch cursor is already closed Confirm the insert cursor was configured to return generated keys, and call getInsertedKeysCursor before closing the batch cursor
startTransaction has no effect Changes commit individually instead of as a unit The service or workflow is running under the debugger Run the service or workflow normally; JTA transactions only work outside of debugging
Named parameter in sqlStatement isn't substituted SQLException or unexpected SQL syntax error The property name in sqlParameters doesn't match the named parameter in sqlStatement Ensure every :parameterName in sqlStatement has a matching property in sqlParameters

Helpful Resources