Obtaining data through a stored procedure

In JBuilder, data is extracted from a server into a data set. This action is called "providing". Once the data is provided, you can view and work with the data locally in data-aware controls. You can store your data to local memory (MemoryStore) or to a local single-file database with a hierarchical directory structure (DataStore). When you want to save the changes back to your database, you must resolve the data. This process is discussed in more detail in Understanding JBuilder's DataExpress architecture.

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. In the Client/Server version of JBuilder, 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, you need a Database component, a ProcedureDataSet component, and a ProcedureDescriptor. 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. Default: load all data up front.

  2. Asynchronous: 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. Fetch as Needed: load the rows as they are needed.

  4. One 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 SQL Explorer to browse and edit database server-specific schema objects, including tables, fields, stored procedure definitions, triggers, and indexes. For more information on SQL Explorer, select Tools|SQL Explorer and refer to its online help.

The following topics related to stored procedure components are covered:


Tutorial: Accessing data through a stored procedure

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 GridControl and a NavigatorControl for data viewing and editing.

The finished example for this tutorial may be available as a completed project in the samples\borland\samples\tutorial\dataset\StorProc directory of your JBuilder installation under the file name StorProc.jpr or check www.borland.com/techpubs/jbuilder/ for the latest updates. Other sample applications referencing stored procedures on a variety of servers are available in the samples\borland\samples\tutorial\dataset\StoredProcedure 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 local InterBase server (in the directory set up in Installing Local InterBase Server). 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 NavigatorControl and Tutorial: saving changes with a ProcedureResolver.

  1. Select File|Close All from the menu.

  2. Select File|Open/Create and open the project ProcSetUp.jpr, which may be located in the JBuilder\samples\borland\samples\tutorial\dataset\StorProc\ProcSetUp 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.

  3. Select createprocedures.java in the Navigation pane, then select Run|Run from the menu. This step creates the tables and procedures on the server.

  4. Select File|Close All from the menu.

Adding the DataSet components

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

  1. Select File|Close All.

  2. Select File|New and double-click the Application icon. Accept all defaults.

  3. Select Frame1.java in the Navigation pane. 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 Component tree.

  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 Local InterBase sample tables by setting the properties as indicated in the following table. These steps assume you have completed Installing Local InterBase Server.
    Property name Value
    Connection URL jdbc:odbc:DataSet Tutorial
    Username SYSDBA
    Password masterkey

    The code generated by the designer for this step is:

    database1.setConnection(new borland.sql.dataset.ConnectionDescriptor
       ("jdbc:odbc:DataSet Tutorial", "SYSDBA", "masterkey", false,
        "sun.jdbc.odbc.JdbcOdbcDriver"));

    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 in the gray area below the button. When the text indicates Success, click OK to close the dialog.

  6. Place a ProcedureDataSet component from the Data Express tab of the Component Palette on the Component tree. 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
    Place SQL Text In Resource Bundle uncheck

    Several procedures were created when createprocedures.java was run. The procedure GET_COUNTRIES is the only 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.

    The code generated by this step is:

    procedureDataSet1.setProcedure(new borland.sql.dataset.ProcedureDescriptor(database1, "select * from GET_COUNTRIES", null, true, Load.ALL));

    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, click OK to close the dialog.

Adding visual controls

To view the data in your application,

  1. Place a GridControl component from the JBCL tab of the Component Palette on the UI Designer. In the Inspector, set its dataSet property to procedureDataSet1. You'll notice that the grid fills with data.

  2. Select Run|Run to compile the application, and to browse the data set.

You must add resolving capability to your application in order to edit, insert, and delete data in the running application. See:


Discussion of stored procedure escape sequences, SQL statements, and server-specific procedure calls

When entering information in the Stored Procedure Escape or SQL Statement field in the procedure property editor, or in code, you have three options for the type of statement to enter. These are

In both of the last two examples, the parameter markers, or question marks, may be replaced with named parameters of the form :ParameterName. For an example using named parameters, see Example: using parameters with Oracle PL/SQL stored procedures. For an example using InterBase stored procedures, see Example: using InterBase stored procedures.


Creating tables and procedures for the tutorial manually

Stored procedures consist of a set of SQL statements. These statements can easily be written and compiled in JBuilder by creating a Java file, entering the statements, then compiling the code. If you do not have access to the sample project StorProc or if you would like to learn how to create a table and insert, update, and delete procedures from JBuilder, follow these steps:
  1. Select File|Close All from the menu.

  2. Select File|New and select the Class icon.

  3. Change the file directory and project name to StorProc\ProcSetUp\ProcSetUp.jpr in the Project Wizard.

  4. Change the Class Name to ProcSetUp in the New Java File dialog. Click OK to create the file ProcSetUp.java.

  5. Make sure that the file ProcSetUp.java is selected in the Navigation pane and that the Source window is selected in the UI Designer. Edit the code in the Source window or copy and paste from online help to match the code below:
    package ProcSetUp;
    
    import borland.jbcl.dataset.*;
    import borland.sql.dataset.*;
    import java.sql.*;
    
    public class CreateProcedures {
    
      public static void main(String[] args) throws DataSetException {
        Database database1 = new Database();
        database1.setConnection(new ConnectionDescriptor("jdbc:odbc:dataset tutorial", "sysdba", "masterkey", false, "sun.jdbc.odbc.JdbcOdbcDriver"));
        try { database1.executeStatement("DROP PROCEDURE GET_COUNTRIES"); } catch (Exception ex) {};
        try { database1.executeStatement("DROP PROCEDURE UPDATE_COUNTRY"); } catch (Exception ex) {};
        try { database1.executeStatement("DROP PROCEDURE INSERT_COUNTRY"); } catch (Exception ex) {};
        try { database1.executeStatement("DROP PROCEDURE DELETE_COUNTRY"); } catch (Exception ex) {};
        database1.executeStatement(getCountriesProc);
        database1.executeStatement(updateProc);
        database1.executeStatement(deleteProc);
        database1.executeStatement(insertProc);
        database1.closeConnection();
      }                                            
    
      static final String getCountriesProc = 
      
    "CREATE PROCEDURE GET_COUNTRIES RETURNS (     \r\n"+
    "  COUNTRY VARCHAR(15),                       \r\n"+
    "  CURRENCY VARCHAR(10) ) AS                  \r\n"+
    "BEGIN                                        \r\n"+
    " FOR SELECT c.country, c.currency            \r\n"+
    "  FROM country c                             \r\n"+
    "  INTO :COUNTRY,:CURRENCY                    \r\n"+
    " DO                                          \r\n"+
    " BEGIN                                       \r\n"+
    "  SUSPEND;                                   \r\n"+
    " END                                         \r\n"+
    "END;";
    
    
      
      static final String updateProc = 
      
    "CREATE PROCEDURE UPDATE_COUNTRY(             \r\n"+
    "  OLD_COUNTRY VARCHAR(15),                   \r\n"+
    "  NEW_COUNTRY VARCHAR(15),                   \r\n"+
    "  NEW_CURRENCY VARCHAR(20) ) AS              \r\n"+
    "BEGIN                                        \r\n"+
    "  UPDATE country                             \r\n"+
    "    SET country = :NEW_COUNTRY               \r\n"+
    "    WHERE country = :OLD_COUNTRY;            \r\n"+
    "END;";
    
    
    
      static final String insertProc = 
      
    "CREATE PROCEDURE INSERT_COUNTRY(             \r\n"+
    "  NEW_COUNTRY VARCHAR(15),                   \r\n"+
    "  NEW_CURRENCY VARCHAR(20) ) AS              \r\n"+
    "BEGIN                                        \r\n"+
    "  INSERT INTO country(country,currency)      \r\n"+
    "    VALUES (:NEW_COUNTRY,:NEW_CURRENCY);     \r\n"+
    "END;";
    
    
      static final String deleteProc = 
      
    "CREATE PROCEDURE DELETE_COUNTRY(             \r\n"+
    "  OLD_COUNTRY VARCHAR(15) ) AS               \r\n"+
    "BEGIN                                        \r\n"+
    "  DELETE FROM country                        \r\n"+
    "    WHERE country = :OLD_COUNTRY;            \r\n"+
    "END;";
    }                           
    
    
  6. Select createprocedures.java in the Navigation pane, then select Run|Run from the menu. This step creates the tables and procedures on the server.

  7. Select File|Close All from the menu.

This is a very simple procedure. For a look at more complicated InterBase stored procedures, use Tools|SQL Explorer to browse procedures on this server. An interesting example is the stored procedure ORG_CHART, which returns a result set that combines data from several tables. ORG_CHART is written in InterBase's procedure and trigger language, which includes SQL data manipulation statements plus control structures and exception handling.


Example: using InterBase stored procedures

In InterBase, the SELECT procedures may be used to generate a DataSet. In the InterBase sample database, employee.gdb, the stored procedure ORG_CHART is such a procedure. To call this procedure from JBuilder, enter the following syntax in the Stored Procedure Escape or SQL Statement field in the procedure property editor, or in code:

select * from ORG_CHART

For a look at more complicated InterBase stored procedures, use SQL Explorer to browse procedures on this server. ORG_CHART is an interesting example. It returns a result set that combines data from several tables. ORG_CHART is written in InterBase's procedure and trigger language, which includes SQL data manipulation statements plus control structures and exception handling.

The output parameters of ORG_CHART turn into columns of the produced DataSet.

See the InterBase Server documentation for more information on writing InterBase stored procedures or see Creating tables and procedures for the tutorial manually for an example of a stored procedure written in InterBase.


Example: using parameters with Oracle PL/SQL stored procedures

Currently, a ProcedureDataSet can only be populated with Oracle PL/SQL stored procedures if you are using Oracle's type-2 or type-4 JDBC drivers, or Borland's DataGateway. The stored procedure that is called must be a function with a return type of CURSOR REF.

Follow this general outline for using Oracle stored procedures in JBuilder:

  1. Define the function using PL/SQL. The following is an example of a function description defined in PL/SQL that has a return type of CURSOR REF. This example assumes that a table named MyTable1 exists.
    
    create or replace function MyFct1(INP VARCHAR2) RETURN rcMyTable1 as
      type rcMyTable1 is ref cursor return MyTable1%ROWTYPE;
        rc rcMyTable;
    begin
      open rc for select * from MyTable1;
      return rc;
    end;
    

  2. Set up a ParameterRow to pass to the ProcedureDescriptor. The input parameter INP should be specified in the ParameterRow, but the special return value of a CURSOR REF should not. JBuilder will use the output of the return value to fill the ProcedureDataSet with data. An example for doing this with a ParameterRow follows.

    ParameterRow row = new ParameterRow();
    row.addColumn( "INP", Variant.STRING, ParameterType.IN);
    row.setString("INP", "Input Value");
    String proc = "{?=call MyFct1(?)}";

  3. Define the ProcedureDescriptor to call this function from JBuilder.
    1. Select the Frame file in the Navigation pane, then select the Design tab.
    2. Place a ProcedureDataSet from the Data Express tab to the Component Tree.
    3. Select the procedure property to bring up the ProcedureDescriptor dialog.
    4. Select database1 from the Database drop-down list.
    5. Enter the following escape syntax in the Stored Procedure Escape or SQL Statement field, or in code:

      {?=call MyPackage1.MyFct1(?)}

    6. Select the Parameters tab of the dialog. Select the ParameterRow just defined.

See your Oracle server documentation for information on the Oracle PL/SQL language.


Using Sybase stored procedures

Stored procedures created on Sybase servers are created in a "chained" transaction mode. In order to call Sybase stored procedures as part of a ProcedureResolver, the procedures must be modified to run in an unchained transaction mode. To do this, use the Sybase stored system procedure sp_procxmode to change the transaction mode to either "anymode" or "unchained". For more details, see the Sybase documentation.


Storing data locally

By default, all data sets store row data in memory (MemoryStore). To store data in a single file with a hierarchical directory structure, use a DataStore instead. Storing data in a DataStore provides persistent storage and caching for JBuilder DataSets, Java Objects, and arbitrary files. The advantages to using DataStore are that the implementation is pure Java, portable, there is a smaller footprint, and provides for better performance.