Stored procedures: InterBase, Oracle, and Sybase specific information

Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.

This set of topics includes tips to help you use

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 StatCODEent field in the procedure property editor, or in code:

select * from ORG_CHART

For a look at more complicated InterBase stored procedures, use JDBC 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. 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. Select the Frame file in the project pane, then select the Design tab.

  4. Place a ProcedureDataSet from the Data Express tab to the content pane.

  5. Select the procedure property to bring up the ProcedureDescriptor dialog.

  6. Select database1 from the Database drop-down list.

  7. Enter the following escape syntax in the Stored Procedure Escape or SQL Statement field, or in code:

    {?=call MyFct1(?)}
  8. Select the Parameters tab of the dialog. Select the ParameterRow just defined as row.

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.

Browsing sample applications that use stored procedures

In the /samples/DataExpress/ServerSpecificProcedures directory of your JBuilder installation, you can browse a sample application with sample code for Sybase, InterBase, and Oracle databases.