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
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.
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:
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;
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(?)}";
Select the Frame file in the project pane, then select the Design tab.
Place a ProcedureDataSet
from the Data Express tab to the content pane.
Select the procedure
property to bring up the ProcedureDescriptor
dialog.
Select database1
from the Database
drop-down list.
Enter the following escape syntax in the Stored Procedure Escape or SQL Statement field, or in code:
{?=call MyFct1(?)}
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.
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.
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.