system.db.createSProcCall

This function is used in Python Scripting.

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. Note that not all JDBC drivers support named procedure parameters. If your function returns a value, you must use registerReturnParam to specify the data type of the returned value. Also be aware 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 of "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
TINYINT	DOUBLE	DATE	NULL
SMALLINT	NUMERIC	TIME	ROWID
INTEGER	DECIMAL	TIMESTAMP	CLOB
BIGINT	CHAR	BINARY	NCLOB
FLOAT	VARCHAR	VARBINARY	BLOB
NCHAR	NVARCHAR	LONGNVARCHAR	BOOLEAN

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

ORACLE_CURSOR	DISTINCT	STRUCT	REF
JAVA_OBJECT	SQLXML	ARRAY	DATALINK
OTHER

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.

note

When using a PostgreSQL database, this function can only make use of Postgres Functions and not Stored Procedures. Postgres Stored Procedures must be run using system.db.runPrepUpdate.

Client Permission Restrictions

This scripting function has no Client Permission restrictions.

Syntax - Gateway

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

Parameters

Type	Parameter	Description
String	procedureName	The named of the stored procedure to call.
String	database	The name of the database connection to execute against.
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

Gateway

Syntax - Vision and Perspective

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

Parameters

Type	Parameter	Description
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. [optional]
String	tx	A transaction identifier. If omitted, the call will be executed in its own transaction. [optional]
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. [optional]

Returns

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

Scope

Vision Client, Perspective Session

Code Examples

Code Snippet - Creating Stored Procedure Call

# This example calls 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 - Creating Stored Procedure Call

# 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 - Creating Stored Procedure Call With Stored Procedure Parameters

# 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 - Creating Stored Procedure Call With Stored Procedure Parameters

# 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")