Getting Started Building a Client/Server Application

In this lesson you add a third sheet window to the main tutorial application. You create and call a function to perform a routine operation (calculate a percentage) on values returned from embedded SQL commands and a value selected by the application user from a drop-down list box control.

The prototype for the function you create throws user-defined exceptions. You call the function in a TRY-CATCH block inside the Clicked event on a command button control. The CATCH clauses in the Clicked event catch user-defined exceptions thrown by the new function as well as a system exception thrown up the application call stack.

You use the new sheet window to calculate the percentage of customers that resides in a selected state. The controls you add to the new sheet window are:

Two static text boxes that you change programmatically to display read-only results
A command button to call a function that calculates percentages
A drop-down list box for a list of states where customers reside
A text box that displays the percentage of customers residing in the state that application users select from the drop-down list box

To add a sheet window to the existing application, you must:

Create the sheet window

You inherit the sheet window from the w_pbtutor_basesheet window. This is the base class for sheet windows that you generated with the Template Application wizard. You do not use the w_master_detail_ancestor extension layer window, since the modifications you made to it are not useful in the new sheet window.

Select File>Inherit from the PowerBuilder menu.

Make sure the Objects of Type box displays Windows.

Select w_pbtutor_basesheet from the available windows in the pbtutor.pbl library and click OK.
Make sure the Layout view displays in the Window painter.

Select Insert>Control>StaticText and click near the top left corner of the Layout view.
In the Properties view, highlight the default text in the Text text box and type the following:
```
1. Select or type a state code in drop-down list:
```
Lengthen the control width and the width of the sheet window to display the entire text and allow room for a drop-down list control at the top right of the window.

A length of 2250 should be sufficient for the sheet window width. You can set this on the Other tab of the Properties view for the window, or you can drag the window edge in the Layout view to make room for an additional control.
Right-click the static text control in the Layout view and click Duplicate from the pop-up menu.

In the Properties view, highlight the default text in the Text text box of the new static text control and type the following:
```
2. Click Percentage button
```
Select Insert>Control>DropDownListBox and click to the right of the static text boxes near the top right corner of the Layout view.
In the Properties view for the drop-down list box, type ddlb_state for the control name.

Select the AllowEdit and the VScrollBar check boxes.
Click the CommandButton button in the painter bar and click below the two static text boxes.

In the Properties view, type cb_percent for the button name and type Percentage for the button text.
Select Insert>Control>SingleLineEdit and click below the command button in the Layout view.

In the Properties view, type sle_result for the control name and type the following for the control text:
```
Text box for percent of customers in the selected state
```
Lengthen the control width to display the entire text.
Make sure no control is selected and the sheet window properties are displayed in the Properties view.

Type Customer Location for the Tag property.

The text you typed will be visible in the sheet window title at runtime. Code in the basesheet ue_postopen event assigns the Tag text to the sheet window title.
Select File>Save from the PowerBuilder menu.

Select pbtutor.pbl for the application library, type w_cust_pct for the new sheet window name, and click OK.

This saves the new sheet window with all its controls to the main tutorial library.

Provide access to the sheet window from the main application frame

You must register the new sheet with the sheet manager.

Double-click w_pbtutor_frame in the System Tree.
If the ue_postopen event is not visible in the Script view, click the Event List tab and double-click ue_postopen.

The ue_postopen event script displays in the Script view.
Edit the list of sheets to add your new window. The following entry should be a single line:
```
string ls_sheets[] = { "w_customers", "w_products", "w_cust_pct" }
```
Edit the display list to add a label for your new window. The following entry should be a single line:
```
string ls_display[] = { "Maintain Customers", "Maintain Products", "Customer Location" }
```
Select File>Save and close the w_pbtutor_frame window.

The next time you run the pbtutor application, you should be able to open the new sheet window from the File menu of the main frame window. Although you can now run the new sheet window from the development environment, you must make sure that you can run it from a compiled application as well.

For this purpose, you reference the sheet windows as window objects in the sheet manager of_registersheet script. The reference is necessary for the compiler to know that this object is used in the application so that it will include it in the executable.

You create a compiled application in Preparing the Application for Deployment
Double-click n_pbtutor_sheetmanager in the System Tree.
Click the Function List tab and double-click of_registersheet.

The script for the of_registersheet function displays in the Script view.
Enter the following after the lines declaring sheet window variables for the w_customers and w_products windows:
```
w_cust_pct lw_sheet3
```
Save and close the n_pbtutor_sheetmanager user object.

Create user-defined exception objects

Where you are

> Create user-defined exception objects

Now you create two user-defined exception objects that you will throw from a function that is invoked when the user clicks the command button on the w_cust_pct window. You also create a user-defined exception object that you throw from a user event on the drop-down list box control that you added to the w_cust_pct window.

Select File>New from the PowerBuilder menu and click the PBObject tab in the New dialog box.
Select the Standard Class icon and click OK.

Select throwable from the Types list box and click OK.

The new exception object displays in the User Object painter.
In the Text box of the Properties view, type the following:
```
No rows were returned from the database. If you typed or selected a state code in the drop-down list box and the database connection has not been closed, either the state you entered has no customers or you entered the state code incorrectly. 
```
The exception object has get and set methods for handling the Text property. Here you set the text property directly in the user interface.
Click outside the Properties view to enable the Save button.

Select File>Save, select the pbtutor.pbl application library, and type exc_no_rows in the User Objects box for the new exception object name, and click OK.
Select File>Close.

Repeat steps 1-5 using the following values for the Text property and the exception object name:

Property	Value
Text	Percentage too low. Only one customer in this state. Notify regional sales manager...
Exception object name	exc_low_number

Repeat steps 1-5 using the following values for the Text property and the exception object name:

Property	Value
Text	You must use the two-letter postal code for the state name.
Exception object name	exc_bad_entry

Create a new user function and user event

Where you are

> Create a new user function and user event

Now you add a function that you invoke from the Percentage command button's Clicked event and an event that is triggered when the focus is changed from the drop-down list box on the w_cust_pct window. The function calculates the percentage of customers living in a particular state. The event processes the current value of the drop-down list box control to make sure it is two characters in length (for the state code).

Open w_cust_pct in the Window painter if it is not already open.

Select (Functions) in the first list box in the Script view.

The Script view displays the Prototype window. The first drop-down list box in the Script view displays (Functions) and the second drop-down list box displays (New Function).
Select decimal for the Return Type and type uf_percentage for Function Name.
Select integer for the Argument Type and type ai_custbystate for the Argument Name.

You will add a second argument in the next step.
Right-click anywhere in the Prototype window and select Add Parameter from the pop-up menu.
Select integer for the second Argument Type and type ai_totalcust for the second Argument Name.
Type exc_no_rows,exc_low_number in the Throws box.

Enter the following script for the new function:

Decimal my_result
exc_no_rows  le_nr
exc_low_number le_ex
/* Process two integers passed as parameters. Instantiate and throw exceptions 
if the first integer value is 0 or 1. Otherwise calculate a percentage and 
return a numeric value truncated to a single decimal place. If the second 
integer value is 0, catch and rethrow the runtime dividebyzero error during 
the calculation. 
*/
CHOOSE CASE ai_custbystate
   CASE 0
      le_nr = create exc_no_rows
      throw le_nr      
   CASE 1
      le_ex = create exc_low_number
      throw le_ex
   CASE ELSE
      TRY
         my_result=(ai_custbystate/ai_totalcust)*100
      CATCH (dividebyzeroerror le_zero)
         throw le_zero
      End TRY   
END CHOOSE
return truncate(my_result,1)

Later in this tutorial, you will call the uf_percentage function from the Clicked event on the command button, passing in two integers and processing the return value.

You now add a user event for the drop-down list box that throws the exc_bad_entry exception object when a user-entered state code is not exactly two characters in length.

Select ddlb_state in the first drop-down list box of the Script view and select (New Event) in the second drop-down list box.
Select integer for Return Type and type ue_modified for Event Name.

Select string for Argument Type and type as_statecode for Argument Name.

Type exc_bad_entry in the Throws box or drag it from the System Tree to the Throws box.

Note that the Event ID is (None). You do not select an Event ID for the ue_modified event. If you selected an Event ID, you could not enter user-defined exception objects in the event Throws clause.

Enter the following script for the new ue_modified event:

exc_bad_entry  le_ex
//Make sure the current text in the drop-down list 
//box is two characters in length. Otherwise, 
//instantiate the exc_bad_entry exception object and 
//throw the exception.
IF len(this.text)<>2 Then
      le_ex = create exc_bad_entry
      throw le_ex
END IF
Return 1

Next you call the ue_modified event and the uf_percentage function, and catch the exceptions thrown by these methods.

Call the methods and catch the exceptions

Where you are

> Call the methods and catch the exceptions

You now write code to populate the drop-down list box controls with state codes from the customer table in the Demo Database database. Since you made the control editable, an application user can also type in a value for the state code. Before you process a user-entered value, you check to make sure the value conforms to the conditions you set in the ue_modified event, namely that it is two characters in length.

You also add code to the Clicked event of the command button control to process the current state code in the drop-down list box control. In the Clicked event you call the uf_percentage function to calculate the percentage of customers from the selected state and catch all exceptions that can be thrown by the function.

Make sure the w_cust_pct is open in the Window painter and that ddlb_state displays in the first drop-down list box of the Script view.
Select losefocus ( ) returns long [pbm_cbnkillfocus] in the second drop-down list box.
Call the ue_modified event and catch the exception object that it throws by entering the following lines for the losefocus event script:
```
Try 
      this.EVENT ue_modified(this.text)
Catch (exc_bad_entry le_be)
      messagebox ("from exc_bad_entry", &             
         le_be.getmessage())
End Try

return 1
```
Select constructor ( ) returns long [pbm_constructor] from the second drop-down list box in the Script view prototype window for the ddlb_state control.

Enter the following lines in the Constructor event to populate the drop-down list box control:

int       li_nrows, n
string       ls_state

//Get the distinct count of all states in the 
//customer table
SELECT count(distinct state) INTO :li_nrows 
FROM customer;

//Declare the SQL cursor to select all states
//in customer table but avoid
//rows with duplicate values for state.
DECLARE custstatecursor CURSOR FOR 
SELECT state FROM customer
GROUP BY state HAVING count(state)=1
UNION 
SELECT state FROM customer
GROUP BY state
HAVING count(state)>1;
OPEN custstatecursor ;
//Populate the control with a single entry for
//every state in the customer table.
FOR n=1 TO li_nrows
      FETCH NEXT custstatecursor INTO :ls_state;            
      this.additem( ls_state)
NEXT
CLOSE custstatecursor ;
//Set first item in list as selected item
this.selectitem (1)

Select cb_percent from the first drop-down list in the Script view.

Make sure clicked ( ) returns long [pbm_bnclicked] displays in the second drop-down list box.

Enter the following lines for the Clicked event script:

Decimal my_result 
Double entry_1, entry_2
Int li_int, li_rtn
String sel_state

sel_state=ddlb_state.text
//Get the number of rows with customers from the 
//selected states and place in the entry_1 variable.
//Change the first static control to display this
//number.

SELECT count(*) INTO :entry_1 FROM customer
      WHERE customer.state=:sel_state;
st_1.text="Customers in state: " + string(entry_1)

//Get the total number of customers and place in 
//the entry_2 variable.
//Change the second static control to display this 
//number.
SELECT count(*) INTO :entry_2 FROM customer;
st_2.text="Total number of customers: " &
      + string(entry_2)

//Call uf_percentage and catch its exceptions.
TRY 
      my_result = uf_percentage (entry_1, entry_2)
CATCH (exc_no_rows e_nr )
      MessageBox("From exc_no_rows",       &
         e_nr.getmessage())
CATCH (exc_low_number e_ln )
      li_int=1 
      MessageBox("From exc_low_number", &             
         e_ln.getmessage())
   CATCH (dividebyzeroerror e_zero)
      li_rtn = MessageBox("No Customers", &
         "Terminate Application?", Stopsign!, YesNo!)
      IF li_rtn=1 THEN
         HALT
      END IF
END TRY

//Display the message in the text box. Vary the //message depending on whether there is only one 
//customer for the selected state or if more than 
//one customer resides in selected state.
IF li_int=1 THEN
      sle_result.text ="Value not calculated for " & 
         + sel_state + "."    + " Try another state."
ELSE
      sle_result.text = String (my_result) + &
         " % of customers are in " + sel_state + "."
END IF

Run the application

Test the new sheet window
Add a test for the divide-by-zero error

Where you are