Stored Procedure
Purpose¶
Execute a stored procedure and return the results. Stored procedures are either UserDefined or are part of a reserved set provided with Platform 6.
Methods¶
Binding name: p6.sproc
buildDataSource¶
Execute a query which will create a new table in the database.
Syntax
long p6.sproc.buildDataSource(String procId, Map parameters, String dataSourceName)
The query is found with its identifier. It will not append records in the table if it already exists.
Info
The table will be attached to the default schema (cf. database.schema configuration) and named ds_<dataSourceName>
Example
// Build a data source table for the invoice dashboard chart
def rowCount = p6.sproc.buildDataSource('O2C.InvoiceSummary', [:], 'invoice_summary')
p6.log.debug "Created data source with ${rowCount} rows"
buildDataSource (append)¶
Execute a query which will create a new table in the database.
Syntax
long p6.sproc.buildDataSource(String procId, Map parameters, String dataSourceName, boolean appendToDataSource)
The query is found with its identifier.
The argument appendToDataSource specifies if you add the records in the table if it already exists.
Info
The table will be attached to the default schema (cf. database.schema configuration) and named ds_<dataSourceName>
Example
// Append new daily records to the existing data source
def rowCount = p6.sproc.buildDataSource('O2C.DailyTransactions', ['date': '2024-03-15'], 'daily_transactions', true)
p6.log.debug "Appended ${rowCount} rows to daily_transactions"
execute¶
Executes the procedure with its identifier. The response is the execution’s result as a list of list of Strings.
Syntax
List<List<String>> p6.sproc.execute(String procedureId [, boolean ignoreResult])
Tip
You can add a last optional argument named ignoreResult.
If true the response of the procedure will be ignored.
If false the response is the execution’s result as a list of list of Strings.
Examples
Retrieve and process results
def rows = p6.sproc.execute('O2C.PendingInvoices')
p6.log.debug "Found ${rows.size()} pending invoices"
rows.each { row ->
p6.log.debug "Invoice: ${row[0]}, Amount: ${row[1]}"
}
Ignore result (fire-and-forget)
p6.sproc.execute('O2C.CleanupExpiredSessions', true)
execute (with parameters)¶
Executes the procedure with its identifier and additional parameters. The response is the execution’s result as a list of list of Strings.
Syntax
List<List<String>> p6.sproc.execute(
String procedureId,
Map parameters
[, boolean ignoreResult]
)
Tip
You can add a last optional argument named ignoreResult.
If true the response of the procedure will be ignored.
If false the response is the execution’s result as a list of list of Strings.
Examples
Query invoices by supplier
def rows = p6.sproc.execute('O2C.InvoicesBySupplier', ['supplier': 'ACME-CORP'])
p6.log.debug "Found ${rows.size()} invoices for ACME-CORP"
Run a cleanup without needing results
p6.sproc.execute('O2C.ArchiveProcessed', ['olderThanDays': '90'], true)
executeWithColumns¶
Executes the procedure with its identifier and additional parameters.
Syntax
Tuple3<List<String>, List<List<String>>, List<Integer>> p6.sproc.executeWithColumns(
String procedureId
[, Map parameters]
)
The response is the execution’s result as a tuple with:
- first: columns as a list of Strings
- second: records as a list of list of Strings
- third: columns type as list of Integer (See ‘java.sql.Types’)
Tip
You can add a last optional argument named parameters.
Examples
// Query with column metadata for dynamic table rendering
def result = p6.sproc.executeWithColumns('O2C.InvoiceReport')
def columns = result.first // ['InvoiceNumber', 'Supplier', 'Amount', 'Status']
def records = result.second // [['INV-0042', 'ACME-CORP', '1250.00', 'APPROVED'], ...]
def types = result.third // [12, 12, 8, 12] (java.sql.Types constants)
p6.log.debug "Report has ${columns.size()} columns and ${records.size()} rows"
With parameters
def result = p6.sproc.executeWithColumns('O2C.InvoicesByStatus', ['status': 'PENDING'])