|
Jeff Ash
Software Engineer |
|
evelopers use a variety of methods and tools for building BBj® applications. However, the applications all consist of one or
more programs that provide various functionality to the complete project. In some cases, it is beneficial to move some of the
business logic into the database so that it is also accessible from third party ODBC or JDBC applications. BBj 6.0 provides
this ability using stored procedures.
What is a Stored Procedure?
A stored procedure consists of a function or procedure written in BBj and embedded in the database. A new data dictionary file
contains stored procedure information such as a definition of the parameters and features of the procedure, and points to a
file containing the source code for the procedure. 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.
Real-World Example
The best way to understand stored procedures and how they work in BBj is to walk through a simple real-world example. This
example uses the Chile Company database included in the BBj installation. Suppose an application needs to acquire a list of
customers who have ordered a particular item. 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 and Web
applications could use it. This would work fine until a change occurs in the query or the logic becomes more complex such that
the 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 will be LIST_CUSTOMERS and will take a single argument – the item number for a
product. 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 Chile Company 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 save the following code sample in Figure 1 (available for
download) in your dictionary directory with the file name
list_customers.prc:
|
Figure 1. Code sample list_customers.prc |
To test the example, open Microsoft Query or another third party ODBC/JDBC application and enter the SQL statement shown in
the dialog box in Figure 2.
|
|
Figure 2. SQL statement |
To call the procedure from a BBj application, execute sptest.bbj in Figure 3 (also available for download):
|
Figure 3. Code sample sptest.bbj |
Summary
Stored procedures offer a powerful new way for developers to add value and ease of maintenance to their BBj applications.
Moving some of the common business and data access logic out of the BBj program into the database centralizes functionality in
one place, making it accessible to the BBj program as well as any third party JDBC/ODBC application. Developers can extend the
simple stored procedure example employed in this article to include complex BBj processing including CALL’s to other BBj
programs. All the powerful functions and features of the BBj language become available via an SQL CALL statement. A stored
procedure’s server-side processing delivers significant performance improvements over executing complex queries across the
network as only the result set passes back across the network to the calling SQL statement. We encourage you to explore the
myriad of new opportunities that you can take advantage of with this new BASIS DBMS feature.
|
