Chapter 9: Data Access

The previous chapter showed you how the new Windows GUI Application Wizard can provide you with an easy route for accessing and querying any installed database. This chapter describes how you can manipulate this data.

Note: The programs and files that are output as a result of using the Windows GUI Applications Wizard are meant as a starting point for developing your own data access applications. They are not intended to be universally applicable to all situations, but are provided so that you can learn to use the basics. You can then adapt the code provided to suit your own, possibly more complex, needs.

The chapter Creating a Windows GUI Application in the Getting Started on-line book is a tutorial which includes practical details on how to use the wizard.

The Windows GUI Application Wizard

The Wizard process created a new screenset and automatically generated an associated COBOL program configured to the functionality you requested. Both the screenset and associated COBOL program were automatically added to your project.

If you want to access an installed database, you must have selected the OpenESQL Data Access option in Step 3: Class Library Features.

Accessing Installed Databases

There are two possible ways of viewing the results of your query after building your application, depending on the option you chose in Step 4 of the Windows GUI Application Wizard:

List box view.
-or-
Grid view.

These views provide the same functionality but use different interfaces. The grid view has a more direct feel in use; the list box view is more familiar to experienced Windows users.

When you run a query, all of the data for that query is read from the database into memory. Only the page of rows visible on screen is loaded from memory at any one time: if you scroll, the current page is updated by retrieving rows from memory.

The grid view of your single table database query might look like that in Figure 9-1.

Figure 9-1: Grid View of Single Table Database Query

The grid view of your table join database query might look like that in Figure 9-2.

Figure 9-2: Grid View of Table Join Database Query

The list box view of your single table database query might look like that in Figure 9-3.

Figure 9-3: List Box View of Single Table Database Query

The list box view of your table join database query might look like that in Figure 9-4.

Figure 9-4: List Box View of Table Join Database Query

The status bar is at the foot of the screen and gives information about the object that the mouse is over. When the mouse is over the general part of the screen, the status bar text tells you which type of function you have selected. Message boxes give you information about errors, for example, trying to edit a key field.

Manipulating the Data

You can see that the table join query views have only two buttons - New Query and Run Query. This is because table join output is read-only, so the following data manipulation information applies only to single table database queries.

You can perform the following functions:

Edit data in the database.
Insert a new row.
Delete a row.

Edit Data

	Grid View	List View
Change data in a field	Select the field and specify the necessary changes, remembering that you cannot change key fields. If the edited field is in a column which has been sorted, your change may result in the whole row being moved to another part of the display.	Select the row to change and specify the necessary changes in the dialog box which opens. If the edited field is in a column which has been sorted, your change may result in the whole row being moved to another part of the display.
Move to the next column	Use either the mouse or the left and right arrow keys.
Delete data in a field	Select the field and press the Delete key.	Select the row to change and specify the necessary changes in the dialog box which opens. If the edited field is in a column which has been sorted, your change may result in the whole row being moved to another part of the display.
Refresh the screen	Click Run Query.	Click Run Query.

Note: Because concurrent versions of the same query can be open, the application refreshes your query from disk before echoing any edits that you make to the data. This ensures that you see the most up-to-date version of the data.

Insert a New Row

	Grid View	List View
Insert a new row	Click New Row. Enter the new data in the top row of the empty grid.	When you click New Row, you see a dialog box with entry fields for each column except for key fields. There is no validation for any of the entry fields.
Perform the insert	Press F7. Status Bar reports the result of your update.	Press F7. The Status Bar reports the result of your update.
View the new line in your query	Click Run Query.	Press Enter.

Note: Your new line may not be immediately visible. This could be a result of either of the following:

This data may be excluded by the current query. To see the new line you need to select a new query.
The new line is outside of the current view and you need to scroll to the new row's position.

Delete a Row

For both grid and list box views, to delete a row of data, select the row to be deleted and click Delete Row.

Viewing the Data

You can perform the following functions on all views:

Search for data by defining a new query.
Sort the data by column.

Search for Data

	Grid View	List View
Search for data	Click New Query. Highlight the field to search on. Enter your search criteria. Right-click in the field to see a pop-up menu. Select the logical operator (=, >, < and !=). The default is 'no filter'. Click Run Query to see the results of your search.	Click New Query. A dialog box is shown with entry fields for each field in your application. Enter your search criteria. Right-click in the field to see a pop-up menu. Select the logical operator (=, >, < and !=). The default is 'no filter'. Press Enter to see the results of your search.

Sort Data

	Grid View	List View
Sort data in a column	Click on the column heading bar. You see: A < symbol on the heading bar to indicate that the data is sorted in ascending order. A > symbol on the heading bar to indicate that the data is sorted in descending order.	Right-click in the list box. You see a pop-up menu from which you select the column to sort.