This tutorial describes how to build a simple form-based database application that allows users to view and edit customer records from the Accounts table. Using Database Xcessory, you simply select fields from the Schema Browser window and drag them to the application under construction. Database Xcessory will automatically set all the resources to the correct values.

A Simple Forms-Based Application

Creating the Container

Widgets in an Xt (X Toolkit) based application are arranged in a hierarchical parent-child structure. Shell widgets are parents to containers that contain other containers and more fundamental primitive widgets. The primitive widgets are usually the ones into which you enter data and with which you interact.

Any window in your finished application must have a container, which must be inside a shell. The container holds the widgets that make up the form.

Using Database Xcessory, all you have to do to begin is create a container. This is because Database Xcessory will automatically create a shell if you create a container without a parent.

Bulletin board

The container we'll use in this application is a bulletin board (XmBulletinBoard). The bulletin board allows you to place children (labelled text fields, in this case) anywhere inside the bulletin board. A bulletin board imposes no constraints on the position of its children. The advantage of using the bulletin board as a container is that it is very easy to use. The disadvantage, for more sophisticated applications, is that it doesn't do anything special when the user resizes the window.

Creating a bulletin board

Use the following steps to create a bulletin board widget that will serve as the parent widget for the XiDBDataField widgets you will create later in this tutorial.

1. In the Palette, click on the Containers tab to display the Container widgets.
2. Click on Bulletin Board.

Notice the square widget outline that appears.

1. Move the outline to the location on the root window where you want to place the bulletin board.
2. Hold down MB1 and drag down and to the right to resize the widget to 4 inches wide by 3 inches long on your display.

A top level shell widget is automatically created with a bulletin board inside of it. The hierarchy of these widgets is displayed as a tree display in the Browser window. The top level shell is typically used for windows that will be displayed most often in an application. You can create other types of shells, depending on the application. These are described in the Builder Xcessory Users Guide andBuilder Xcessory Reference Manual.

Using the Schema Browser to Add Data Fields

Oracle users

• Because Oracle is case-insensitive, the Schema Browser renders all names uniformly in uppercase.

Once you've created a container, the next step is to add the labelled data fields using the Schema Browser window. You can use the Schema Browser as if it were a palette of Data Presentation widgets. Drag field names from the Schema Browser to the application. Database Xcessory then creates data fields, graphs, tables, and other Data Presentation widgets with the resources automatically set to display and edit that field (if editable; for example, if you create a graph instead of a data field, that graph is not editable).

Adding labeled fields

Use the following steps to add labelled data fields as children of the bulletin board container you have already created:

• Select Sample Data from the View menu to display the Sample Data window at the bottom of the Schema Browser window.
• Select OrderEntry from the Database list (Sybase users) or DBPak from the Owner list (Oracle users).

The Database list (Sybase) or Owner list (Oracle) is located in the upper right corner of the Schema Browser. To display all available databases (Sybase), or owners (Oracle), click on the down arrow. Click on a database name (in this case, OrderEntry) or owner name (in this case DBPak) to select it.

• Select Accounts from the Tables list.

The Tables list is located on the left side of the Schema Browser. Click on the Accounts table to select it.

The Columns window list now shows all the columns that correspond to the Accounts table along with their data types. The Sample Data window at the bottom of the Schema Browser now displays data that has been read from the first set of rows in the table. 

Declarations of Accounts Table (Sybase version).

Selecting Data Fields

Check that Data Field is selected in the Object Type option menu of the Schema Browser. (The Object Type option menu is located in the upper left corner of the Schema Browser.)

To display the option menu:

• Click on the currently-selected menu item.
• To make a new selection, click on a different menu item. That item is then displayed as the currently-selected menu item, and the option menu is no longer displayed.

The Object Type option menu displays the type of widget that will be created when you drag a field from the Schema Browser. Most other DBPak widgets can also be created with the Schema Browser.

Toggling on the Labeled Fields

Check that Labeled Fields is toggled on in the Schema Browser Option menu. The Option menu is in the menu bar at the top of the Schema Browser. The Labeled Fields menu option is toggled on by default.

• Select Account_Number, Name, Address, and City from the column list, and drag them on to the Bulletin Board.

 

Multiple Columns Selected

One XiDBDataField will be created in your interface for each column definition selected. These widgets are pre-configured to display data from the columns selected in the Schema Browser.

Database Xcessory creates the Data Source, Query and Data Field widgets. The children of the bulletin board, in the Browser object instance hierarchy look like the Instance Hierarchy shown below:

Instance Hierarchy

Notice that two objects, q_Accounts (Query widget) and orderEntry (Database Access widget) for Sybase, or q_ACCOUNTS andtest for Oracle, have been created but are not physica>

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.

In this tutorial you will develop a form that has fields from two tables. A join is often necessary when there are fields from more than one table that will be displayed on the same form. In this case, you will be joining the Invoice table and the Accounts table.

Accounts are organized by a unique integer key, Account_Number. The Invoice table could store all the account information (such as the name, address, city, state, and zip code), but there is no reason to duplicate this data since it is already in the Accounts table. The Invoice table has eight fields. Only one of these shares information with the Accounts table. This field is the Account_Number field.

The Application You Will Create using a Simple Join

Creating the Application

Oracle users

• Because Oracle is case-insensitive, the Schema Browser renders all names uniformly in uppercase.

Performing a simple join

Use the following steps to perform a simple join:

• Create a bulletin board widget to serve as the parent widget for window.

The bulletin board should be about 3 inches high by 4 inches wide.

• Select the OrderEntry database from the Database list (Sybase) or DBPak from the Owner list (Oracle) on the Schema Browser.
• Select the Invoice table from the Tables list on the Schema Browser.

Add the fields from the Invoice table to the application. Later, you will add the account information.

• Select the Invoice_Number and the Account_Number field in the Columns list on the Schema Browser.

Since the two fields are next to each other in the list, click on the Invoice_Number field and drag down to the Account_Number field to select both fields at the same time.

• UsingMB2, drag the two selected fields from the Schema Browser to the upper left corner of bulletin board you have already created.

Place them in the upper left corner. Leave room to add the account information later.

• Select the Accounts table from the Tables list on the Schema Browser.
• Select the Name, Address, City, State, and Zip fields from the Accounts table.
• Drag all five fields from the Schema Browser to the bulletin board.
• Place them to the right of the invoice number.

If the Accounts information is not aligned with the Invoice, you can move them by selecting all the Accounts widgets and dragging them to a location of your choice. Hold down the Shift key and drag a rectangle with MB1 around all the Account fields to select all the widgets. Move them together by holding down the Ctrl key, positioning the pointer over one of the selected widgets, and dragging MB1 until the group is properly aligned.

When selecting a group of fields, if you inadvertently select the background container widget, you cannot move the group of widgets. Deselect the container by holding down Ctrl and click MB1on the container (the easiest way is to deselect the container object on theBrowser hierarchy). The other objects in the group remain selected, and you can now move the group.

Setting Resources

To display and set the necessary resources for this application:

• In the Browser, select q_Invoice (the query widget for the invoice fields) by clicking MB1 on the q_Invoice widget in the Browser tree.
• To display its resources, update the Resource Editor window using one of the following methods:

  • Click on the Update icon located in the tool bar below the Resource Editor menu bar.
  • Double-click on the widget to list the widget's resources and their settings in a scrollable window at the bottom of the Resource Editor.

Note: All database-related resources are grouped at the end of the list to help differentiate them from the appearance-setting resources.

• Find the tables resource, and enter "from" in the Find Resource field at the bottom of the Resource Editor.

Note: This resource was set automatically to both the Invoice and Accounts table when you dragged fields from both tables into the application. Although it is not necessary to supply the value, you must use qualified names in the data fields XmNexpression resources to allow DBPak to determine from which tables to obtain data.

• Find the where resource:

where Resource Edited to Create the Join

• Modify the where resource on the query (q_Invoice) to create the join. The correct value is: "Invoice.Account_Number = Accounts.Account_Number".

After typing the value into the text input field, be sure to either press Enter or click on the OK button that is displayed to the right of the input field.

The text entered into the where resource is copied into the where clause that is part of the SQL statement that is constructed and sent to the database server. By specifying the join conditions, the server will return data from both the Invoice table and the Accounts table, where the Invoice table's account number matches the Accounts table account number.

• In the Browser, select:

accounts_name

• Update the XmNqbeOperator resource to XiDB_QBE_USER_DEFINED. Refer to "XmNqbeOperator" in DX Hyperhelp for more information.
• To complete the interface, add a control panel at the bottom of the form.

Drag the Control Panel from the DBPak Controls group on the Palette. If you have any questions about how to do this, see Adding a Control Panel to the Application.

Testing the Interface

Refer to Testing the Application in Play Mode for information about testing your interface.

When you enter Play Mode, you can use the control panel widgets to navigate through the data. Start by pressing Search, and notice that the Account information updates together with the Invoice information. The where clause keeps them synchronized.

Using Query by Example (QBE)

You can restrict the set of values shown when browsing through displayed data by using a technique called Query by Example.

Suppose you are looking for an invoice from a customer, but you have forgotten the customer's name. You know the customer's name begins with the letter "R".

To find the customer's name using QBE, use the following steps:

• Enter Play Mode.
• If necessary, click the Stop button on the control panel to enter QBE Mode.
• Enter "R%" into the Name field.

By default, the interface is in QBE Mode. If you enter data into any of the data fields and then initiate a search, that data will be interpreted as search strings to restrict the rows that will be returned by the server.

The "R%" represents all names that have an R as the first character and any number of other characters following. The percent character (%) is an SQL wild-card character that matches any string.

DBPak contains many capabilities for controlling database queries. For example, if you want this field to always search for values that begin with what was entered (without the user being required to enter the% operator), then you can set the Data Presentation widgets's XmNqbeOperator resource to XiDB_QBE_BEGINS_WITH. Refer to "XmNqbeOperator"in DX Hyperhelp.

• Click on the Search button on the control panel.

You can now browse through all the invoices restricted to those having a customer name starting with the letter "R".

You can change the search parameters by clicking on the Stop button in the control panel. This places the interface back in QBE mode and you can then enter a new search string.

Writing Out and Saving the UIL File

You will build upon this newly created interface in Tutorial 3: Master-detail Display and Entry Form Application. Write out the .uilfile for the interface and save it so you can use it for Tutorial 3 at your leisure.

Writing and saving the .uil file

First, make a directory in which to save tutorial .uil files. The following steps describe how to write and save the .uil file for an interface.

• Return to Build Mode.
• Select Save As... from the File menu on the Browser to display the File Selection dialog.

Note: The default path displayed at the top of the dialog is the path of the directory from which you started Database Xcessory.

• Change the directory path in the Filter input field at the top of the File Selection dialog.

Either enter the full path name of the directory in which you want to save the .uil file or use the list of current directory contents to navigate to the desired directory.

• Click on the Filter button at the bottom of the File Selection dialog.
• Enter the name of the .uil file you are writing out in the File Name input field at the bottom of the File Selection dialog.
• Click on the OK button to write out the file uil.uil, which can be read back into Database Xcessory to reconstruct the collection.

Summary of Tutorial 2

You created an interface with two tables joined together by setting a single resource on the query widget. Database Xcessory did the rest automatically. When you press the Search button, the Query widget (to which the Search button points) builds an SQL selectstatement by:

• Finding all Data Presentation widgets (in this case, data fields) and using their expression values to create the expression list of the select statement.
• Using the XmNfrom resource and any table qualifications in the Data Presentation widgets to build the from clause in theselect statement.
• Taking the where resource (and other query resources) to build the remainder of the select statement.

The query widget then sends the select statement to the server.

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.

In this tutorial you will build upon the previous application to create an order display and entry form. This will allow you to browse through all the invoices in the database and see the items ordered for each invoice in a table along with the order information. In a master-detail relationship, the master (or driving table) represents the "one" side of a one-to-many relationship, and the detail table is on the "many" side.

In this case, the InvoiceDetail table is related to the Invoice table by the Invoice_Number field. Although the Invoice_Number is enough to relate the InvoiceDetail records to the correct invoice, the Line field, a line number, is required to ensure that each InvoiceDetail record is uniquely specified when updating the table. Both of these fields make up the key for the InvoiceDetail table.

If you saved the .uil file for the Tutorial 2 interface, select Open or Read from the Browser File menu and select the file from the File Selection dialog that is displayed. The interface should look like:

Simple Join Application You Created in Tutorial 2

Creating the Application

Oracle users

• Because Oracle is case-insensitive, the Schema Browser renders all names uniformly in uppercase.

Adding a table

Use the following steps to add a table to the application shown above.

• Resize the bulletin board so that there is space below the control panel to add a table (about 2 inches high by the width of the existing interface), as shown:

Existing Interface Resized

• Select InvoiceDetail from the Tables list in the Schema Browser.
• Select Table from the Object Type option menu, as shown:

Option Menu Items

Select all columns displayed in the Columns window (because the columns are contiguous, drag through all the columns to select them all at once).

While holding MB2 over the selected columns, drag and drop them onto the empty space on the bulletin board below the Control Panel and on the left side of the bulletin board. Because you selected Table from the Object Type option menu, all the columns you dragged and dropped onto the bulletin board are placed in a table.

Table Added to Application

Setting Resources for the Table

It is necessary to specify a where clause that retrieves all of the Invoice_Detail records for the invoice number currently displayed in the Invoice Number field on the application.

Specifying a where clause

To specify a where clause, use the @value() function within a "where" clause:

1. Locate the dbTable table widget on the Browser, using the Panner or the Select search field.

Table Widget Selected

• Update the Resource Editor by double-clicking on the dbTable widget in the Browser.
• Locate the where resource in the Resource Editor.
• In the input field of the where resource, enter the following: "InvoiceDetail.Invoice_Number = @value(invoice_Invoice_Number)".

This creates a relationship between the dbTable widget and the invoice_Invoice_Number widget. When the value ofinvoice_Invoice_Number changes, it is substituted into the "where" clause of dbTable and the query is re-evaluated.

• Switch to Play Mode.
• Press Search. As you move through the records, notice that the table updates to show the invoices associated with the account.

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.

Most of the time you will probably use textual data fields (such as XiDBDataField) to allow the user to enter data into your application, but sometimes you may want to present the user with a set of choices.

In this tutorial you will create an application that allows the user to choose one salesperson from a list of all salespeople and displays all of that salesperson's sales (invoices) in a table. We'll use an XiDBCombinationBox for the one-of-many choice.

Creating the Application

Oracle user

• Because Oracle is case-insensitive, the Schema Browser renders all names uniformly in uppercase.

Use the following steps to create the application:

• Create a bulletin board about 6 inches wide by 3 inches high.
• Select Combo Box from the Object Type option menu on the Schema Browser.
• Select Sales from the Tables list on the Schema Browser.
• Select the Employee_Id column from the Columns window on the Schema Browser.
• Using MB2, drag and drop the Employee_Id column onto the bulletin board. Your interface should now look like the figure shown below.

One Column Added as a Combination Box to the Interface

Setting Resources for the Combination Box

Enumerated widgets retrieve their choices from their XmNmap resource, in a way that is determined by the value of the XmNmapType resource. You will set XmNmapType to XiDB_MAP_SQL, which says that the XmNmap specifies an SQL statement that lists the choices.

• Locate the combination box widget (sales_Employee_Id) on the Browser hierarchy.
• Double-click on the sales_Employee_Id widget to update the Resource Editor.
• Find the map resource, located directly above the mapType resource.
• Set the map resource to "select Sales.Employee_Id,Sales.Name from Sales" (without the quotes).

The default setting for the map resource is XmNmap_Unset. The map resource has a text input field, so double-click on the default value and delete it to clear the input field.

The value of the widget is the first column--in this case, the employee ID. By specifying a second column, we are saying that the value in the second column (the name of the salesperson) should be displayed in the widget and then mapped to the first column value (for example, Employee_Id) if it is selected. We're doing this here so that we can select by name, but then use the corresponding Employee_Id in a join.

• Find the mapType resource in the Resource Editor.

This resource should be located directly below the map resource.

• Change the resource setting to XiDB_MAP_SQL.

By default, this resource is set to XiDB_MAP_STRING. Instead of a text input field, this resource presents an option menu of settings. Select XiDB_MAP_SQL from the option menu items.

All Possible Settings Displayed for mapType Resource

• Find the visibleItemCount resource.

By default, when you click on the arrow of the combination box you have created, only one choice will be displayed. The visibleItemCount resource determines how many items will be displayed when you click on the arrow.

• Enter "4" in the visibleItemCount input field.

The next step is to create the table.

Creating the Table

• Select Invoice from the Tables list in the Schema Browser.
• Select Table from the Object Type option menu in the Schema Browser. Drag the mouse through the Columns window to select all the columns.
• Drag and drop all the selected columns onto the bulletin board, below the combination box.

A table of the selected Invoice columns is created on your interface.

Although the combination box is connected to a query, you will not use that query in the search. The table acts as the query for the purposes of searching.

All Columns from Invoice Table Added to Interface as a Table

• Increase the height of the table by resizing the table, as you would any widget.

Either click on the dbTable object on the Browser hierarchy, or click on the table on the interface to select it.

Or use the mouse to resize the table. For example, use MB1 to drag the bottom edge down. The purpose of resizing the table is to display more data at once.

The table should now look like:

Resized Table

Setting Resources for the Table

Once you have created the table, the next step is to set resources for it:

• Find the table dbTable on the Browser hierarchy.
• Double-click on dbTable to both select it and update the Resource Editor.
• Find the where resource.
• In the where input field, enter "Invoice.Employee_Id = @value(sales_Employee_Id)" to set the where clause to retrieve only those invoices that correspond to the currently selected salesperson.

Testing the Application

You can now go into Play Mode and test the application:

• Click on the arrow button of the combination box to display the following three choices:

Combination Box Choices Displayed

Select any customer name from the list. Note that the information now displayed in the table, as shown below:

Information Associated with Customer Name Displayed in Table

In the previous tutorials you have created interfaces that presented information in the form of labelled data fields, enumerated widgets, and tables. One of the most exciting features of DBPak is that other presentation styles are available. In this tutorial you will learn how to present sales data graphically using exactly the same techniques as before.

When creating graphs, you will usually want to group the data by some common value. If you think about graphs you've seen, they seldom display actual data. They usually display sums or totals compared to each other. This grouping is accomplished with the XmNgroupBy resource on queries and widgets with query attributes, like the plotter in this example.

Suppose that you want to find out which items in your company's inventory are selling and which items are just taking up shelf space. One way of presenting the data is through a column of numbers, but it would be hard to find the "interesting" data. By creating a graph, however, those interesting values would become very obvious.

You will create a bar graph of sales for each item in your inventory with one bar per item. Individual item sales can be found in the InvoiceDetail table, while the actual descriptions of the items are in the Inventory table.

Creating the Plotter Widget

• Start by creating a new application by selecting New from the Browser File menu.
• Select the OrderEntry database in the Schema Browser.
• Select the Inventory table in the Tables list in the Schema Browser.
• Select Graph Types... Bar Plot in the Object Type option menu in the Schema Browser.
• Select the Description field from the Columns list.
• Drag the Description field and drop it onto the Browser.

Notice that Database Xcessory created a XiDBPlotter widget along with the other familiar basic widgets, such as the Control Panel.

The Plotter widget is a container that holds various plots and axis widgets.

If you select Show Compound Children from the Browser View menu, you can see the axis widgets (xaxis and yaxis) that were automatically created, as shown below:

Compound Children of dbPlotter Widget

Database Xcessory sets the plotter's xColExpression resource to Inventory.Description. Those values will label the x axis when you switch to Play Mode to test the interface.

dbPlotter Display on Your Interface Before Entering Play Mode

Creating the Bar Plot

• Select the InvoiceDetail table from the Tables list in the Schema Browser. The InvoiceDetail table contains the detail records for the Invoices in the database. The detail records represent the actual line items in the order forms. There is one detail record for each line that appears on an invoice. The InvoiceDetail table contains the number of each item ordered on that invoice and the price per item.
• Select Price from the Columns list.
• Drag the Price field onto the dbPlotter object in the Browser hierarchy.

Alternatively, you may drag the Price field onto the Plotter widget in the interface. XiDBBarPlot is now created as a child of the plotter widget, shown below:

Bar Plot (invoiceDetail_Price) is Child of Plotter Widget (dbPlotter)

The Plotter is a container widget that has x-axis and y-axis compound children. When you add a plot widget (such as a Bar Plot) to the Plotter, you are adding just the plot and a set of y values.

You may be wondering why a Plotter widget was created the first time, and a Bar Plot widget is created this time. Database Xcessory notices that you are dragging the field into an existing Plotter widget and it created a BarPlot. Each new plot dragged onto the Plotter widget will be added as a child and another set of y values. You can mix plot types in one plotter container.

How Interface Appears Before Entering Play Mode

Setting Resources for Sum

• Select invoiceDetail_Price (the XiDBBarPlot) in the Browser if it's not already selected.
• Press the Update button in the Resource Editor window to update the resources for the BarPlot widget.
• Change the expression resource from "InvoiceDetail.Price" to "sum(InvoiceDetail.Price * InvoiceDetail.Quantity)".

We want the value of all like items on all invoices, and not just the price for a single unit. We have to take into account any lines in which a person ordered more than one item. This expression is added to the SQL select statement that is generated and sent to the server. The server performs the calculation, and returns a single value to be used by the bar plot.

• Select the dbPlotter widget in the Browser.
• Press Update in the Resource Editor window.
• Set the where resource to: 
  "Inventory.Part_Number = InvoiceDetail.Part_Number".

This joins the Inventory table to the InvoiceDetail table. By setting the where resource, you can then use values from both tables with a single query. When the data is fetched from the database, both tables will be consulted.

Creating the Groups

• Edit the groupBy resource to "Inventory.Description." In this case we need to group the data by inventory item. That is, we are asking the database to give us the sum of the money spent on all orders, grouped by inventory part.

All resources from the Plotter and the BarPlot are pulled together to create an SQL select statement to gather data for the graph. The statement sent looks like the following:

SELECT Inventory.Description,
sum(InvoiceDetail.Price * InvoiceDetail.Quantity)
FROM Inventory, InvoiceDetail
WHERE Inventory.Part_Number = InvoiceDetail.Part_Number
GROUP BY Inventory.Description

When reviewing these steps you can see how this statement was constructed: the select expressions came from the dbPlotter and dbBarPlot expression resources, the where clause came from the where resource on the dbPlotter, and the group by clause came from the groupBy resource on the dbPlotter.

• Go into Play Mode to test the interface.

When you press the Search button, the data is fetched and the graph is displayed, as shown.

Interface In Play Mode

You must make the window very wide so that the inventory descriptions aren't displayed on top of each other.

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.

Setting Up Tutorials

Tutorial 1: Simple Forms-Based Application

Tutorial 2: Simple Join Application

Tutorial 3: Master-detail Display and Entry Form Application

Tutorial 4: Handling Multiple Choices with Enumerated Widgets

Tutorial 5: Graphs Application

Tutorial 6: Using Stored Procedures