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

p6.sproc.buildDataSource("name", [:], "table_name")

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

p6.sproc.buildDataSource("name", [:], "table_name", true)

execute¶

Executes the procedure with its identifier. The response is the execution’s result as a list of list of Strings.

Syntax

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

Dont ignore result

p6.sproc.execute("name")

Ignore result

p6.sproc.execute("name", 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

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

Dont ignore result

p6.sproc.execute("name", ["key":"value"])

Ignore result

p6.sproc.execute("name", ["key":"value"], true)

executeWithColumns¶

Executes the procedure with its identifier and additional parameters.

Syntax

Tuple<String[], String[][], 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

def result = p6.sproc.executeWithColumns("name")
def columns = result.first
def records = result.second
def types = result.third

With parameters

def result = p6.sproc.executeWithColumns("name", ["key":"value"])