Stored Procedures Case Study

Add Business Logic to the Database Using Stored Procedures

Though developers can build BBj applications using a variety of methods and tools, each providing unique functionality to the complete project, they may choose to move some of the business logic into the database for access from third party ODBC/JDBC applications. Stored procedures, available in BBj 6.0 and higher, provide this ability.

A stored procedure consists of a function or procedure written in BBj and embedded in the database. Two new data dictionary files contain the information describing the stored procedures such as a definition of the parameters and features of the procedure, and the location of a file containing the source code for the procedure. BBj creates these files (procedure.dd and proc_params.dd) when it creates the first stored procedure in a database. To access a stored procedure, a program executes an SQL CALL statement that specifies the name of the procedure and any necessary parameters. Using SQL makes it possible to call the procedure from BBj or any third party ODBC/JDBC application and get the same results.

Creating/Editing a Stored Procedure

Use the BBj Enterprise Manager to create and edit stored procedures. It is good design practice to decide ahead of time where to locate the source code files for the stored procedures in a database. The default location is in the same directory as the data dictionary files. However, it may be desirable to keep them in a separate directory that contains nothing but stored procedure source files. Developers can included a complete or relative path to the source file for a stored procedure, or user properties (also called globals) can be defined in the database properties and then used in the same way as table data file paths (i.e. (MYPROCS)procname.prc).

NOTE: Using user properties is highly recommended as it makes the database more portable and easier to change the location of files later.

To create a new stored procedure:

Log in to the BBj Enterprise Manager.
Expand the Databases tree folder.
Locate the node for the database to add the stored procedure to, and expand it.
Locate the Stored Procedures node and expand it.
Right click on the Stored Procedures node and select New Stored Procedure.
Enter a name for the stored procedure. Names must follow the same naming conventions as tables and columns and can be up to 32 characters long.
Click on the newly created node under the Stored Procedures node.
In the right hand pane, enter a file name in the Source Location field that will contain the stored procedure source code such as (DICTIONARY)my_proc.prc. The file can be a plain text file or a compiled BBj file.
Optionally place a check in the "Has Result Set" check box if the stored procedure will be returning an SQL result set.
Optionally specify the "Return Type," "Return Precision" (length), and "Return Scale" if the stored procedure will return a value such as a string, integer, date, etc.
Optionally add any parameters that the stored procedure requires and specify the SQL type, direction (IN, OUT, IN/OUT), and the precision (length) and scale.
Click the [Save Changes] button.
Use the BASIS IDE or another text editor to write the source code for the stored procedure and save it in the appropriate location with the specified name.

Stored Procedure Properties and Options

The following properties and options are available on stored procedures:

Name – The name of the stored procedure. Field forces all characters to uppercase and validates input, only allowing valid characters. Names can be up to 32 characters long.

Source Location – The path to the file containing the source code for the stored procedure. The path can be a relative or full path to the file. The path can also include user properties (also called globals in BBj) such as (DICTIONARY), etc. BASIS recommends using this latter method because it makes databases more portable and easier to maintain.

Config Location – The location of the config.bbx in which the interpreter runs the stored procedure. Stored procedures run in their own interpreter so this config.bbx can be different than the other BBj programs.

Return Type – The SQL data type that the return value will be if the stored procedure will have a return value. Leave it set to "<<No Return Value>>" if the stored procedure does not have a return value.

Return Precision – The precision or length of the return value if a return value is used.

Return Scale – The scale of the return value if used. This is only meaningful on NUMERIC type values.

Has Result Set – Check this box if the stored procedure will return an SQL result set. Leave it unchecked if it will be returning an update count or nothing.

Parameter Specification – Table of the parameters that the stored procedure requires. The following settings are available on each parameter:

Name – Name of parameter. The field forces names to uppercase and only allows valid characters. Names can be up to 32 characters long.
SQL Type – SQL data type of the parameter.
Direction – The direction the parameter works. This can be IN (value is sent to the procedure), OUT (value is retrieved from the procedure), or IN/OUT (value is both sent to and then updated by the procedure).
Precision – The precision or length of the values in the parameter.
Scale – The scale of the values in the parameter. Only valid on NUMERIC type parameters.
Comments – Comments about the parameter, up to 255 characters long.

Calling a Stored Procedure

Call a stored procedure using an SQL CALL statement from any ODBC/JDBC application, or from a BBj program. The syntax for a CALL statement is as follows:

[?=]CALLproc_name([param1, param2, ...])

The optional "?=" at the beginning of the line means that the stored procedure is a function that returns a simple value such as a string, number, date, etc.
proc_name is the name of the procedure that should be called.
The optional param1, param2, etc. is the list of parameter values that are to be passed to the procedure when it is called. Parameters can be IN (a value is sent), OUT (a value will be returned in that position), or IN/OUT (a value will be both sent and returned).

NOTE: Consult the JDBC API documentation from Oracle for details on calling a stored procedure from a JDBC application and accessing return values and OUT parameters, or the ODBC API documentation from Microsoft Corporation for accessing stored procedures from ODBC applications.

Calling a Stored Procedure From a BBj Application

Use standard SQLPREP and SQLEXEC calls to execute a stored procedure from a BBj application. These calls are used to execute BBj stored procedures located in BBj databases as well as stored procedures located in third party databases such as Oracle, MySQL, etc. The following example shows how to call a stored procedure named MY_PROC that returns an integer, has a result set, and returns a string in the param2$ variable for the second parameter (an OUT parameter):

SQLOPEN(chan) "MyDB"
SQLPREP(chan) "?=CALL my_proc(?, ?)"
SQLEXEC(chan) retVal, param1, param2$
DIM REC$:SQLTMPL(chan)
rem Print out the result set from the stored procedure
WHILE 1
REC$=SQLFETCH(chan,end=*BREAK)
PRINT REC$
WEND

Writing the Stored Procedure Source Code

The heart of the stored procedure is the BBj source code that performs the operations of the procedure. While the code can do anything that the BBj language allows (except GUI operations), it is also necessary to access certain information about the procedure call that was made. To access this information, use the BBjStoredProcedureData object that can be retrieved from the BBj API using the following code. Also included in the sample is a few of the most common calls used with the BBjStoredProcedureData:

rem Get the BBjStoredProcedureData instance
sp! = BBJAPI().getFileSystem().getStoredProcedureData()

rem Get the value from an IN parameter named MY_PARAM
myParam$ = sp!.getParameter("MY_PARAM")

rem Set the value in an OUT parameter name MY_OUT
sp!.setParameter("MY_OUT", outVal$)

rem Set the return value for the procedure
sp!.setReturn(retVal)

rem Run the query that will be the result set of the procedure
chan = sqlunt
sqlopen(chan)"MyDB"
sqlprep(chan)"select * from my_table"
sqlexec(chan)

rem Set the returning result set to be the result of the query.
sp!.setResultSet(chan)

rem This returns an UPDATE count.
rem NOTE: Either an UPDATE count or a result set can be returned
rem but not both.
sp!.setUpdateCount(5)

Real-world Example

A great way to understand stored procedures and how they work in BBj is to walk through a simple real-world example. Our example uses the Chile Company database that is included in the BBj installation. Suppose an application needs to acquire a list of customers who have ordered a particular item from the company. Then suppose both a BBj application and a Web-based application need to access this information. One solution would be to provide all users with the appropriate SQL query so that the BBj application could use it, as well as the Web application. This would work fine until a change occurs in the query or the logic becomes more complex such that a query would not be able to provide the required result set. We could provide the changes to everyone so that they can update their code or we could use a stored procedure and centralize the logic.

The name of the stored procedure sample is LIST_CUSTOMERS and will take a single argument – the item number for a product. We start by creating a new stored procedure in the Chile Company database:

Log in to the BBj Enterprise Manager.
Expand the Databases tree folder.
Locate the ChileCompany database and expand its folder.
Locate the Stored Procedures folder and expand it.
Right click on the Stored Procedures folder and select New Stored Procedure.
Enter LIST_CUSTOMERS when prompted for the name of the procedure.
Click on the newly created LIST_CUSTOMERS node under the Stored Procedures folder.
In the right hand pane, enter (DICTIONARY)list_customers.prc for the Source Location field.
Place a check in the "Has Result Set" check box.
In the Parameter Specification list, click in the first cell and type the parameter name ITEM_NUM, set the SQL Type to CHAR, set the Direction to IN, and set Precision to 6.
Click the [Save Changes] button.
Use the BASIS IDE or another text editor to create and save the following code in the data dictionary as list_customers.prc:

rem Get an object containing the parameter values passed into the procedure.
sp! = BBJAPI().getFileSystem().getStoredProcedureData()

rem Get the item number passed into the procedure
itemNum$ = sp!.getParameter("ITEM_NUM")

rem Open an SQL channel to run the query on. We want to use the same database.
chan = sqlunt
sqlopen(chan, mode="PROCEDURE")sp!.getDatabaseName()

rem Run the query that will get the appropriate information
sqlprep(chan)"select c.cust_num, cvs(c.last_name,3) + ', ' + cvs(c.first_name,3)
: cust_name from customer c, order_header o, order_line l
: where l.item_num = ? and o.order_num = l.order_num and c.cust_num = o.cust_num"

sqlexec(chan)itemNum$

rem Set the returning result set to be the result of the query.
sp!.setResultSet(chan)

To test the example, open Microsoft Query or another third party ODBC or JDBC application and enter the following SQL statement:

CALL LIST_CUSTOMERS('000010')

To call the procedure from a BBj application, execute the program sptest.bbj shown below.

rem Open up an SQL channel to the ChileCompany Database
rem and call the LIST_CUSTOMERS stored procedure

chan = sqlunt
SQLOPEN(chan) "ChileCompany"
SQLPREP(chan) "CALL LIST_CUSTOMERS('000010')"
SQLEXEC(chan)
DIM REC$:SQLTMPL(chan)

rem Print out the result set from the stored procedure
WHILE 1
REC$=SQLFETCH(chan,end=*BREAK)
PRINT REC$
WEND