ProcedureCall Bean

The ProcedureCall bean is a non-visual bean. Using the ProcedureCall bean, you can run a stored procedure. You can pass parameters as input, output or both and you can access any result sets the stored procedure returns. You can also use any result sets that are returned to insert, update, or delete rows in the database.

The ProcedureCall bean has a procedure property which specifies how to connect to a database and defines an SQL statement. All of the properties you can specify for a Select bean can also be specified for a ProcedureCall bean. In addition, the ProcedureCall bean has properties that govern the handling of any result sets returned by a stored procedure.

The ProcedureCall bean provides a set of methods  for running stored procedures and obtaining output from them. For example, the execute method executes the SQL CALL statement. There are methods for setting input parameter values and getting output parameter values. To run a stored procedure, you connect an interface component to the ProcedureCall bean. For example, you can make an event-to-method connection between the actionPerformed event for a button and the execute method of the ProcedureCall bean. When the button is selected, the SQL CALL statement specified in the procedure property executes.

When you run a stored procedure using the ProcedureCall bean, it may return zero, one, or multiple result sets. Unlike the native Java interface to relational data (JDBC), the ProcedureCall bean maintains result sets in a memory cache where you can move back and forth between the result sets and the rows within the result sets. You can control the number of result sets in the cache at one time and the number of rows within each result set at one time or over time by setting ProcedureCall properties that control memory management.  

Once you have used a Procedure Call bean to run a stored procedure, if it returned one or more result sets, you are always positioned on a result set, referred to as the "current result set". If the current result set has one or more rows, you are also positioned on a row, referred to as the "current row". Many of the Procedure Call bean properties operate on the current row of the current result set. Initially the current result set is the first one returned, and the current row is its first row. The Procedure Call bean includes methods to move to a different row or a different result set. For example, there are methods that move to the next row, the previous row, the next result set, and the previous result set.

Values for parameters used as input to or output from the stored procedure can be accessed via an interface component such as a text field using the bound parameter properties of the Procedure Call bean that Visual Age for Java generates.

If the stored procedure returns one or more result sets, you can display data from the current result set by making a property-to property connection between the appropriate source property of the ProcedureCall bean and the appropriate target property of an interface component such as a JTable or a text field. To display result set data in tabular form make a property-to-property connection between the this property of the ProcedureCall bean and the model property of the JTable. As you move from one result set to another, the table will change accordingly.

You can also display result set data one row at a time. If the stored procedure returns only one result set or all returned result sets have similar columns and you use the SQL Assist SmartGuide to describe a result set, Visual Age for Java will generate two bound properties for each data column in the result set. One property is the data column in its specified data type, the other is a string representation of the data column. Make a property-to-property connection between the string representation of each data column in the result set and the text property of a text field, and the text field displays the column values of the current row of the current result set.

Inserting, updating and deleting data

The ProcedureCall bean provides methods that you can use to insert, update, and delete relational data. To perform these operations, the ProcedureCall bean must return one or more result sets and be positioned to the desired result set and the desired row. Any changes made to rows in the cache are applied to the database as well:

There are various ways to use the methods for inserting, updating and deleting data. For example, one way to implement updates in a program is to make an event-to-code connection between an appropriate interface component, such as a button, and a method. This method would obtain the new value from an interface component such as a text field and then use a ProcedureCall bean method to set the value of the column in the current row of the current result set to this new value. The method would then use another ProcedureCall bean method to update the row in the database with these values.

Memory Management

By setting expert properties of the ProcedureCall bean, you can control how many result sets are in the memory cache at one time, and for each result set, how many rows are in the memory cache at one time or over time.

The properties for result sets determine:

The properties for rows determine:

The maximum number of rows that will be in the cache at one time is the least of:

If memory management is not critical to your program, you need not be concerned with these properties. By default, the cache size is not limited; rows are fetched one at a time (a packet size of 1); and all of the rows of all result sets are fetched into the cache as soon as you run your store procedure.

Only the last result set fetched remains open to fetch additional rows. Therefore, if you fetch all of the result sets as soon as the stored procedure runs, you probably want to fetch all of the rows for each result set at that time. Otherwise, there may be rows in result sets, other than the last one, which cannot be fetched.


Connection Aliases and SQL Specifications
Parameterized SQL Statements
Select Bean
Modify Bean
Selector Beans
Navigator Bean


Accessing Relational Data