ICallableStmt interface (deprecated)
ICallableStmt is deprecated and is provided for backward compatibility only. New applications should use the java.sql.ICallableStatement interface from the JDBC Core API.
The ICallableStmt interface provides a standard way to call stored procedures in any database server. A stored procedure is a block of SQL statements stored in a database. Stored procedures provide centralized code for manipulating data and reduce the amount of data that needs to be sent to the client side of an application. They are typically used to execute database operations, for example, modify, insert, or delete records.
To call a stored procedure from an AppLogic, use the ICallableStmt object. The ICallableStmt interface defines methods for executing a stored procedure or function, and setting and getting parameter values to and from a stored procedure.
To create an instance of the ICallableStmt interface, use prepareCall( ) in the IDataConn interface.
Package
com.kivasoft
Methods
Related Topics
prepareCall( ) in the IDataConn interface (deprecated)
close( )
Releases the callable statement.
Syntax
public int close()
Usage
Use close( ) to release a callable statement object after the AppLogic has finished processing the results returned by the stored procedure. You must release the callable statement object so that the data connection is available to process other commands.
Return Value
GXE.SUCCESS if the method succeeds.
execute( )
Executes the stored procedure called by the ICallableStmt object.
Syntax
public IResultSet execute(
int flags,
IValList params,
ITrans trans,
IValList props);
flags .
params .
IValList object that contains parameters to pass to the callable statement. If you use setParams( ) instead to specify the parameters, specify null here.
trans .
ITrans object that contains the transaction associated with this callable statement, or null for no transaction.
props .
IValList object that contains properties, or null for no properties. This parameter applies only if the callable statement returns a result set. Informix stored procedures, for example, return out parameter values only as a result set. Sybase, DB2, and MS SQL Server stored procedures also support the return of a result set. Multiple result sets, however, is not supported.
After instantiating an object of the IValList interface, set any of the following properties:
If RS_BUFFERING is enabled and if the optional parameters are not specified, the global values in the registry are used instead.
Usage
Use execute( ) to run a callable statement that has been created with prepareCall( ) in the IDataConn interface. If the stored procedure called by the ICallableStmt object can return multiple result sets, use executeMultipleRS( ) instead.
If the stored procedure called by the ICallableStmt object contains parameters, instantiate an IValList object and use setValString( ) or setValInt( ) in the IValList interface to specify the parameter values to pass to the stored procedure.
After creating and setting up the IValList object, pass it to execute( ) or setParams. If you use setParams( ) to pass parameters to the stored procedure, specify NULL for the params parameter in execute( ).
Rule
When accessing a stored procedure on Sybase or MS SQL Server, input parameter names specified in the call must be prefixed with the ampersand (&) character, for example, ¶m1. Other database drivers accept the ampersand, as well, as the colon (:) character. For all database drivers, input/output and output parameter names are prefixed with the colon (:) character, for example, :param2.
Return Value
IResultSet object, or null for failure (such as an invalid parameter).
Example
//Write command to call stored procedure
String theProc = "{call myProc1(&p1, :p2)}";
IQuery myquery;
myquery = createQuery();
myquery.setSQL(theProc);
//Prepare the callable statement for execution
ICallableStmt myStmt;
myStmt = conn_rtest.prepareCall(0, myquery, null, null);
//Set the in parameter value
IValList inParams = GX.CreateValList();
inParams.setValInt("&p1",6210603);
//Set the out parameter value to any integer
inParams.setValInt(":p2", 0);
//Run the callable statement
myStmt.execute(0, inParams, null, null);
//Get the stored procedure's output value
outParams = myStmt.getParams(0);
Related Topics
prepareCall( ) in the IDataConn interface (deprecated)
getParams( )
setParams( )
executeMultipleRS( )
Executes a stored procedure, called by the ICallableStmt object, that can return multiple result sets.
Syntax
public int executeMultipleRS(
int dwFlags,
IValList pParams,
ITrans pTrans,
IValList pProps);
dwFlags.
Specify 0.
pParams.
IValList object that contains parameters to pass to the callable statement. If no parameters are required, pass in an empty IValList. If you use setParams( ) instead to specify the parameters, specify null here.
pTrans.
ITrans object that contains the transaction associated with this callable statement, or null for no transaction.
pProps.
IValList object that contains properties, or null for no properties.
After instantiating an object of the IValList interface, set any of the following properties:
If RS_BUFFERING is enabled and if the optional parameters are not specified, the global values in the registry are used instead.
Usage
Use executeMultipleRS( ) to run a callable statement that returns multiple result sets. The callable statement should already have been created with prepareCall( ) in the IDataConn interface.
If the stored procedure called by the ICallableStmt object contains parameters, instantiate an IValList object and use setValString( ) or setValInt( ) in the IValList interface to specify the parameter values to pass to the stored procedure.
After creating and setting up the IValList object, pass it to executeMultipleRS( ) or setParams. If you use setParams( ) to pass parameters to the stored procedure, specify NULL for the pParams parameter in executeMultipleRS( ).
Rule
When accessing a stored procedure on Sybase or MS SQL Server, input parameter names specified in the call must be prefixed with the ampersand (&) character, for example, ¶m1. Other database drivers accept the ampersand, as well, as the colon (:) character. For all database drivers, input/output and output parameter names are prefixed with the colon (:) character, for example, :param2.
Tip
The difference between execute( ) and executeMultipleRS( ) is that execute( ) can return only a single result set. If you're not sure how many results sets, if any, a stored procedure returns, use executeMultipleRS( ).
Return Value
GXE.SUCCESS if the method succeeds.
Example
//Write command to call stored procedure
String proccall = "{call multiRSproc()}";
IQuery qryobj = GX.createQuery();
qryobj.setSQL(proccall);
//Prepare the callable statement for execution
ICallableStmt stmt = connobj.PrepareCall(0, qryobj, null, null);
//Create the IValList to pass to the stored procedure
IValList inparms = GX.CreateValList();
//Run the callable statement
stmt.executeMultipleRS(0, inparms, null, null);
//Check if there is a result set
if (stmt.getMoreResults())
{
//Get the result set
IResultSet rsobj = stmt.getResultSet();
while (rsobj.fetchNext()) {
if (rsobj.getValueInt(1) == 100) {
System.out.println("Found record 100");
}
}
}
//Check if there is another result set
if (stmt.getMoreResults()) {
rsobj = stmt.getResultSet();
while (rsobj.fetchNext()) {
if (rsobj.getValueString(2) == 'George') {
System.out.println("Found record George");
}
}
}
Related Topics
prepareCall( ) in the IDataConn interface (deprecated)
getMoreResults( )
getResultSet( )
getMoreResults( )
Checks if there is a result set to retrieve. This method is valid only if you used executeMultipleRS( ) (instead of execute( )) to execute a stored procedure called by the ICallableStmt object.
Syntax
public boolean getMoreResults()
Usage
If you used executeMultipleRS( ) to execute a stored procedure that returns multiple results sets, use getMoreResults( ) in conjunction with getResultSet( ) to check if there is a result set before retrieving it.
If there is a current result set with unretrieved rows, getMoreResults( ) discards the current result set and makes the next result set available.
Return Value
True if there is a result set.
Example
//Run the callable statement
stmt.executeMultipleRS(0, inparms, null, null);
//Check if there is a result set
if (stmt.getMoreResults())
{
//Get the result set
IResultSet rsobj = stmt.getResultSet();
while (rsobj.fetchNext()) {
if (rsobj.getValueInt(1) == 100) {
System.out.println("Found record 100");
}
}
}
//Check if there is another result set
if (stmt.getMoreResults()) {
rsobj = stmt.getResultSet();
while (rsobj.fetchNext()) {
if (rsobj.getValueString(2) == 'George') {
System.out.println("Found record George");
}
}
}
Related Topics
prepareCall( ) in the IDataConn interface (deprecated)
executeMultipleRS( )
getResultSet( )
getParams( )
Returns the value of the stored procedure's output parameter or parameters.
Syntax
public IValList getParams(
int dwFlags);
dwFlags.
Specify 0 (zero).
Usage
Some stored procedures return output parameters. If the stored procedure your callable statement executes returns output parameters, use getParams( ) to get the values.
The getParams( ) method returns the values in an IValList object. The key names associated with the values are the parameter names as specified in the query that was passed to the prepareCall( ) method
Tip
Informix stored procedures return output parameters in a result set. This result set is returned by execute( ) or executeMultipleRS( ). The getParams( ) method, therefore, does not apply to Informix stored procedures.
Return Value
IValList object, or null for failure.
Related Topics
prepareCall( ) in the IDataConn interface (deprecated)
execute( )
setParams( )
getResultSet( )
Retrieves a result set. This method is valid only if you used executeMultipleRS( ) (instead of execute( )) to execute a stored procedure called by the ICallableStmt object.
Syntax
public IResultSet getResultSet()
Usage
If you used executeMultipleRS( ) to execute a stored procedure that returns multiple results sets, use getResultSet( ) in conjunction with getMoreResults( ) to retrieve the results sets.
Return Value
IResultSet object, or null if there is no result set.
Example
//Run the callable statement
stmt.executeMultipleRS(0, inparms, null, null);
//Check if there is a result set
if (stmt.getMoreResults())
{
//Get the result set
IResultSet rsobj = stmt.getResultSet();
while (rsobj.fetchNext()) {
if (rsobj.getValueInt(1) == 100) {
System.out.println("Found record 100");
}
}
}
//Check if there is another result set
if (stmt.getMoreResults()) {
rsobj = stmt.getResultSet();
while (rsobj.fetchNext()) {
if (rsobj.getValueString(2) == 'George') {
System.out.println("Found record George");
}
}
}
Related Topics
prepareCall( ) in the IDataConn interface (deprecated)
executeMultipleRS( )
getMoreResults( )
setParams( )
Specifies the parameter values to pass to the stored procedure.
Syntax
public int setParams(
int dwFlags,
IValList pParams);
dwFlags.
Specify 0 (zero). For internal use only.
pParams.
IValList object that contains the parameters to pass to the stored procedure. You must set all parameters required by the stored procedure. If you don't, a runtime error will occur when execute( ) is called. If you use setParams( ), specify NULL for the pParams parameter in execute( ).
Usage
If the stored procedure the callable statement executes accepts input parameters, use setParams( ) to pass the parameter or parameter values. The alternative is to pass the parameter values with the execute( ) method. Parameters passed to execute( ) supersede parameters specified with setParams( ).
For both setParams( ) and execute( ), you pass the parameter values in an IValList object.
Return Value
GXE.SUCCESS if the method succeeds.
Related Topics
prepareCall( ) in the IDataConn interface (deprecated)
execute( )
getParams( )
|
Previous
