Tutorial 6: Using Stored Procedures

Note: We recommend that you print this tutorial for easy reference. To print, click on the right-hand frame before accessing your HTML Browser's File/Print command.

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedures reduce network traffic and improve performance. Additionally, stored procedures can be used to help ensure the integrity of the database.

DBPak can use stored procedures as an alternate means of inserting, deleting, or updating records.

In this tutorial, we'll create and use a stored procedure as part of a new part-entry application. In the OrderEntry database, adding a part means inserting a new record into the Inventory table.

Suppose the following restrictions have been placed on the database. In the Inventory table, we have imposed the rule that all part numbers must be entered in uppercase. In addition, all fields in each record must be specified--none can be left blank. These are restrictions that we would rather not have in our application. Therefore, the first step is to write a stored procedure to take input data from our application and make it conform to the specified rules.

A Sample Stored Procedure Used by this Tutorial

This section describes the Sybase and Oracle versions of the sample stored procedure provided for use with this tutorial.

Sybase users

• The Sybase stored procedure for this tutorial has five arguments, two of which have default values:

create procedure newPart @pno PartType, @desc varchar(40),
	@price money
	@quant int = 0, @reorder int = 10 
as

The following code converts the part number to uppercase:

declare @newPno PartType
select @newPno = upper(@pno)

If the quantity and reorder quantity values are NULL, the default values are used:

if @quant is NULL
select @quant = 0
if @reorder is NULL
select @reorder = 10 

The following code inserts the record into the Inventory table:

insert into Inventory (Part_Number, Description, Price,
	Quantity_On_Hand, Reorder_Quantity)
values (@newPno,@desc,@price,@quant,@reorder )

We select the fields from the record that we just inserted and use them as the return value of the procedure. These values will be displayed in DBPak widgets.

select Part_Number, Description, Price, Quantity_On_Hand,
	Reorder_Quantity from Inventory where
	Part_Number = @newPno

This is the complete stored procedure. The next step is to use it in Database Xcessory. We will create a data entry screen with the Schema Browser.

Oracle users

• The Oracle stored procedure for this tutorial has five arguments:

              
  	create or replace procedure newPart(
              pno IN OUT char,
              thePrice IN OUT NUMBER,
              part_description IN varchar,
              quantity_on_hand IN OUT integer,
              reorder IN OUT integer
              )
              
          as
              newPno CHAR(8);
              quan integer := 0;
         BEGIN
  
      

  The following code converts the part number to uppercase:

              newPno := UPPER(pno);
    

  If the quantity and reorder quantity values are NULL, the default values are used:

  	IF (quantity_on_hand is not NULL) then
  		quan := quantity_on_hand;
  	end if;
  
  	IF (reorder is NULL) then
  		reorder := 10;
          end if;
  
  

  The following code inserts the record into the Inventory table:

  	INSERT INTO INVENTORY (PART_NUMBER, DESCRIPTION, PRICE,
  		QUANTITY_ON_HAND, REORDER_QUANTITY)
  	VALUES (newPno, part_description, thePrice, quan, reorder);
  
  

  We select the fields from the record that we just inserted and store the values in the in/out parameters of the stored procedure. These values will be displayed in DBPak widgets.

SELECT PART_NUMBER, PRICE, QUANTITY_ON_HAND,REORDER_QUANTITY
INTO pno, thePrice, quantity_on_hand, reorder
FROM INVENTORY WHERE PART_NUMBER = newPno;
END

Creating the Interface

Oracle users

• Because Oracle is case-insensitive, the Schema Browser renders all names uniformly in uppercase.
• Select OrderEntry from the Database list (Sybase) or DBPak from the Owner list (Oracle).
• Select Inventory from the Tables list.
• Select Data Field from the Object Type option menu of the Schema Browser.
• Drag the mouse through the Columns window to select all the listed fields.
• While holding down MB2 over the selected fields, drag the pointer onto the Browser.
• Release MB2. Database Xcessory creates an entire interface, including the control panel, as shown below:

Interface Created by Dragging and Dropping All Inventory Fields onto Browser

Setting Resources

The Quantity_On_Hand and the Reorder_Quantity fields are considered optional items.To tell DBPak that these fields are not required for an XiDB_INSERT operation, set the XmNrequiredForInsert resource to False for each of these fields:

• To select the Quantity_On_Hand widget and display its resources in the Resource Editor, double-click on inventory_Quantity_On_Hand in the Browser hierarchy.
• Find the requiredForInsert resource in the Resource Editor.
• Toggle the requiredForInsert resource to False.
• Double-click on inventory_Reorder_Quantity in the Browser hierarchy.
• Find the requiredForInsert resource in the Resource Editor (displayed automatically in the center of the Resource Editor window because requiredForInsert is the last resource you located).
• Toggle the requiredForInsert resource to False.

Notifying DBPak about the Stored Procedure

Use the following steps to tell DBPak to use the stored procedure you have written in this tutorial when performing an insert.

• Double-click on the query q_Inventory widget in the Browser hierarchy.
• Find the insertProc resource in the Resource Editor.
• Edit the insertProc resource.

Sybase users

• The following resource setting is included as the tutorial-6.text file in your example/dx-tutorials directory. You can cut and paste the following from the file into the insertProc input field in the Resource Editor:

inventory_Part_Number, inventory_Description,
inventory_Price, inventory_Quantity_On_Hand,
inventory_Reorder_Quantity <- newPart(
[inventory_Part_Number], [inventory_Description],
[inventory_Price], [inventory_Quantity_On_Hand] opt,
[inventory_Reorder_Quantity] opt)

This resource setting specifies the following:

Call the newPart stored procedure with the following arguments:

• The current contents of the inventory_Part_Number, inventory_Price, and inventory_Description widgets:

[inventory_Part_Number], [inventory_Description],
[inventory_Price]

• If they are not empty, the current contents of the inventory_Quantity_On_Hand and inventory_Reorder_Quantity widgets:

[inventory_Quantity_On_Hand] opt,
[inventory_Reorder_Quantity] opt

• Assign the results of stored procedure "newPart" to the following widgets:

inventory_Part_Number, inventory_Description,
inventory_Price, inventory_Quantity_On_Hand,
inventory_Reorder_Quantity <- newPart

This ensures that the user sees the values that have actually been entered into the database.

Oracle users

The following resource setting is included as the tutorial-6.text file in your example/dx-tutorials/oracle directory. You can cut and paste the following from the file into the insertProc input field in the Resource Editor:

newPart([inventory_Part_Number] in out, 
[inventory_Price] in out, 
[inventory_Description],
[inventory_Quantity_On_Hand] in out opt, 
[inventory_Reorder_Quantity] in out opt )

This resource setting specifies the following:

• Call the newPart stored procedure with the following arguments:
  
  The current contents of the inventory_Part_Number, inventory_Price, 
  and inventory_Description widgets:
  
  	[inventory_Part_Number] in out, 
  	[inventory_Price] in out, 
  	[inventory_Description],
  
  If they are not empty, the current contents of the inventory_Quantity_On_Hand 
  and inventory_Reorder_Quantity widgets:
  
  	[inventory_Quantity_On_Hand] in out opt,
  	[inventory_Reorder_Quantity] in out opt
  

Note: The parameters [inventory_ Part_Number], [inventory_Price], [inventory_Quantity_On_Hand], and [inventory_ Reorder_Quantity] are used for both input and output ("in out" parameters). The current values of each widget are used as input, and the values that the stored procedure returns are displayed in each widget. This ensures that the user sees the values that have actually been entered into the database.

Testing the Interface

You can now enter Play mode and test the interface.