system.db.createSProcCall

Description

Creates an SProcCall object, which is a stored procedure call context. This is an object that is used to configure a call to a stored procedure. Once configured, you'd use system.db.execSProcCall to call the stored procedure. The call context object then holds any results from the stored procedure. The SProcCall object has the following functions used for registering parameters:

SPRocCall.registerInParam(index OR name, typeCode, value)

SPRocCall.registerOutParam(index OR name, typeCode)

SPRocCall.registerReturnParam(typeCode)

These functions are used to register any in/out parameters for the stored procedure. Parameters can be referenced by index (starting at 1, not 0), or by name. To register an in/out parameter, you simply register it twice - once as an input parameter with the value you'd like to pass to the stored procedure, and once as an output parameter. N.B. not all JDBC drivers support named procedure parameters. If your function returns a value, you must use registerReturnParam to specify the datatype of the returned value. Note that this is different from stored procedures that return a result set, which doesn't require any setup on the SProcCall object. Some database systems call stored procedures that return a value "functions" instead of "procedures". For all of these functions, you'll need to specify a type code. These are codes defined by the JDBC specification. For your convenience, the codes exist as constants in the system.db namespace. Each type code will be mapped to a database-specific type by the JDBC driver. Not all type codes will be recognized by all JDBC drivers. The following type code constants are available for use in createSProcCall:

BIT	REAL	LONGVARCHAR	LONGVARBINARY	BLOB
TINYINT	DOUBLE	DATE	NULL	CLOB
SMALLINT	NUMERIC	TIME	OTHER	JAVA_OBJECT
INTEGER	DECIMAL	TIMESTAMP	SQLXML	DATALINK
BIGINT	CHAR	BINARY	NCLOB	BOOLEAN
FLOAT	VARCHAR	VARBINARY	ARRAY	ROWID
NCHAR	NVARCHAR	LONGNVARCHAR

The following type code constants are available for other uses, but are not supported by createSProcCall:

ORACLE_CURSOR	DISTINCT	STRUCT	REF

Once the call context has been executed, you can retrieve the result set, return value, and output parameter values (if applicable) by calling the following functions:

SProcCall.getResultSet() - returns a dataset that is the resulting data of the stored procedure, if any.

SProcCall.getUpdateCount() - returns the number of rows modified by the stored procedure, or -1 if not applicable.

SProcCall.getReturnValue() - returns the return value, if registerReturnParam had been called.

SProcCall.getOutParamValue(index OR name) - returns the value of the previously registered out-parameter.

Syntax

system.db. createSProcCall( procedureName, database, tx, skipAudit )

Parameters

String procedureName - The named of the stored procedure to call.

String database - The name of the database connection to execute against. If omitted or "", the project's default database connection will be used.

String tx - A transaction identifier. If omitted, the call will be executed in its own transaction.

boolean skipAudit - A flag which, if set to true, will cause the procedure call to skip the audit system. Useful for some queries that have fields which won't fit into the audit log.

Returns

SProcCall - A stored procedure call context, which can be configured and then used as the argument to system.db.execSProcCall.

Scope

All

Code Examples

Code Snippet

#This example would call a stored procedure named "start_batch" against the current project's default database connection that had no input or output parameters, and did not return any values or results:
 
call = system.db.createSProcCall("start_batch")
system.db.execSProcCall(call)

Code Snippet

#This example would call a stored procedure "get_shift_workers" with no arguments, which returned a result set of employees for the current shift. It then pushes the resulting dataset into a Table component:
 
call = system.db.createSProcCall("get_shift_workers")
system.db.execSProcCall(call)
 
results = call.getResultSet()
table = event.source.parent.getComponent("Table")
table.data = results

Code Snippet

#This example would call a stored procedure that took two arguments, the first an integer and the second a string. It also is configured to return an integer value.
 
call = system.db.createSProcCall("perform_calculation")
call.registerReturnParam(system.db.INTEGER)
call.registerInParam(1, system.db.INTEGER, 42)
call.registerInParam(2, system.db.VARCHAR, "DC-MODE")
 
system.db.execSProcCall(call)
 
#Print the result to the console
print call.getReturnValue()

Code Snippet

#This example would do the same as the one above, except for a stored procedure that returned its value using an out-parameter. It also uses named argument names instead of indexed arguments.
 
call = system.db.createSProcCall("perform_calculation")
call.registerInParam("arg_one", system.db.INTEGER, 42)
call.registerInParam("arg_two", system.db.VARCHAR, "DC-MODE")
call.registerOutParam("output_arg", system.db.INTEGER)
 
system.db.execSProcCall(call)
 
#Print the result to the console
print call.getOutParamValue("output_arg")