Stored Procedures

Introduction

This section explains how to use the wizard to generate code for database stored procedures. 

  • Most, but not all databases support stored procedures. 
  • Different database manufacturers have different implementations of stored procedures.

The JDatabase Wizard exposes stored procedures in terms of what the JDBC driver exposes. 

In some circumstances the JDBC driver is unable to provide all the necessary information (namely ResultSet columns which are displayed with a little red marker). In this case, the wizard provides facilities for the user of the wizard to enter the information the wizard needs to call the stored procedure correctly.


Stored Procedure View

When a stored procedure node is selected in the tree, a stored procedure view tab appears in the right pane of the wizard. Below is an example stored procedure view tab.

The vast majority of users will not need to change any fields on this tab.. 

We have come across one JDBC database driver which reports database stored procedures with one name, but expects another name to be used in SQL.

The call name is used to name the stored procedure when part of the SQL is generated. For example, with the information displayed in the image above, the wizard would generate the call as follows:

{ ? = CALL  spGetCustomerByID ( ? )  }

Note that the ;1 does not appear in the call.


Stored Procedure Entries

JDBC describes a stored procedure as follows:

The Wizard supports all but one of these features: as far as the Wizard is concerned, a stored procedure can return zero or one ResultSet. 

The ResultSet's columns must be specified using the Wizard, therefore the columns the ResultSet contains must be known before the stored procedure is called.

Return Value

A stored procedure can sometimes return a value. When it does return a value, the Wizard adds a node to the "Return Value" node:

Below is a snapshot of the Return View panel associated with a given stored procedure return value. The values in this panel should not usually need to be changed.

Parameters

A stored procedure can use parameters. When a stored procedure does use parameters, a node is added to the tree. 

The icon displayed shows whether the parameter is Input, Input & Output, Output, or unused

Below is an snapshot of the parameter view panel associated a given stored procedure parameter. The values in this panel should not usually need to be changed.

Resultset Columns

Some databases allow JDBC to retrieve ResultSet columns from a procedure call. The Java code to achieve this can be coded as follows:

myCall = theConnection.prepareCall("{ CALL MeSchema.GetAllCustomers }");
myResultSet = myCall.executeQuery();
while (myResultSet.next())
{
  System.out.println(myResultSet.getString(1) + " " + myResultSet.getString(2));
}

Some JDBC drivers are unable to list the ResultSet columns.

The JDatabase Wizard allows columns to be defined by the user, should the JDBC driver not enumerate them.

Creating Columns

In order to create a ResultSet column in the project file, the "ResultSet Columns" tree node should be selected. Note that the columns have a little red marker. This is because the Wizard has not found these columns via the JDBC driver.

The following panel appears in the right side of the Wizard:

The procedure columns should be created in the same order that the stored procedure returns them in the ResultSet. The Wizard's ordering of the columns can be changed later, but it is easier if they are entered correctly from the start.

Editing Columns

In order to change a ResultSet's column, the column should be selected from the tree.

When a column is selected in the tree, the right side of the Wizard's window displays the corresponding panel.


Classes

When mapping a database stored procedure to java classes, it is necessary to create classes in the wizard. The wizard generates one or two classes for a procedure object. 

The "JDBC class", contains the JDBC code to access the database, procedure parameters as properties of this class and the stored procedure's return value as a property of this class.

The other, "data class", is only generated, if there is at least one ResultSet column defined. See the ResultSet columns section to learn more about this.

Once a class is created in the wizard, we can further specify the database object's parameters, return value and columns we want the classes to use. We can also specify the JDBC class's method name which calls the stored procedure.

There now follows a description of how the wizard is used to create a class.

JDBC Classes View

The JDBC classes view allows a new JDBC class description to be created in the project file. There are 2 types of classes: JDBC1 classes and JDBC2 classes.

The database class will contain the code to select, insert, update and delete database data.

The data class will hold the data which has been retrieved from the database (select), or the data to use when inserting/updating the database.

If the Serializable data class checkbox is checked, the generated data class implements java.io.Serializable.


Class Info Tab

Creating the JDBC1 class brings up a tabbed pane, whose first tab is illustrated below.

The class information tab allows us to change general class information.


Database Class Tab

The Database class generated by the wizard contains all the code necessary to call the stored procedure. It can also contain properties which relate to the stored procedure's parameters and return value.

The Database Class tab allows the stored procedure parameter and return value properties of the Database class to be edited.

Double clicking on a row in the table pops up a JDBC Class property editor.

JDBC Class Property Editor

The advanced tab is shown below

If a parameter is Input only, the above tab is enabled. Using this tab it is possible to use "hard coded" sql snippets instead of a JDBC parameter.

The server expression should not contain a question mark (?) charecter.


Data Class Tab

The JDatabase Wizard generates a data class if the stored procedure returns a ResultSet (see ResultSet Columns). The data class contains one property per ResultSet column used.

The Data Class tab shows which properties may be present in the generated data class.

When one of the rows is clicked, a Data Class property editor dialog pops up.

Data Class Property Editor


JDBC Method Tab

The JDBC method tab shows the method that will be generated in the JDBC class. It is this method which must be called to invoke the stored procedure in the database.

Any server side expressions are visible, if entered.

A different method name can be entered, should the Wizard's default name be inappropriate.

The save button should be pressed if the method name, or javadoc, has been changed.


Advanced Settings Tab

The advanced settings tab provides additional control over the generated classes. The wizard generates two classes.

For the data class the following settings are available:

For the database class, the following settings are available:


JDBC2 Settings

For JDBC2 stored procedure classes, an additional tab allows control of any resultset returned.

All of our test databases only allow read operations on returned resultsets. The wizard supplies all options supported with JDBC 2, please be aware of your JDBC driver's limitations when setting the combo boxes here.


Using The Generated Code

The following link explains how to use the JDBC1 code generated by the wizard.

The following link explains how to use the JDBC2 code generated by the wizard.


Copyright © 2002 J3 Limited, all rights reserved.