CtrlK
BlogDocsLog inGet started
Tessl Logo

tessl/maven-org-apache-groovy--groovy-sql

Higher-level abstraction over Java's JDBC technology providing simplified database operations with resource management

—

Pending
Overview
Eval results
Files

stored-procedures.mddocs/

Stored Procedures

Complete stored procedure support with input/output parameters, result set processing, and CallableStatement wrapper functionality.

Capabilities

Basic Stored Procedure Calls

Execute stored procedures with input parameters and retrieve output values.

// Basic procedure calls
public List call(String sql) throws SQLException;
public List call(String sql, List<?> params) throws SQLException;
public List call(String sql, List<?> params, Closure closure) throws SQLException;
public List call(GString gstring) throws SQLException;

// Map-based parameter calls
public List call(String sql, Map params) throws SQLException;
public List call(Map params, String sql) throws SQLException;
public List call(String sql, Map params, Closure closure) throws SQLException;
public List call(Map params, String sql, Closure closure) throws SQLException;

Example:

import static groovy.sql.Sql.*

// Simple procedure call with no parameters
def result = sql.call('CALL get_server_time()')
println "Server time: ${result[0]}"

// Procedure with input parameters
def userId = 123
def results = sql.call('CALL get_user_details(?)', [userId])
results.each { row ->
    println "User data: $row"
}

// Using output parameters
def params = [
    INTEGER(userId),  // input parameter
    out(VARCHAR),     // output parameter for user name
    out(INTEGER)      // output parameter for user count
]
def outputs = sql.call('CALL get_user_info(?, ?, ?)', params)
println "User name: ${outputs[0][1]}"  // First row, second column (first output param)
println "User count: ${outputs[0][2]}" // First row, third column (second output param)

Result Set Processing

Process result sets returned by stored procedures with various processing options.

// Process first result set only
public void callWithRows(String sql, Closure closure) throws SQLException;
public void callWithRows(String sql, List<?> params, Closure closure) throws SQLException;
public void callWithRows(String sql, List<?> params, Closure metaClosure, Closure rowClosure) throws SQLException;

// Process all result sets
public void callWithAllRows(String sql, Closure closure) throws SQLException;
public void callWithAllRows(String sql, List<?> params, Closure closure) throws SQLException;
public void callWithAllRows(String sql, List<?> params, Closure metaClosure, Closure rowClosure) throws SQLException;

// Result set processing constants
public static final int NO_RESULT_SETS = 0;
public static final int FIRST_RESULT_SET = 1;
public static final int ALL_RESULT_SETS = 2;

Example:

// Process first result set from procedure
sql.callWithRows('CALL get_active_users()') { row ->
    println "Active user: ${row.name} (${row.email})"
}

// Process first result set with parameters
sql.callWithRows('CALL get_users_by_department(?)', ['Engineering']) { row ->
    println "${row.name} - ${row.title}"
}

// Process all result sets (procedure returns multiple result sets)
sql.callWithAllRows('CALL get_user_summary(?)') { resultSet ->
    println "Processing result set..."
    while (resultSet.next()) {
        println "Row: ${resultSet.getString(1)}"
    }
}

// With metadata processing
sql.callWithRows('CALL get_report_data(?)', [reportId],
    { meta ->
        println "Report has ${meta.columnCount} columns"
        (1..meta.columnCount).each { i ->
            println "Column $i: ${meta.getColumnName(i)}"
        }
    },
    { row ->
        println "Data row: $row"
    }
)

Input/Output Parameters

Handle complex parameter combinations including input, output, and bidirectional parameters.

// Parameter type interfaces (defined in parameters.md)
public interface InParameter {
    int getType();
    Object getValue();
}

public interface OutParameter {
    int getType();
}

public interface InOutParameter extends InParameter, OutParameter {}

public interface ResultSetOutParameter extends OutParameter {}

Example:

import static groovy.sql.Sql.*
import java.sql.Types

// Complex parameter scenario
def params = [
    VARCHAR('john.doe'),        // input: username
    out(INTEGER),              // output: user ID
    out(VARCHAR),              // output: full name  
    out(TIMESTAMP),            // output: last login
    inout(VARCHAR('active'))   // input/output: status (input 'active', may be changed by procedure)
]

def results = sql.call('CALL authenticate_user(?, ?, ?, ?, ?)', params)

// Extract output values from first result row
def outputRow = results[0]
def userId = outputRow[1]        // First output parameter (user ID)
def fullName = outputRow[2]      // Second output parameter (full name)
def lastLogin = outputRow[3]     // Third output parameter (last login)
def finalStatus = outputRow[4]   // InOut parameter final value

println "Authentication result:"
println "User ID: $userId"
println "Name: $fullName" 
println "Last login: $lastLogin"
println "Status: $finalStatus"

// Handle null outputs
if (userId == null) {
    println "Authentication failed"
} else {
    println "User authenticated successfully"
}

ResultSet Output Parameters

Handle stored procedures that return result sets as output parameters.

public static ResultSetOutParameter resultSet(int type);

Example:

import static groovy.sql.Sql.*

// Procedure with ResultSet output parameter
def params = [
    INTEGER(departmentId),     // input: department ID
    resultSet(Types.OTHER)     // output: result set of employees
]

def results = sql.call('CALL get_department_employees(?, ?)', params)

// The ResultSet is returned as part of the output
def employeeResultSet = results[0][1]  // Second column contains the ResultSet

// Process the returned ResultSet
if (employeeResultSet instanceof ResultSet) {
    while (employeeResultSet.next()) {
        println "Employee: ${employeeResultSet.getString('name')} - ${employeeResultSet.getString('title')}"
    }
    employeeResultSet.close()
}

Complex Stored Procedure Scenarios

Handle advanced stored procedure patterns including multiple result sets, cursors, and error handling.

Example:

// Procedure returning multiple result sets
sql.call('CALL get_comprehensive_report(?)', [reportId]) { allResults ->
    allResults.eachWithIndex { resultSet, index ->
        println "Processing result set $index:"
        
        // Get metadata for this result set
        def meta = resultSet.metaData
        def columnNames = (1..meta.columnCount).collect { meta.getColumnName(it) }
        println "Columns: ${columnNames.join(', ')}"
        
        // Process rows
        while (resultSet.next()) {
            def row = columnNames.collectEntries { col ->
                [col, resultSet.getObject(col)]
            }
            println "Row: $row"
        }
        println "---"
    }
}

// Error handling with stored procedures
try {
    def params = [
        INTEGER(accountId),
        DECIMAL(amount),
        out(VARCHAR),  // output: error message
        out(INTEGER)   // output: error code
    ]
    
    def results = sql.call('CALL transfer_funds(?, ?, ?, ?)', params)
    
    def errorMessage = results[0][2]
    def errorCode = results[0][3]
    
    if (errorCode != 0) {
        println "Transfer failed: $errorMessage (code: $errorCode)"
    } else {
        println "Transfer successful"
    }
    
} catch (SQLException e) {
    println "Database error during transfer: ${e.message}"
}

// Working with cursor-based procedures
def cursorParams = [
    INTEGER(pageSize),
    INTEGER(offset),
    out(Types.OTHER)  // cursor output
]

sql.call('CALL get_paged_results(?, ?, ?)', cursorParams) { results ->
    // Handle cursor-based result processing
    // Implementation depends on database-specific cursor handling
}

Batch Stored Procedure Calls

Execute stored procedures in batch mode for improved performance.

Example:

// Batch procedure calls with same parameters structure
def userUpdates = [
    [userId: 1, status: 'active', lastLogin: new Date()],
    [userId: 2, status: 'inactive', lastLogin: new Date() - 30],
    [userId: 3, status: 'pending', lastLogin: null]
]

sql.withBatch('CALL update_user_status(?, ?, ?)') { stmt ->
    userUpdates.each { update ->
        stmt.addBatch([
            INTEGER(update.userId),
            VARCHAR(update.status),
            update.lastLogin ? TIMESTAMP(update.lastLogin) : null
        ])
    }
}

// Batch with different procedure calls
sql.withBatch { stmt ->
    // Different procedures in same batch
    stmt.addBatch('CALL log_user_action(?, ?)', [INTEGER(userId), VARCHAR('login')])
    stmt.addBatch('CALL update_last_seen(?)', [INTEGER(userId)])
    stmt.addBatch('CALL increment_login_count(?)', [INTEGER(userId)])
}

Database-Specific Stored Procedure Examples

Handle database-specific stored procedure patterns and syntax variations.

Example:

// MySQL stored procedure with multiple outputs
def mysqlParams = [
    INTEGER(customerId),
    out(DECIMAL),      // total_orders
    out(DECIMAL),      // total_amount
    out(INTEGER)       // order_count
]
def mysqlResults = sql.call('CALL get_customer_stats(?, ?, ?, ?)', mysqlParams)

// Oracle procedure with cursor
def oracleParams = [
    INTEGER(departmentId),
    out(Types.OTHER)   // REF CURSOR
]
// Oracle-specific cursor handling would go here

// SQL Server procedure with table-valued parameter
def sqlServerData = [
    [id: 1, name: 'Item 1', price: 10.99],
    [id: 2, name: 'Item 2', price: 15.99]
]
// SQL Server table-valued parameters require special handling

// PostgreSQL function call (functions vs procedures)
def pgResult = sql.firstRow('SELECT * FROM calculate_interest(?, ?)', [principal, rate])
println "Interest: ${pgResult.interest_amount}"

Install with Tessl CLI

npx tessl i tessl/maven-org-apache-groovy--groovy-sql

docs

batch-operations.md

core-operations.md

dataset.md

index.md

parameters.md

result-processing.md

stored-procedures.md

transactions.md

tile.json