Using stored procedures

Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.

With a stored procedure, one or more SQL statements are encapsulated in a single location on your server and can be run as a batch. ProcedureDataSet components enable you to access, or provide, data from your database with existing stored procedures, invoking them with either JDBC procedure escape sequences or server-specific syntax for procedure calls. To run a stored procedure against a SQL table where the output is a set of rows, you need the following components. You can provide this information programmatically, or by using JBuilder design tools.

When providing data from JDBC data sources, the ProcedureDataSet has built-in functionality to fetch data from a stored procedure that returns a cursor to a result set. The following properties of the ProcedureDescriptor object affect the execution of stored procedures:
PropertyPurpose

database Specifies what Database connection object to run the procedure against.
procedure A Java String representation of a stored procedure escape sequence or SQL statement that causes a stored procedure to be executed.
parameters An optional ReadWriteRow from which to fill in parameters. These values can be acquired from any DataSet or ReadWriteRow.
executeOnOpen Causes the ProcedureDataSet to execute the procedure when it is first opened. This is useful for presenting live data at design time. You may also want this enabled at run time. The default value is true.
loadOption An optional integer value that defines the method of loading data into the data set. Options are:
  1. Load All Rows: load all data up front.
  2. Load Rows Asynchronously: causes the fetching of DataSet rows to be performed on a separate thread. This allows the DataSet data to be accessed and displayed as the QueryDataSet is fetching rows from the database connection.

  3. Load As Needed: load the rows as they are needed.

  4. Load 1 Row At A Time: load as needed and replace the previous row with the current. Useful for high-volume batch-processing applications.

A ProcedureDataSet can be used to run stored procedures with and without parameters. A stored procedure with parameters can acquire the values for its parameters from any DataSet or ParameterRow. The section "Example: using parameters with Oracle PL/SQL stored procedures" provides an example.

Use JDBC Explorer to browse and edit database server-specific schema objects, including tables, fields, stored procedure definitions, triggers, and indexes. For more information on JDBC Explorer, select Tools|JDBC Explorer and refer to its online help.

The following topics related to stored procedure components are covered:


Tutorial: Retrieving data using stored procedures

This tutorial shows how to provide data to an application using JBuilder's UI designer and a ProcedureDataSet component. This example also demonstrates how to attach the resulting data set to a JdbTable and a JdbNavToolBar for data viewing and editing.

The finished example for this tutorial may be available as a completed project in the /samples/DataExpress/SimpleStoredProcedure directory of your JBuilder installation. Other sample applications referencing stored procedures on a variety of servers are available in the /samples/DataExpress/ServerSpecificProcedures directory, and a sample of providers is available in the /samples/DataExpress/CustomProviderResolver directory.

Creating tables and procedures for the tutorial

These steps run a stored procedure that creates a table and insert, update, and delete procedures on the InterBase server (make sure have followed the setup instructions in "Setting up InterClient and InterBase for the samples"). This procedure is written in the InterBase language. These procedures will be used both in this section and in the "Tutorial: Saving changes using a QueryResolver" and "Tutorial: Saving changes with a ProcedureResolver".

  1. InterBase Server and InterServer should be running on the same machine, unless it has been turned off.

  2. Select File|Close All from the menu to close existing projects.

  3. Select File|Open and open the project ProcedureSetUp.jpr, which is located in the /jbuilder/samples/DataExpress/SimpleStoredProcedure/ProcedureSetup directory of your JBuilder installation. If the project is not available or if you would like to explore the CreateProcedures.java file, see the section "Creating tables and procedures for the tutorial manually" at the end of this topic.
  4. Select Project|Properties.

  5. Select the Required Libraries tab. Select InterClient. This option will be available if you have set it up as in "Adding a JDBC driver to JBuilder".

  6. Double-click CreateProcedures.java in the project pane and edit the path to the InterBase employee.gdb file to be that on your computer. (Use foward slashes in the path.)

  7. Save the file, then right-click CreateProcedures.java in the project pane, and select Run. This step creates the tables and procedures on the server.

  8. Select Tools|JDBC Explorer to verify that the tables and procedures are created.
  9. Select File|Close Project from the menu.

Adding the DataSet components

To create this application and populate a data set using the stored procedure,

  1. Select File|New and double-click the Application icon. Accept all defaults, or modify the path and project name to make them more descriptive.

  2. Select Project Properties, and click the Required Libraries tab.

  3. Add InterClient. This option will be available if you have set it up as in "Adding a JDBC driver to JBuilder".

  4. Close the dialog.

  5. Select the Design tab to activate the UI designer

  6. Select the Database component on the Data Express tab of the component palette, and click anywhere in the component tree.

  7. Open the connection property editor for the Database component by selecting the connection property ellipsis button in the Inspector. Set the connection properties to the InterBase sample tables by setting the properties as indicated in the following table. These steps assume you have completed "Setting up InterClient and InterBase for the samples".
    Property name Value
    Driver interbase.interclient.Driver
    URL jdbc:interbase://<IP address or localhost>/<path to .gdb file>
    Username SYSDBA
    Password masterkey

    The connection dialog includes a Test Connection button. Click this button to check that the connection properties have been correctly set. Results of the connection attempt are displayed beside the button. When the text indicates Success, click OK to close the dialog.

    The code generated by the designer for this step can be viewed by selecting the Source tab and looking for the ConnectionDescriptor. Select the Design tab to continue.

  8. Place a ProcedureDataSet component from the Data Express tab of the component palette on the content pane. Set the procedure property of the ProcedureDataSet as follows:

    Property name    Value
    Database    database1
    Stored Procedure Escape or SQL Statement    SELECT * FROM GET_COUNTRIES

    Several procedures were created when CreateProcedures.java was run. The procedure GET_COUNTRIES is the one that will return a result set. The SELECT statement is how a procedure is called in the InterBase language. The other procedures will be used for resolving data in the topic " Tutorial: Saving changes with a ProcedureResolver".

    Tip: You can use the Browse Procedures button in future projects to learn what stored procedures are available. See "Discussion of stored procedure escape sequences, SQL statements, and server-specific procedure calls" for more information.

    Click Test Procedure to ensure that the procedure is runnable. When the gray area beneath the button indicates Success as shown below, click OK to close the dialog.

    ProcedureDescriptor

    The code generated by this step is can be viewed by selecting the Source tab and looking for setProcedure. Click the Design tab to continue.

Adding visual components

This topic shows how to create a UI for your application using dbSwing components.

  1. Select contentPane(BorderLayout) in the component tree.

  2. Click on the JdbNavToolBar component on the dbSwing tab of the component palette, and drop the component in the area at the top of the panel in the UI designer. Set its constraints property to NORTH. jdbNavToolBar1 automatically attaches itself to whichever DataSet has focus.

  3. Drop a JdbStatusLabel in the area at the bottom of the panel in the UI designer and set its constraints property to SOUTH. jdbStatusLabel1 automatically attaches itself to whichever DataSet has focus.

  4. Drop a TableScrollPane component from the dbSwing tab to the center of the panel in the UI designer. Make sure its constraints property is set to CENTER.
  5. Select tableScrollPane1 and drop a JdbTable into the center of it. Set its dataSet property to procedureDataSet1.

  6. Select Run|Run Project to run the application and browse the data set.

The running application looks like this:

ProcedureDataSet example

By default, the Save Changes button on the toolbar will save changes using a default QueryResolver. To customize the resolving capability in your application in order to edit, insert, and delete data in the running application, see