Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.
You can use different Resolver implementations to save changes back to your data source. QueryDataSets
use a QueryResolver
to save changes by default. The default resolver can be overridden by setting the StorageDataSet.resolver
property.
This topic explores the basic resolver functionality provided by the DataExpress package for ProcedureDataSet
components. It extends the concepts explored in "Obtaining data through a stored procedure" by exploring the different methods for saving data changes back to a data source.
The topic "Tutorial: retrieving data using stored procedures" explores how to run a stored procedure in order to retrieve data. The tutorial
creates a table on the server, and then creates insert, update, and delete procedures that will provide information on how to resolve changes back to the source. Using JBuilder's IDE, the tutorial instantiates a ProcedureDataSet
component and associated UI components, and displays the data returned from the JDataStore in a table. The Save button on the JdbNavToolBar
can be used to save data changes back to the employee file when certain properties have been set.
In this topic, the retrieving tutorial is expanded by adding basic resolving capability. With a ProcedureDataSet
component, this can be accomplished in two ways. The following sections discuss each option in more detail.
JdbNavToolBar
whose Save button also performs a basic query resolve function. See "Tutorial: saving changes using a QueryResolver".
ProcedureResolver
that requires special coding of the stored procedure on the database on which the data should be resolved. An example of this is available in "Tutorial: Saving changes with a ProcedureResolver".
If the resolver
property of a ProcedureDataSet
is not set, the default resolver is a QueryResolver
that will generate INSERT, UPDATE, and DELETE queries to save the changes. The QueryResolver
requires tableName
and rowID
properties to be set. This tutorial shows how.
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.
To complete the application and save changes back to the COUNTRY table,
At this point in the tutorial, you can run the application and view and navigate data. In order to successfully insert, delete, or update records, however, you need to provide more information to the QueryResolver
, as follows. The QueryResolver
is invoked by default unless a ProcedureResolver
is defined (see "Tutorial: saving changes with a ProcedureResolver"). Then proceed with the following steps:
Select Frame1.java in the project pane. Select the Design tab to activate the UI designer.
Select procedureDataSet1
in the component tree.
Set the tableName
property of procedureDataSet1
to "COUNTRY" in the Inspector.
resolvable
property of the procedureDataSet1
is set to to True
.Click the expand icon to the left of procedureDataSet1
in the project pane to expose the columns of the data set.
Select the key column, which is named COUNTRY.
Set the rowID
property of the COUNTRY column to True
.
Select Run|Run Project to run the application.
The application compiles and displays in a separate window. Data is displayed in a table with the toolbar and a status label that reports the current row position and row count. You can now insert, update, or delete records and save the changes back to your database.
When you run the application, notice the following behavior:
You can resize the window to display more fields, or scroll using the horizontal scroll bar.
In the above example, you could add a JButton
coded to handle saving changes in place of the JdbNavToolBar
. For more detailed information about how to do this, see "Tutorial: adding a button to save changes from a QueryDataSet". With the button control selected in the component tree, select the Event tab of the Inspector, select the actionPerformed()
method, double-click its value field, and add the following code in the Source window:
try { database1.saveChanges(procedureDataSet1); System.out.println("Save changes succeeded"); } catch (Exception ex) { // displays the exception on the JdbStatusLabel if // the application includes one, // or displays an error dialog if there isn't DBExceptionHandler.handleException(ex); }If you've used different names for the instances of the objects, for example,
database1
, replace them accordingly.
To use a ProcedureResolver, you need to implement three stored procedures on the database, and specify them as properties of the ProcedureResolver. The three procedures are:
insertProcedure
is invoked for every row to be inserted in the DataSet
.
The available parameters for an invocation of an insertProcedure
are:
DataSet
.
ParameterRow
specified in the ProcedureDescriptor
.
The stored procedure should be designed to insert a record in the appropriate
table(s) given the data of that row. The ParameterRow
may be used for output
summaries or for optional input parameters.
updateProcedure
is invoked for every row changed in the DataSet
.
The available parameters for an invocation of an updateProcedure
are:
DataSet
.
DataSet
.
ParameterRow
specified in the ProcedureDescriptor
.
The stored procedure should be designed to update a record in the appropriate table(s) given the original data and the modified data. Since the original row and the modified row have the same column names, the named parameter syntax has been expanded with a way to indicate the designated data row. The named parameter ":ORIGINAL.CUST_ID" thus indicates the CUST_ID of the original data row, where ":CURRENT.CUST_ID" indicates the CUST_ID of the modified data row. Similarly, a ":parameter.CUST_ID" parameter would indicate the CUST_ID field in a ParameterRow
.
deleteProcedure
is invoked for every row deleted from the DataSet
.
The available parameters for an invocation of a deleteProcedure
are:
DataSet
.
ParameterRow
specified in the ProcedureDescriptor
.
The stored procedure should be designed to delete a record in the appropriate table(s) given the original data of that row.
A example of code that uses this method of resolving data to a database follows in "Tutorial: Saving changes with a ProcedureResolver". In the case of InterBase, also see "Example: Using InterBase stored procedures with return parameters".
The following tutorial shows how to save changes to your database using JBuilder's UI designer, a ProcedureDataSet
component, and a ProcedureResolver
. Some sample applications referencing stored procedures on a variety of servers are available in the /samples/DataExpress/ServerSpecificProcedures directory.
To complete the application and save changes back to the COUNTRY table with custom defined insert, update, and delete procedures, first, open the project file you created for "Tutorial: retrieving data using stored procedures". Resolving capability will be added to the existing project.
The current project contains a JdbNavToolBar
component. In addition to enabling you to move around the table, a toolbar provides a Save Changes button. At this point, this button will use a QueryResolver. Once we provide a custom resolver via a ProcedureResolver
, the Save Changes button will call the insert, update, and delete procedures specified there instead
At this point in the application, you can run the application and have the ability to view and navigate data. In order to successfully insert, delete, or update records, however, you need to provide the following information on how to handle these processes. With the project open,
Select a ProcedureResolver
component from the DataExpress tab of the component palette on the content pane. Click in the content pane to add the component to the application.
Set the database
property of the ProcedureResolver
to the instantiated database, database1
in the Inspector.
Set the deleteProcedure
property to DELETE_COUNTRY as follows:
procedureResolver1
in the component tree and click its deleteProcedure
property in the Inspector.
Double-click in the deleteProcedure
property value field to bring up the DeleteProcedure
dialog.
Set the Database
property to database1
.
The following statement is written in the Stored Procedure Escape or SQL Statement field:
execute procedure DELETE_COUNTRY :OLD_COUNTRY
execute procedure DELETE_COUNTRY :COUNTRY
See the text of the procedure in ""Creating tables and procedures for the tutorial manually" or by using the JDBC Explorer (Tools|JDBC Explorer).
insertProcedure
property to INSERT_COUNTRY as follows:
insertProcedure
property of the ProcedureResolver
to open the insertProcedure
dialog.
Set the Database
field to database1
.
Click Browse Procedures, then double-click the procedure named INSERT_COUNTRY.
Edit the generated code to read:
execute procedure INSERT_COUNTRY :COUNTRY, :CURRENCY
Set the updateProcedure
property to UPDATE_COUNTRY as follows:
updateProcedure
property of the ProcedureResolver
to open the updateProcedure
dialog.
Set the Database
property to database1
.
Click Browse Procedures, then double-click the procedure named UPDATE_COUNTRY.
Edit the generated code to read:
execute procedure UPDATE_COUNTRY :ORIGINAL.COUNTRY, :CURRENT.COUNTRY, :CURRENT.CURRENCY
procedureDataSet1
in the project pane. Set the resolver
property to procedureResolver1
.
Select procedureDataSet1. Set its metaDataUpdate property to None
.
Select Run|Run Project to run the application.
When you run the application, you can browse, edit, insert, and delete data in the table. Save any change you make with the Save Changes button on the toolbar. Note that in this particular example, you cannot delete an existing value in the COUNTRY column because referential integrity has been established. To test the DELETE procedure, add a new value to the COUNTRY column and then delete it.
An InterBase stored procedure that returns values is called differently by different drivers. The list below shows the syntax for different drivers for the following function :
CREATE PROCEDURE fct (x SMALLINT) RETURNS (y SMALLINT) AS BEGIN y=2*x; END
Calling fct procedure from different drivers:
execute procedure fct ?
If the procedure is called through a straight JDBC driver, the output is captured in a result set with one row. JBuilder allows the following syntax to handle output values:
execute procedure fct ? returning_values ?
JBuilder will then capture the result set and set the value into the parameter supplied for the second parameter marker.
{call fct(?,?)}
where the parameter markers should be placed at the end of the input parameters.