DX provides extensive support for stored procedures. Stored procedures are SQL-based routines that define commonly used database interactions. Stored procedures are frequently implemented by database administrators in order to:

• model business rules;
• enforce security constraints;
• ensure that table inserts, updates and deletes are performed correctly.

DX supports stored procedures in two ways:

• Within DBPak Query widgets, you can set resources to specify Update, Insert and Delete stored procedures. DX will execute the stored procedure in place of, or in addition to, dynamically executed SQL.
• You can also use API calls to invoke a stored procedure at your discretion, by setting the XiDBDoProcedure() or XiDBDoSelectProcedure parameters of any Query widget.

Using Stored Procedures

All DBMSs have different methods for calling stored procedures. DBPak unifies them into a standard form which we call a stored procedure call, a string that specifies all of the following:

• Name of the procedure
• Parameters
• Widget which should receive the return value (if any)
• Set of widgets to which to distribute the results.

For each parameter, a stored procedure call specifies the following:

• Value or widget from which to retrieve the value;
• Type of parameter (in, out or in/out)
• Whether the parameter is required or optional

The basic syntax of a stored procedure call is:

{<widget> {,<widget>...<-} <proc_name> (<params>) {RETURNS <widget>}

where items in braces are optional, and an item followed by ... denotes an optionally repeated item.

Note than not all DBMS's support all features of the DBPak stored procedure call. The following table illustrates feature support among DBMS's:

Example 1: Inserting Records

This example describes how to use a stored procedure to create new Inventory records. The procedure below (written in Sybase Transact/SQL) ensures that the part number contains only uppercase characters, and fills in a few column defaults. It is installed in the sample database.

Code: Write the procedure as follows:

	/*newPart - creates a new part recorded
	* It forces the part number to upper case and fills in some
	* defaults
	*/
	
	create procedure newPart @pno PartType, @desc varchar(40),
		@price money, @quant int = 0, @ reorder int = 10
	as
		declare @newPno PartType

		
		select @newPno = upper(@pno)
		if @quant is NULL
			select @quant = 0
		if @reorder is NULL
			select @reorder = 10

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

This procedure accepts as paramaters all of the fields of the Inventory record, and returns (with the final select statement) the same set of fields.

In our application, we create XiDBDataField widgets as described in the following table:

The XmNinsertProc resource of the Query would be:

part_num, description, price, quant, reorder <-newPart([part_num], [description],[price],[quant] opt, [reorder]

Procedure parameters that might be defaulted are marked as single 'opt' in the XmNinsertProc procedure call, and XmNrequiredForInsert is set to False in their corresponding widgets. This allows the application to call the newPart procedure with those fields left blank. You must correctly match the values returned from the procedure with the appropriate widgets.

Although this example requires an initial Part _Number, it's not a requirement. An application might have an insert procedure that takes no input parameters, creates a blank record with a new unique key, and returns the key for DBPak to send to a widget. After the insert, DBPak selects the new record (using the returned key) to fill in the remaining fields.

Note: to use this technique, all Data presentation widget must set to false.

Example 2: Updating a Value with a Stored Procedure

This example illustrates how to write a stored procedure for updating a value in the database (written in Sybase Transact-SQL) to update the Price column of the Inventory table in the OrderEntry database).

  
	/* changePrice - changes Price of an Inventory
	item. Will fail if you raise or lower more than 10%. There is no
	reason for that restriction other than to highlight what happens
	whan an update procedure fails when using DBPak.
	*/

         create procedure changePrice @pno PartType, @newPrice money
         as
	        declare @curPrice money

	        select @curPrice = Price from Inventory
		     		where Part_Number = @pno

			if @@rowcount != 1 begin
				raiserror 9999 "Part %1! not found", @pno
				return 1
	        	end

			if 	((@newPrice  <   @curPrice * .9) or
				 (@newPrice  >   @curPrice * 1.1))
			begin
                  		raiserror 40001 "price change for %1! is too large", @pno
                  		return 1
	        	end

 			update Inventory set Price = @newPrice
       				where Part_Number = @pno
        		return 0

To use this procedure, you need at least two Data Presentation widgets in the application: one to provide the Part_Number and one to provide the new Price.

The widget holding the Price uses the XmNupdateProc resource to specify the this stored procedure as follows:

	Widget parent, query;
	Widget partNo, price;
	...

	partNo = XtVaCreateManagedWidget("partnumber",
		XiDBDataFieldWidgetClass, parent,
		XmNexpression, "Inventory.Part_Number",
		XmNquery, query,
		XmNkeyField, True,
 		XmNdataType, XiDB_INTEGER,
		NULL);

	price = XtVaCreateManagedWidget("price",
		XiDBDataFieldWidgetClass, parent,
		XmNexpression, "Inventory.Price",
		XmNquery, query,
		XmNdataType, XiDB_FLOAT,
		XmNudpateProc, "changePrice ([partnumber],[price])",
		NULL);

Note that the XmNupdateProc value references the widgets that are parameters by the Xt name of the widget, not the name of the C variable holding the widget ID.

If this update fails , the application receives an XmNdbErrorCallback on the associated Database Access widget. The native_reason member will contain the error code procided by the procedure (either 99999 or 40001).

Additional Resources:

• Refer to Stored Procedures, in DX HyperHelp.
• See Considerations When Using Stored Procedures in DX HyperHelp for a usage checklist.
• Tutorial 6: Using Stored Procedures takes you step-by-step through the process of inserting a new record by creating and using stored procedures.