CtrlK
BlogDocsLog inGet started
Tessl Logo

tessl/maven-org-codehaus-groovy--groovy-sql

A facade over Java's JDBC APIs providing greatly simplified resource management and result set handling with Groovy-specific enhancements.

—

Pending
Overview
Eval results
Files

parameters-procedures.mddocs/

Parameter Types and Stored Procedures

Groovy SQL provides a comprehensive type-safe parameter system for stored procedure calls, supporting input, output, and input/output parameters with proper JDBC type mapping and automatic result handling.

Parameter Interfaces

InParameter

Interface for input parameters with type information:

interface InParameter {
    int getType()      // Get JDBC data type constant
    Object getValue()  // Get parameter value
}

OutParameter

Interface for output parameters from stored procedures:

interface OutParameter {
    int getType()  // Get JDBC data type constant
}

InOutParameter

Interface for bidirectional parameters (both input and output):

interface InOutParameter extends InParameter, OutParameter {
    // Inherits getType() from both interfaces
    // Inherits getValue() from InParameter
}

ResultSetOutParameter

Interface for result set output parameters:

interface ResultSetOutParameter extends OutParameter {
    // Inherits getType() from OutParameter
}

ExpandedVariable

Interface for variables that should be expanded into SQL rather than parameterized:

interface ExpandedVariable {
    Object getObject()  // Get the object to expand into SQL
}

Parameter Factory Methods

Input Parameter Creation

Create typed input parameters:

static InParameter in(int type, Object value)

Output Parameter Creation

Create output parameters for stored procedures:

static OutParameter out(int type)

Bidirectional Parameter Creation

Create input/output parameters:

static InOutParameter inout(InParameter in)

Result Set Parameter Creation

Create result set output parameters:

static ResultSetOutParameter resultSet(int type)

Variable Expansion

Create variables for SQL expansion:

static ExpandedVariable expand(Object object)

Predefined Type Constants

OutParameter Constants

Pre-defined output parameter constants for all JDBC types:

static final OutParameter ARRAY
static final OutParameter BIGINT
static final OutParameter BINARY
static final OutParameter BIT
static final OutParameter BLOB
static final OutParameter BOOLEAN
static final OutParameter CHAR
static final OutParameter CLOB
static final OutParameter DATALINK
static final OutParameter DATE
static final OutParameter DECIMAL
static final OutParameter DISTINCT
static final OutParameter DOUBLE
static final OutParameter FLOAT
static final OutParameter INTEGER
static final OutParameter JAVA_OBJECT
static final OutParameter LONGVARBINARY
static final OutParameter LONGVARCHAR
static final OutParameter NULL
static final OutParameter NUMERIC
static final OutParameter OTHER
static final OutParameter REAL
static final OutParameter REF
static final OutParameter SMALLINT
static final OutParameter STRUCT
static final OutParameter TIME
static final OutParameter TIMESTAMP
static final OutParameter TINYINT
static final OutParameter VARBINARY
static final OutParameter VARCHAR

Typed InParameter Factory Methods

Create typed input parameters using convenience methods:

static InParameter ARRAY(Object value)
static InParameter BIGINT(Object value)
static InParameter BINARY(Object value)
static InParameter BIT(Object value)
static InParameter BLOB(Object value)
static InParameter BOOLEAN(Object value)
static InParameter CHAR(Object value)
static InParameter CLOB(Object value)
static InParameter DATALINK(Object value)
static InParameter DATE(Object value)
static InParameter DECIMAL(Object value)
static InParameter DISTINCT(Object value)
static InParameter DOUBLE(Object value)
static InParameter FLOAT(Object value)
static InParameter INTEGER(Object value)
static InParameter JAVA_OBJECT(Object value)
static InParameter LONGVARBINARY(Object value)
static InParameter LONGVARCHAR(Object value)
static InParameter NULL(Object value)
static InParameter NUMERIC(Object value)
static InParameter OTHER(Object value)
static InParameter REAL(Object value)
static InParameter REF(Object value)
static InParameter SMALLINT(Object value)
static InParameter STRUCT(Object value)
static InParameter TIME(Object value)
static InParameter TIMESTAMP(Object value)
static InParameter TINYINT(Object value)
static InParameter VARBINARY(Object value)
static InParameter VARCHAR(Object value)

Stored Procedure Calls

Simple Procedure Calls

Execute stored procedures and return result codes:

int call(String sql) throws Exception
int call(String sql, List<Object> params) throws Exception
int call(String sql, Object[] params) throws Exception
int call(GString gstring) throws Exception

Procedure Calls with Output Processing

Execute procedures and process output parameters:

void call(String sql, List<Object> params, Closure closure) throws Exception
void call(GString gstring, Closure closure) throws Exception

The closure receives an Object[] containing all output parameter values.

Procedure Calls Returning Rows

Execute procedures that return result sets:

List<GroovyRowResult> callWithRows(String sql, List<Object> params, Closure closure) throws SQLException
List<GroovyRowResult> callWithRows(GString gstring, Closure closure) throws SQLException

Procedure Calls Returning Multiple Result Sets

Execute procedures that return multiple result sets:

List<List<GroovyRowResult>> callWithAllRows(String sql, List<Object> params, Closure closure) throws SQLException
List<List<GroovyRowResult>> callWithAllRows(GString gstring, Closure closure) throws SQLException

Usage Examples

Basic Parameter Types

import groovy.sql.Sql
import static groovy.sql.Sql.*

def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")

// Create typed input parameters
def nameParam = VARCHAR("John Doe")
def ageParam = INTEGER(30)
def salaryParam = DECIMAL(75000.50)
def activeParam = BOOLEAN(true)

// Use in query
sql.execute("INSERT INTO employees (name, age, salary, active) VALUES (?, ?, ?, ?)",
           [nameParam, ageParam, salaryParam, activeParam])

Simple Stored Procedure Call

def sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:xe", "user", "pass", "oracle.jdbc.OracleDriver")

// Call procedure with input parameters
def empId = 100
def newSalary = 85000
def result = sql.call("{ call update_salary(?, ?) }", [empId, newSalary])
println "Procedure returned: ${result}"

Stored Procedure with Output Parameters

def sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:xe", "user", "pass", "oracle.jdbc.OracleDriver")

// Call procedure with input and output parameters
def empId = 100
def params = [
    INTEGER(empId),           // Input parameter
    out(VARCHAR),             // Output: employee name
    out(DECIMAL),             // Output: current salary
    out(DATE)                 // Output: hire date
]

sql.call("{ call get_employee_info(?, ?, ?, ?) }", params) { outParams ->
    def employeeName = outParams[0]
    def currentSalary = outParams[1] 
    def hireDate = outParams[2]
    
    println "Employee: ${employeeName}"
    println "Salary: \$${currentSalary}"
    println "Hired: ${hireDate}"
}

Input/Output Parameters

def sql = Sql.newInstance("jdbc:sqlserver://localhost:1433;databaseName=test", "user", "pass", "com.microsoft.sqlserver.jdbc.SQLServerDriver")

// Create input parameter that will also receive output
def salaryParam = DECIMAL(75000)
def inOutSalary = inout(salaryParam)

def params = [
    INTEGER(100),             // Employee ID (input)
    DECIMAL(0.10),           // Raise percentage (input)
    inOutSalary              // Current salary (input/output)
]

sql.call("{ call give_raise(?, ?, ?) }", params) { outParams ->
    def newSalary = outParams[0]  // The updated salary
    println "New salary: \$${newSalary}"
}

Procedure Returning Result Set

def sql = Sql.newInstance("jdbc:postgresql://localhost:5432/test", "user", "pass", "org.postgresql.Driver")

// Call procedure that returns a result set
def deptId = 10
def employees = sql.callWithRows("{ call get_department_employees(?) }", [INTEGER(deptId)]) { outParams ->
    // Process any output parameters if present
    if (outParams) {
        println "Additional output: ${outParams}"
    }
}

employees.each { emp ->
    println "Employee: ${emp.name}, Salary: ${emp.salary}"
}

Multiple Result Sets

def sql = Sql.newInstance("jdbc:sqlserver://localhost:1433;databaseName=test", "user", "pass", "com.microsoft.sqlserver.jdbc.SQLServerDriver")

// Call procedure that returns multiple result sets
def allResults = sql.callWithAllRows("{ call get_company_report() }", []) { outParams ->
    if (outParams) {
        println "Report generated at: ${outParams[0]}"
    }
}

// Process multiple result sets
allResults.eachWithIndex { resultSet, index ->
    println "Result Set ${index + 1}:"
    resultSet.each { row ->
        println "  ${row}"
    }
}

Complex Parameter Scenarios

def sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:xe", "user", "pass", "oracle.jdbc.OracleDriver")

// Mix of different parameter types
def params = [
    VARCHAR("Engineering"),        // Department name
    out(INTEGER),                 // Output: department ID
    out(DECIMAL),                 // Output: average salary
    out(INTEGER),                 // Output: employee count
    resultSet(OTHER)              // Output: result set cursor
]

sql.call("{ call analyze_department(?, ?, ?, ?, ?) }", params) { outParams ->
    def deptId = outParams[0]
    def avgSalary = outParams[1]
    def empCount = outParams[2]
    def resultSetCursor = outParams[3]
    
    println "Department ID: ${deptId}"
    println "Average Salary: \$${avgSalary}"
    println "Employee Count: ${empCount}"
    
    // Process result set if available
    if (resultSetCursor instanceof ResultSet) {
        while (resultSetCursor.next()) {
            println "Detail: ${resultSetCursor.getString(1)}"
        }
    }
}

Using GString with Parameters

def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")

def employeeId = 100
def raisePercentage = 0.15

// GString call with parameter
sql.call("{ call give_raise(${INTEGER(employeeId)}, ${DECIMAL(raisePercentage)}, ${out(DECIMAL)}) }") { outParams ->
    def newSalary = outParams[0]
    println "Employee ${employeeId} new salary: \$${newSalary}"
}

Variable Expansion

def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")

// Use expand() for dynamic SQL parts that shouldn't be parameterized
def tableName = "employees"
def columnName = "salary"

def params = [
    expand(tableName),     // Will be expanded directly into SQL
    expand(columnName),    // Will be expanded directly into SQL  
    DECIMAL(50000)         // Will be parameterized normally
]

// This allows dynamic table/column names while still using parameters for values
sql.eachRow("SELECT * FROM ${params[0]} WHERE ${params[1]} > ?", [params[2]]) { row ->
    println "Employee: ${row.name}, Salary: ${row.salary}"
}

Error Handling with Procedures

def sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:xe", "user", "pass", "oracle.jdbc.OracleDriver")

try {
    def params = [
        INTEGER(999),          // Non-existent employee ID
        out(VARCHAR),          // Error message output
        out(INTEGER)           // Error code output
    ]
    
    sql.call("{ call safe_update_employee(?, ?, ?) }", params) { outParams ->
        def errorMessage = outParams[0]
        def errorCode = outParams[1]
        
        if (errorCode != 0) {
            println "Procedure error ${errorCode}: ${errorMessage}"
        } else {
            println "Update successful"
        }
    }
} catch (SQLException e) {
    println "SQL Error: ${e.message}"
}

Install with Tessl CLI

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

docs

batch-transactions.md

core-operations.md

dataset-operations.md

index.md

parameters-procedures.md

result-handling.md

tile.json