Skip to end of metadata
Go to start of metadata

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 nametypeCodevalue) 

SPRocCall.registerOutParam(index OR nametypeCode) 

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: 

 

BITREALLONGVARCHARLONGVARBINARY

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_CURSORDISTINCTSTRUCTREF
JAVA_OBJECTSQLXMLARRAYDATALINK
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.

Client Permission Restrictions

This scripting function has no Client Permission restrictions.


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")
  • No labels