Enterprise Manager Java App: Database - Procedures Tab

In BBj 13.0 and higher, the Enterprise Manager Java app has been superseded by a new browser Enterprise Manager and Eclipse plug-in. See Enterprise Manager - Database: Procedures.

Stored Procedures is a powerful feature of the BBj SQL engine that allows developers to embed portions of their application logic in the database. Putting logic in the database makes that functionality available to not only BBj programs, but also third party ODBC or JDBC applications via the SQL CALL statement.

Stored procedure code is written using the familiar BBj language.

To create a stored procedure using the Enterprise Manager

1. Select the desired database from the "Databases" list.

2. Click on the Stored Procedures tab.

3. Click [Add New Stored Procedure].

To edit an existing stored procedure using the Enterprise Manager

1. Select the desired database from the "Databases" list.

2. Click on the Stored Procedures tab.

3. Select the appropriate stored procedure.

4. Click [Edit Stored Procedure].

Information Tab

The Information tab provides SPROCs with several options.

Setting

Description

Name

Name of the stored procedure. This is the name that will be referred to from CALL statements.

Source Location

Location of the source code that will be executed when the procedure is called. This should be a relative path as shown above. The source file can be a BBj program file or a plain text file.

Config Location

Optional location of an alternate config.bbx file to use for the stored procedure instead of the default.

Return Type

Optional data type if this procedure has a simple return value. Procedures may return either a simple type, or a result set, but not both.

Return Precision

The precision of the return value, if it has one.

Return Scale

The scale of the return value, if it has one.

Has Result Set

Checked if this procedure returns a result set. Procedures may return either a simple type, or a result set, but not both.

Parameter Specification

A SPROC can have zero or more parameters. Parameters can be one of three types: IN, OUT, or IN/OUT (see Directionbelow).

Name	Name of the parameter. This is the name used to get/set the value from the stored procedure code.
SQL Type	The SQL data type for the parameter. This is the standard SQL data type expected and not a BBx string template type.
Direction	The direction the parameter functions. IN parameter only allows values to be passed into the procedure to be processed by the code. OUT parameters only return a value from the procedure. IN/OUT parameters can pass values into the procedure and they also return values from the procedure.
Precision	The precision or length of the value for the parameter. This value does not limit the size of the values passed in, but it is used by third party applications interested in information regarding the expectations of the stored procedure for the parameter. While any size will work, be advised that some third party applications may not interact with the stored procedure correctly if this size value is smaller than the actual data expected by the procedure.
Scale	The scale of the value for the parameter.
Comments	Any comments relevant to the parameter.

Build Source Template
(BBj 9.0 and higher)

When the SPROC is fully defined, clicking [Build Source Template] creates a skeleton source file for the SPROC. This file contains the necessary code to access the stored procedure information, get/set parameter values, populate an in-memory record set that can be returned as the result of the call, sample code to read from a data file, etc. This provides an instant foundation to begin building the stored procedure code.

NOTE: Creating a source template erases the current contents of the stored procedure source file.

Upon clicking [Build Source Template], a warning displays requiring confirmation to continue. Next, if the SPROC is defined to return a result set, it will prompt for an option string template. If one is provided, the sample code to generate, populate, and return the result set matching the template is generated (otherwise a default template will be used).

Below is an example that shows a portion of the generated code.

Source Code Tab

The Source Code tab shows a read-only view of the source code for the stored procedure. To edit the code, BASIS recommends using the BASIS IDE. Alternatively, if the file is a plain text file, a standard text editor will work as well.

Enterprise Manager Java App: Database - Procedures Tab

See Also