Enterprise Manager - Database: Procedures

To view this topic for the preceding Enterprise Manager, see Database - Procedures Tab.

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.

Create a Stored Procedure Using the Enterprise Manager

Select the desired database from the "Databases" list.

Click on the "Procedures" tab.

Click [+] Add New Stored Procedure.

Edit an Existing Stored Procedure Using the Enterprise Manager

Select the desired database from the "Databases" list.

Click on the "Procedures" tab.

Select the appropriate stored procedure.

Click [Edit Stored Procedure].

The Procedure Editor window displays:

General Information

The GeneralInformation tab provides SPROCs with several options.

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

Return Type Information

The Return Type Information provide the type of the return information from the stored procedure.

Return Type	Type of data returned by this SPROC (if it returns any data). This can be an SQL result set or a standard SQL data type such as CHAR, VARCHAR, INTEGER, DATE, etc.
Return Precision	The precision of the return value, if it has one.
Return Scale	The scale of the return value, if it has one.

Program Information

The Program Information provide the location of the stored procedure file and config file.

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 File Location	Optional location of an alternate config.bbx file to use for the stored procedure instead of the default.

Edit/View Code

Selecting [Edit/View Code] shows a read-only view of the source code for the stored procedure. To edit the code, BASIS recommends using the BASIS Development Kit (BDT) with the Eclipse IDE. Alternatively, if the file is a plain text file, a standard text editor will work as well.

Licensing Considerations

The JDBC or ODBC connection used to execute a stored procedure (SPROC) CALL checks out the license at connect time. Once the connection is established, any number of SPROCs can be executed on that connection without any further licenses.

Regardless of the statement type (i.e. SELECT, UPDATE, CALL - sproc, etc.) there are no additional licenses checked out. However, SPROCs can be written in such a way that they do in fact checkout additional license(s) and so care should be taken when writing sproc code.
Performing normal OPEN, READ RECORD, etc. calls in the SPROC code do NOT checkout additional licenses unless they specify a different user on the open. If a different user is specified, a new license will be checked out. This is by design.
When performing SQLOPEN in an SPROC, you must include MODE="PROCEDURE" on the SQLOPEN call: SQLOPEN(1, MODE="PROCEDURE")"ChileCompany"
If the MODE="PROCEDURE" is left out, an additional license will get checked out for the SQLOPEN call.
If you do an SQLOPEN and specify a different user in the MODE= string, then it will checkout an additional license. This is also by design.
If the SPROC establishes a connection to a remote BBj DBMS, the remote BBj DBMS will checkout an additional license.