Executing a ProcedureCall bean

To access relational data using a ProcedureCall bean, 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 statement associated with the ProcedureCall bean is executed.

Alternatively, you can connect the DBNavigator bean to the ProcedureCall bean. The DBNavigator bean provides a set of buttons that includes an Execute button dbnavex.gif (989 bytes). The DBNavigator bean is a Swing component, and requires the Java Foundation Classes (JFC) library. To use the DBNavigator bean, you create a property-to-property connection between the this property of the ProcedureCall bean and the model property of the DBNavigator bean. The this property refers to the whole object of the ProcedureCall bean. The model property specifies which Select or ProcedureCall bean the DBNavigator bean will navigate. When selected, the Execute button in the DBNavigator bean invokes the execute method of the ProcedureCall bean, which executes the SQL statement.

If you have defined input parameters in your SQL statement, you must set the parameters before you invoke the execute method. Likewise, if you have defined output parameters, you will want to get their values after you invoke the execute method. If you used the SQL Assist SmartGuide to compose the SQL statement, VisualAge for Java generates two bound properties for each (input or output) parameter you defined. One property is the parameter in its specified data type. The other property is a String representation of the parameter. So, for example, you can make a property-to-property connection between the text property of a text field and the String representation of a parameter. Because the generated property is bound, when you make the connection, code is generated to invoke the getParameterFromString method whenever the parameter value changes. This is sufficent for an output parameter. For an input parameter, because the text property is not bound, you must also specify in the connection properties an event to trigger the propagation of the text value to the parameter.  Once you do this, code is generated to invoke the setParameterFromString method whenever the event is fired.

When you execute an SQL statement using a ProcedureCall bean, it may return no result sets, one result set, or many result sets. Unlike the native Java interface to relational data (JDBC), the ProcedureCall bean maintains rows of the
result sets in a memory cache where you can move back and forth both among different result sets and among the
rows of each result set.. The number of result sets and the number of rows in each that are initially fetched when you execute are controlled by the following properties of the ProcedureCall bean:

If fillResultCacheOnExecute is set to false, one result set is initially fetched. If it is set to true, the number of result sets fetched is the lesser of all result sets and the limit imposed by maximum ResultsInCache.

If fillCacheOnExecute is set to false, one packet of rows is initially fetched. If it is set to true, the number of rows fetched for each result set is the least of: all of the rows, the limit imposed by the maximumRows property, the limit imposed by packetSize times maximumPacketsInCache.

(If you use the default values for all of these properties, all of the rows in all of the result sets are fetched.)

If there are any result sets, regardless of how many result sets were initially fetched, you are positioned on the first one; and if the result set has any rows, regardless of how many of its rows were fetched, you are positioned on the first one.

For some database products, it may be necessary for you to fetch all of the result sets returned by a stored procedure before getting the values of any output parameters. The simplest way to accomplish this is to use the default values for the fillResultCacheOnExecute and maximumResultsInCache properties. This way all of your result sets will be immediately fetched into the cache.


Related concepts
About Relational Database Access

Related procedures
Editing ProcedureCall bean properties
Adding the DBNavigator bean to the Visual Composition Editor surface
Connecting Beans

Related references
Data Access Beans