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.
Database
component encapsulates a database connection through JDBC to the SQL server and also provides lightweight transaction support.The ProcedureDataSet
component provides the functionality to run the stored procedure (with or without parameters) against the SQL database and stores the results from the execution of the stored procedure.
The ProcedureDescriptor
object stores the stored procedure properties, including the database to be queried, the stored procedures, escape sequences, or procedure calls to execute, and any optional stored procedure parameters.
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:
Property | Purpose |
---|---|
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:
|
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:
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.
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".
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.Select Project|Properties.
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".
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.)
CreateProcedures.java
in the project pane, and select Run. This step creates the tables and procedures on the server.
Select File|Close Project from the menu.
To create this application and populate a data set using the stored procedure,
Select File|New and double-click the Application icon. Accept all defaults, or modify the path and project name to make them more descriptive.
Select Project Properties, and click the Required Libraries tab.
Add InterClient. This option will be available if you have set it up as in "Adding a JDBC driver to JBuilder".
Close the dialog.
Select the Design tab to activate the UI designer
Select the Database
component on the Data Express tab of the component palette, and click anywhere in the component tree.
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.
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".
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.
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.
This topic shows how to create a UI for your application using dbSwing components.
Select contentPane(BorderLayout)
in the component tree.
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.
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.
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.
Select tableScrollPane1
and drop a JdbTable
into the center of it. Set its dataSet
property to procedureDataSet1
.
Select Run|Run Project to run the application and browse the data set.
The running application looks like this:
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