Retrieving data using a stored procedure

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.

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.

Other sample applications referencing stored procedures on a variety of servers are available in the samples/DataExpress/ServerSpecificProcedures sub-directory of your JBuilder installation, 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 you 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 with a JdbNavToolBar" and "Tutorial: saving changes with a ProcedureResolver".

  1. InterBase Server and InterServer should be running on the same machine on which the following project will be run.

  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 samples/DataExpress/SimpleStoredProcedure/ProcedureSetup sub-directory of your JBuilder installation. If you would like to explore the CreateProcedures.java file, see the topic "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 provided you have set up as in "Setting up InterClient").

  6. Select the Run tab. Add -Xverify:none to the Java VM field. Close the dialog.
  7. Right-click CreateProcedures.java in the project pane, 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, add InterClient to the list of required libraries, and set the Java VM parameters. To do this,
    1. Select the Required Libraries tab. Select InterClient (this option will be available if you have set up as in "Setting up InterClient").

    2. Select the Run tab. Add -Xverify:none to the Java VM field. Close the dialog.

  3. Select the Design tab to activate the UI designer.

  4. Place a Database component from the Data Express tab of the component palette on the Structure pane.

  5. Open the connection property editor for the Database component by selecting the connection property ellipsis 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> (for example, jdbc:interbase://localhost//usr/interbase/examples/employee.gdb)
    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.

  6. Place a ProcedureDataSet component from the Data Express tab of the component palette on the Structure pane. In the Inspector, set the procedure property of the ProcedureDataSet component from the Inspector 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".

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

    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 a JdbNavToolBar component from the dbSwing tab. Drop the component in the area slightly above the panel in the Design frame. JdbNavToolBar automatically attaches itself to whichever DataSet has focus.

    This will enable you to move quickly through the data set when the application is running, as well as provide a default mechanism for saving changes back to your data source.

  2. Click on the JdbStatusLabel component on the dbSwing tab of the component palette. Drop the component in the area slightly below the panel in the Design pane. JdbStatusLabel automatically attaches itself to whichever DataSet has focus.

    Among other information, the status bar displays information about the current record or current operation.

  3. Add a TableScrollPane component from the dbSwing tab to the designer.

    Scrolling behavior is not available by default in any Swing component or dbSwing extension, so, to get scrolling behavior, we add the scrollable Swing or dbSwing components to a JScrollPane or a TableScrollPane. TableScrollPane provides special capabilities to JdbTable over JScrollPane. See the dbSwing documentation for more information.

  4. Drop a JdbTable component from the dbSwing tab into the TableScrollPane component. Set its dataSet property to procedureDataSet1.

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

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