Getting Started-2-Tier

You can embed special processing in your application screens with Panther's scripting language JPL. Because the JPL is saved with the screen, it is accessible to the screen and its widgets.

In this lesson you learn how to:

• Attach JPL procedures to screens: one procedure to send data and the other to receive the transmitted data.

• Implement error handling by displaying a message to the user.

• Use the DB Interactions window to view database objects and their relationships on your application screens, and to gain access to their properties.

• Write transaction manager hook functions that automatically generate a distributor ID number when a distributor is added to the database.

This lesson shows how to use the JPL editor and describes its capabilities. The JPL procedures that you need are already written and stored in tutorstd.lib. The following diagram illustrates the JPL procedures you will enter in this lesson.

1. If necessary:

   • Invoke the editor and connect to the database.

   • Reopen client screen dstslect.scr from the client.lib library.
     

Write a JPL procedure that is called when a user chooses the New or Orders button on the dstslect.scr client screen. Both buttons call a JPL procedure that opens the Distributor Orders screen; each button passes to this procedure a different parameter (NEW or SELECT), which determines whether the screen is used to add a new distributor to the database, or view orders of an existing distributor.

Because the same procedure is called by two different screen objects, make the procedure available to both by attaching the JPL code to the screen's JPL Procedures property.

2. Select the dstslect.scr client screen—click on an empty area in the screen to deselect all widgets.

   The Properties window displays screen properties.

3. Under Focus, select the JPL Procedures property.

   Panther opens the edit window for this screen's JPL:

   

   The screen entry procedure enter_screen, produced by the screen wizard, is already attached to the screen's JPL. It calls the public command, which makes the code in the smwizard.jpl module available to all application screens. Leave the wizard JPL code unchanged and add new procedures to this module.

4. Position the cursor a line or two below the return line of the enter_screen procedure. Insert the JPL procedure send_dst_data, which is stored in tutorstd.lib:

   • Choose EditInsert From Library. The Open JPL Module dialog opens.

   • If necessary, open tutorstd.lib by choosing Open and selecting it from the Open Library dialog.

   • From the Open JPL Module dialog, select tutorstd.lib from the list of open libraries, and select send_dst.jpl from the list of members. Choose OK.
     

     The send_dst_data procedure is read into the JPL edit window:

     proc send_dst_data(cmd)
     {
     	vars occ
     	if (cmd == "NEW")
     	{
     		send DATA cmd, ""
     	}
     	else
     	{
     		occ= Master->grid_current_occ
     		if (distrib_id[occ] == "")
     		{
     			msg emsg "First select a distributor."
     			return 1
     		}
     		else
     		{
     			send DATA cmd, distrib_id[occ]
     		}
     	}
     	call sm_jwindow("(+5,+5)dstord.scr")
     	return 0
     }

More About the send_dst_data Procedure

The send_dst_data procedure is called when a user chooses either the New button or Orders button on the Select Distributor (dstslect.scr) screen. Each button supplies a different string argument to the procedure's cmd parameter, which controls how the procedure executes:

• If New is chosen, NEW is supplied as an argument. The send command puts this string into a specialized global buffer, or bundle. Bundle data is accessible to any other screen through the receive command.

• If the Orders button is chosen, SELECT is supplied as an argument. In this case, the send command puts two items into the bundle: the SELECT string, and the distributor ID in the selected distributor record. To ensure that a distributor ID is supplied, the procedure checks whether a record is selected from the grid, and issues an error message if one is not.

In both cases, the dstord.scr screen is opened through the call to sm_jwindow, which opens the Distributor Orders (dstord.scr) screen at the specified (+5,+5) position: five grid units right and five grid units down from the calling screen.

5. Choose Apply to save the procedure.
6. Choose FileSave to save the screen.

More About the JPL Edit Window

You can write and edit JPL procedures within Panther's own edit window. This window offers access to JPL that is attached to a screen or stored in a library. You can also use any text editor that your system offers and that is specified in the application variable SMEDITOR-for example, Notepad on Windows or vi on UNIX. To access this editor:

• Bypass the JPL edit window and go directly to the system editor by choosing OptionsDirect to External Editor.

• Invoke the system editor from the JPL edit window by choosing Editor.

When you exit the system editor, you resume in the JPL edit window, where you save your changes back to its source by choosing Apply or OK. You can also save library JPL modules to a new library member by choosing Save As.

To save the current module's contents to a disk file, choose FileSave AsASCII Text File. Use this option in order to print JPL code from your system, or to send it as email.

You can insert the contents of other JPL modules into the current one: choose EditInsert From Library to insert from a library module, or EditRead File to read from a disk file. Panther inserts the file's contents at the cursor's current position.

The send_dst_data procedure sends data that is needed by the dstord.scr screen. Now you must write a screen entry procedure for dstord.scr that captures this data and uses it to determine the screen's behavior when it is opened by the dstslect.scr screen's New or Orders buttons.

7. If necessary, reopen the dstord.scr screen from client.lib.
   

8. Select the dstord.scr client screen by clicking in an empty space within its borders, so no widgets are selected.
9. Under Focus, choose JPL Procedures.

   The JPL edit window opens and displays screen wizard-generated comments and code. Replace this with code that is in tutorstd.lib.

10. Comment out the original enter_screen procedure by placing // at the beginning of each of the three lines.
    

11. Position the cursor a line or two below the return line of the commented procedure.
12. Choose EditInsert From Library.
13. From the Open JPL Module dialog, select se_dst.jpl from tutorstd.lib. Choose OK.
    

    The enter_screen procedure is read into the JPL edit window:

    proc enter_screen (screen_name, context)
    {
    	public smwizard.jpl
    	vars cmd
    
    	if ((context & K_EXPOSE) || !(sm_is_bundle("")))
    	{
    		return 0
    	}
    	else
    	{
    		receive DATA cmd, distrib_id
    		if (cmd == "NEW")
    		{
    			LDistrib_id->hidden=PV_YES
    			distrib_id->hidden=PV_YES
    		}
    		call sm_tm_command(cmd)
    		return 0
    	}
    }

More About the enter_screen Procedure

The enter_screen procedure is invoked as the dstord.scr screen's entry procedure. First, the public command makes the wizard-generated JPL module smwizard.jpl available to the application. Next, the procedure determines whether a user can add a distributor record or edit an existing one:

• The first if statement checks for two conditions: the screen is already open and is reexposed—context & K_EXPOSE; or no data was sent from the dstslect.scr screen—!(sm_is_bundle("")). If either condition is true, the procedure returns.

• If both if conditions are false, the receive command captures the bundle data sent from the dstslect.scr screen. If data is received for distrib_id, it is placed in that widget.

• An if statement tests the value of cmd. If it evaluates to the string NEW, text widget distrib_id and its label LDistrib_id are hidden. sm_tm_command passes the value of cmd to call the appropriate transaction manager command, NEW or SELECT. If the NEW command is called, it prepares the screen to accept new data. If the SELECT command is called, it fetches the appropriate data using the value in distrib_id.

14. Save the procedure by choosing Apply.

More About Sharing Information Across Screens

The send and receive commands let you transfer data between screens in an application. The send command specifies the data items to send and stores them in a buffer. When the target screen executes the corresponding receive command, the buffered data is retrieved.

Panther provides several ways for screens to access each other's data:

• JPL can directly reference variables and widgets on other open screens with the syntax convention screen_name!widget_name.

• JPL global variables, created with the global command, can store data that is accessible to the entire application.

• Local Database Blocks (LDB) contain widgets whose values are automatically written to same-named widgets on a screen when it opens or regains focus, and which read the values of those widgets when the screen exits.

For more information about using send and receive commands and LDBs, refer to Chapter 25, "Moving Data Between Screens," in the Application Development Guide.

The JPL that you have so far written opens the dstord.scr screen when a user chooses the New button on the dstslect.scr (Select Distributor) screen, and opens dstord.scr (Distributor Orders) in insert mode. You can now write a JPL hook function for dstord.scr that generates a unique distributor ID number. This hook function executes during transaction manager processing and supplements the default transaction model.

15. Position the cursor at the very end of the JPL file for the dstord.scr to start a new procedure.
16. Choose EditInsert From Library.
17. From the Open JPL Module dialog, select evnt_svr.jpl from tutorstd.lib and choose OK.

    The tm_events procedure is read into the JPL edit window:

    proc tm_events (event_id)
    {
    	if ((event_id == TM_INSERT_EXEC) && (distrib_id == ""))
    	{
    		DBMS ALIAS distrib_id
    		DBMS QUERY SELECT MAX(distrib_id)+1 from distributors
    		DBMS ALIAS // Clears prior aliasing
    	}
    	else if (event_id == TM_POST_SAVE)
    	{
    		distrib_id->hidden=PV_NO
    		LDistrib_id->hidden=PV_NO
    	}
    	return TM_PROCEED
    }

More About the tm_events Procedure

The tm_events procedure checks whether an Insert transaction manager event occurred and the distributor ID is blank. If both conditions are true, the first DBMS ALIAS command ensures that the database value from the DBMS QUERY statement is written to the variable distrib_id.

To generate a unique identification number, the second DBMS statement specifies a SELECT to find the highest distributor ID in the distributors database table and increments that number by one.

The hook function then checks whether a transaction manager SAVE command executed and, if so, unhides widgets distrib_id and LDistrib_id by changing their Hidden property to No.

18. Choose OK to save the procedure and exit the JPL window.
19. Choose FileSave to save the screen.

More About SQL DBMS Statements

In general, you'll use the transaction manager to automatically generate SQL statements. However, you can also write your own SQL. The Panther DBMS statements let you:

• Declare open connections.

• Manage cursors explicitly.

• Execute SQL statements, stored procedures, RPC calls.

• Execute a variety of database-related tasks.

Refer to Chapter 28, "Writing SQL Statements," in Application Development Guide for more information

When you used the screen wizard to create dstord.scr in Lesson 4, you specified two tables as their data source. When the wizard created these screens, it copied an invisible table view widget from each table's corresponding repository screen, distributors and orders. Each table view widget contains information about its repository entry's source table; it is created automatically when you import the table into the repository (Lesson 3).

Also, because dstord.scr is based on two tables, it also contains a link widget that specifies their join relationship. Like table widgets, link widgets are automatically created during the import process for each foreign key that is defined for a database table. When the screen wizard creates a screen with multiple table views, it copies from the repository the links that describe their relationship.

More About Table Views

Table views store the following types of database information:

• Primary key

• List of table columns

• Database attributes such as sort order, distinct, etc.

Panther's transaction manager uses the information stored in table view and link widgets to determine the SQL statements to generate for each transaction command.

Also, table views let you easily modify the default transaction manager behavior

At runtime, the transaction manager traverses all screen table views in the order that they are linked, and issues transaction commands to populate the master and detail sections accordingly. If a table view has a hook function attached to it, the transaction manager executes the function when it traverses that table view.

You attach a hook function to a table view through its Function property. In this case, you want to attach a hook function to the distributors table view. You can select a table widget and view its properties through either the Widget List or the DB Interactions window. In this instance, use the DB Interactions window to select the table view widget and access its Function property.

More About the Database Interactions Window

The Database Interactions window displays an interactive, graphical representation of a screen's table view widgets and link widgets. You can view the relationships between parent and child table views and the links that connect them. By selecting the toggle buttons representing these database objects you gain access to their properties.

20. With focus on dstord.scr, choose ViewDB Interactions.

    The DB Interactions window opens, displaying a graphical representation of table views and links on the dstord.scr screen.

    

More About Sequential and Server Links

Link widgets are not visible at runtime, but are visible in the editor so that you can access their properties.

There are two type of links-sequential and server:

• Sequential links (denoted by < in the DB Interaction window) join two tables with a one-to-many relationship. SQL SELECT statements for the parent table view are generated and executed before any SQL statements are generated for the child table view.

• Server links (denoted by = in the DB Interaction window) join two tables with a one-to-one relationship. The database server is used to join the two tables, and a single SQL SELECT statement is generated to retrieve the data.

21. In the DB Interactions window, select the button that represents the distributors table view.

    The Properties window now displays table view properties.

    

22. Under Transaction, set the Function property to tm_events and choose OK.
23. Save all the open screens (press F6).

More About Accessing Properties

All Panther objects and their properties can be accessed and most can be modified programmatically through JPL or C functions. You get or set properties for any screen or widget, or the application itself.

Refer to "Setting Properties Using the Property API" in Chapter 19 of Application Development Guide for information about JPL syntax for identifying screens and widgets, their properties, and property values. For a list of properties and their values, refer to Chapter 1, "Runtime Properties," in Quick Reference.

In Lesson 4, you created the screen and chose web-friendly output, which affects the appearance of the screen when it is called from another screen. To adjust the appearance of the screen for GUI:

24. Bring focus to the dstord.scr screen. Click on an empty area in the screen to deselect all widgets. If the Properties window is not open, double-click on the screen or choose ViewProperties Window.
25. In the Properties window, select the Display heading.
26. Select the Border property.
27. Click on the option menu to display its drop-down list, and select Yes. Press Enter or choose OK.
28. Select the System Menu property and repeat Step 27.
29. Select the Title Bar property and repeat Step 27.
30. Save the dstord.scr screen (press F5).

You now have a working application. You can now test whether the application flow functions as designed.

31. Bring focus to the dstslect.scr client screen.
32. Choose FileTest Mode or .

    The Select Distributor screen opens.

33. Choose .

    All distributors in the vidsales database are listed.

    

34. Choose .

    The Distributor Orders screen opens with all data fields clear. The distributor ID fields are not visible.

    

35. Enter Movie Time in the Distributor field.
36. Choose .

    The distributor is added to the database and the ID is displayed in the Distrib_id field.

    

37. Close the Distributor Orders screen (choose Close from the system menu or press Escape).

    The Select Distributor screen regains focus. If you choose View and then scroll down the list of distributors, notice that the new distributor is now listed. The next lesson shows how to enhance the screen entry procedure on this screen so users can see the latest data changes.

Select one of the distributors and then gain access to all of their orders.

38. Select the row in the grid associated with Distrib_id 3 and choose the Orders button.

    The Distributor Orders screen opens and displays all orders for the selected distributor. The screen entry procedure (enter_screen) on the dstord.scr client screen receives the ID you selected (3) and the SELECT command, and calls sm_tm_command to select the specified database record.

39. When done, return to the editor and save all screens (FileSave All).

    ---

You inserted several screen-level JPL procedures: a pair of procedures that send data from one screen and capture it to another; and two other JPL procedures that tell the transaction manager how to handle database transactions. You did this by including these procedures:

• A procedure that submits a specific command and the appropriate data about a selected distributor to another screen. You attached the procedure to the dstslect.scr client screen so that it is called whenever a user chooses either the Orders or New button. These buttons invoke this procedure via their Control String property, passing as an argument the command to issue. If a NEW command is issued, the dstord.scr client screen opens and is ready to accept new data. If a SELECT command is issued, primary key data for the selected distributor is submitted to the dstord.scr client screen.

• A screen entry procedure for the called screen—the dstord.scr client screen—that receives a NEW or SELECT command and accordingly determines the screen's behavior.

• A transaction manager hook function in JPL that automatically generates a unique ID when a new distributor record is added to the database.

What did you learn?

You learned:

• The JPL edit window provides several ways to attach JPL to screens—inserting a JPL module/procedure from another library, inserting a file from a disk, and typing it directly in the edit window. You can also use your favorite text editor.

• Depending on the application, there are advantages to storing all the JPL procedures at screen-level where they are available to the entire screen and all widgets on it. The procedures can be called from a widget property such as Control String or Entry/Exit properties.

• You can control application behavior and database transactions with JPL procedures.

• You can customize transaction manager behavior with hook functions that the transaction manager calls while it processes database transactions.

• The DB Interactions window offers a graphical representation of a screen's table views and links, and access to their properties.