This sample demonstrates the use of DataExpress' ProcedureDataSet and ProcedureProvider components to utilize stored procedures on an SQL server.
By default, this sample attempts to connect an InterBase database accessed via the InterClient JDBC driver. To run this sample against another database, you will need to modify the StoredProcedureDM.java file and specify the parameters appropriate for your database. You will also need to create an SQL script with commands appropriate for your database to create the sample tables and define stored procedures. Sample configurations for using this sample with Oracle and Sybase databases are included in StoredProcedureDM.java. The corresponding sample SQL scripts are named sqlOracle.sql and sqlSybase.sql, respectively. Each SQL script creates two new tables in the configured database and populates them with some sample data. The relation modeled is that of a master-detail (one-to-many) relationship, with each row of the SAMPLEDEPT table (departments) having zero or more detail rows in the SAMPLEEMP table (employees). The SQL script also defines stored procedures for retrieving, inserting, updating, and deleting rows in the detail table.
Note: Before running the sample, make sure that the parameters for your database in StoredProcedureDM.java are correct, the proper JDBC driver has been added as a library to your project, and that your database server is running and accessible.
Select Run | Run project... to run the sample. A frame appears with a toolbar, status bar, and two empty tables for displaying the master and detail table data. If this is the first time you are running the sample, press the Create button on the toolbar to create the sample tables and stored procedures in the database. Press the Connect button to bind the tables to the data in the database. The top table is bound via a QueryDataSet to all the rows in the SAMPLEDEPT table. The bottom table uses a ProcedureDataSet to execute the SAMPLEEMPINFO stored procedure, which returns only the rows in the SAMPLEEMP table with the same DEPTID as the currently selected row in the top table. The masterLink property of the ProcedureDataSet defines the master-detail relation between the two tables, and automatically causes the ProcedureDataSet to re-execute its stored procedure whenever the currently selected row in the master table changes.
Updates to the master table are automatically handled by the QueryDataSet's default QueryResolver, and updates to the detail table are handled by a ProcedureResolver bound to the resolver property of the ProcedureDataSet. When the Save button is pressed on the toolbar, the ProcedureDataSet determines which changes need to be applied back to the database, and its ProcedureResolver automatically calls the corresponding insert, update, and delete stored procedures on the server.
Pressing the Disconnect button the toolbar will unbind the tables from their DataSets (any unsaved changes will be lost). Closing the frame closes the database connection but does not drop the sample tables and procedures in the database. Press the Drop button to drop the tables and procedures on the server.