OpenESQL

DB2

Chapter 8: OpenESQL Assistant

The OpenESQL Assistant is an interactive tool that makes it easy for you to:

• Prototype SQL SELECT statements and test them against your database.

• Design SQL INSERT, UPDATE and DELETE statements.

Once you have created your SQL queries, you can use the OpenESQL Assistant to insert them into your Net Express COBOL code. All you have to do is open the appropriate project and program, and the OpenESQL Assistant will insert the SQL query at the current insertion point. And if you want it to, the OpenESQL Assistant will even create and insert any auxiliary code made necessary by the insertion of your SQL queries.

This chapter is in the form of a tutorial which shows you how to:

• Start the OpenESQL Assistant

• Connect to a data source

• Select a table

• Select a column

• De-select a column

• Select all the columns in a table

• De-select a table

• Display column details

• Create a new query

• Select a different table

• Change the query type

• Connect to a different data source

• Run a select query

• Specify search criteria

• Disconnect from a data source

• Create a table join

• Add an embedded SQL statement to your program

• Add auxiliary code to your program

• Close the OpenESQL Assistant

8.1 Starting the OpenESQL Assistant

To start the OpenESQL Assistant, select Dockable Windows from the Net Express View menu. Select the OpenESQL Assistant checkbox and close the Dockable Windows dialog box.

Figure 8-1: OpenESQL Assistant

The OpenESQL Assistant window is a dockable window, although you can toggle this facility by right-clicking in the grey area immediately below the title bar and checking (or unchecking) Allow docking. You can also hide the window by right-clicking and checking Hide. If you are unfamiliar with docking or hiding windows, look up Docking in the online help file and select Rearranging views and dockable windows.

8.2 Connecting to a Data Source

Once you have started the OpenESQL Assistant, a list of all the ODBC data sources that you have set up is displayed. In Figure 8-1 above, for example, Excel_Files and Oracle_Database are two of the existing data sources that are displayed by the OpenESQL Assistant.

To connect to a data source, double-click on its name, or on the appropriate data source icon, for example:

Figure 8-2: Data Source Icon and Name

Note: You can connect to one data source only at any one time. For information on changing the data source to which you are connected to, see the section Connecting to a Different Data Source.

Depending on how the data source is set up, you may be prompted to enter one or more of the following:

• a user-id

• a password

• a database name

If you haven't set up any data sources yourself, you can use one of the sample data sources that is set up automatically when you install Net Express. One of these, NetExpress Sample2 points to a sample Microsoft Access database, sample.mdb which is supplied with Net Express and installed in the demo\smpldata\access directory.

The remainder of this tutorial assumes that you are using the sample.mdb database and this is the database which is shown in all of the illustrations.

8.3 Selecting a Table

Once you have connected to a data source, the names of all the tables in that data source are displayed underneath the data source name:

Figure 8-3: Selecting a Table

You can select a table by double-clicking on its name. You will be prompted to select the type of query that you want. At this point, it doesn't matter which type of query you select, so accept the default (a singleton select) by simply clicking on the OK button:

Figure 8-4: Selecting a Query

Once you have selected a query, the COBOL code for the query you have selected is automatically generated and displayed under the Query tab. At the same time, a list of all the columns in the table is displayed underneath the table name:

Figure 8-5: Displaying Columns

You will notice that a table alias is generated automatically by the OpenESQL Assistant:

SELECT FROM Customer A

As this is the first table that you have selected, the alias used is the letter "A". If you were to select a second table, the OpenESQL Assistant would generate an alias of "B" and so on.

Notice also that each column name is prefixed by its alias (A.CustID, A.Company). This enables you to distinguish the columns in one table from those in another.

8.3.1 Selecting a Column

To select a column, simply double-click on the column name. Notice that the COBOL code is automatically updated.

8.3.2 De-selecting a Column

You can de-select a selected column by double-clicking on it. Each time you select or de-select a column, the COBOL code is automatically updated.

8.3.3 Selecting all the Columns in a Table

You can select all of the columns in a table by:

• Right-clicking on the table name

• Clicking on Select All Columns

Figure 8-6: Selecting Columns

You can always tell whether a column is currently selected because, if it is, the column icon is checked:

Figure 8-7: Column Icon

8.4 De-selecting a Table

You can de-select a table by double-clicking on it a second time. If you have already selected columns from this table, you will be prompted to confirm that you want to de-select it. Click on the Yes button. The table is de-selected and the code that was generated is amended:

Figure 8-8: De-selecting a Table

Note that you must double-click on the table name or table icon to de-select the table. Clicking on the minus sign (-) simply toggles the view of the table (+ displays all the columns, - hides them).

For example, select the Customer table again by double-clicking on it. Select all the columns by right-clicking on the table name and clicking Select All Columns. Now click on the minus sign to hide the columns:

Figure 8-9: Hiding the Columns in a Selected Table

8.5 Displaying Column Details

To see more detailed information on the columns in a table, simply click on the Details tab.

Figure 8-10: Displaying Column Details

The information that is displayed is as follows:

• Column Name

  The column name of each column in the table is displayed. Notice that each column name is prefixed by the table alias, for example, CustID is shown as A.CustID. A tick in the box to the left of the column name indicates that the column is currently selected.

• Type

  The data type of the column is displayed. These are the data types used by the data source to which you are connected. The data type of a column must match the COBOL picture clause of the host variable that is used to pass values for that column to and from the data source.

  You can get the OpenESQL Assistant to generate a copyfile (in the current directory and called tablename.cpy) which declares all the necessary host variables with the correct COBOL picture clauses (that is, matching the data type of the table columns). See the section Adding Auxiliary Code below.

• Precision

  The total number of digits in the column is displayed. Precision is only displayed for those columns for which it is relevant. If the column is a text column, precision shows the length of the column.

• Scale

  The number of digits to which the column is rounded is displayed. Scale is only displayed for those columns for which it is relevant.

• Host Variable

  The OpenESQL Assistant automatically generates a host variable for each column. The host variable name takes the form:

  :<table-name>-<column-name>

  For example, the host variable generated by the OpenESQL Assistant for the CustID column is :Customer-CustID, that for the City column is :Customer-City and that for the Phone column is :Customer-Phone.

• Indicator Variable

  As well as a host variable, the OpenESQL Assistant also generates an indicator variable for each column. The indicator variable name takes the form:

  :<table-name>-<column-name>-NULL

  For example, the indicator variable generated by the OpenESQL Assistant for the CustID column is :Customer-CustID-NULL, that for the City column is :Customer-City-NULL and that for the Phone column is :Customer-Phone-NULL.

8.6 Creating a New Query

The following sections explain the ways in which you can modify an existing query or create a new query.

8.6.1 Selecting a Different Table

To select a different table you must first de-select the currently selected table by double-clicking on it (if you have any columns currently selected you will be prompted to confirm that you want to de-select the table) and then select the new table by double-clicking on that. You will be prompted to select a query type.

8.6.2 Changing the Query Type

You cannot change the query type without first de-selecting the currently selected table and then either re-selecting it (if you want to create a different type of query for the same table) or selecting a new table. Once you have selected a table, you will be prompted to select a query. Select the query that you want and click on the OK button.

8.6.3 Connecting to a Different Data Source

If you want to connect to a different data source, click on the Create a New Query button, . This disconnects you from the current data source thus allowing you to select a new data source by double-clicking on its name. If you attempt to connect to a new data source without first disconnecting from the current data source, an error message is displayed informing you that you cannot connect to a second data source.

8.7 Running a Select Query

Connect to the NetExpress Sample2 data source and select the Customer table. You will be prompted for a query type. Select SELECT (cursor) and click on the OK button.

Figure 8-11: Selecting a SELECT (cursor) Query

You will notice that the COBOL code for the SELECT statement is automatically generated and displayed:

Figure 8-12: SELECT (cursor) Query Code

This code includes:

• A DECLARE CURSOR statement which creates the cursor to be used in the SELECT:

  DECLARE CSR-1 CURSOR FOR SELECT FROM Customer A

• An OPEN statement which executes the SELECT statement specified in the corresponding DECLARE CURSOR statement:

  OPEN CSR-1

• A FETCH statement to retrieve the next row from the cursor's results set:

  FETCH CSR-1 INTO

• A CLOSE statement to close the cursor:

  CLOSE CSR-1

As the query stands, it would cause a syntax error - click on the Run the Query button, , to see the error displayed - because it does not specify which column(s) to select or what to select them into.

To select a column, double-click on it. Select A.CustID. For each column that you select, the automatically generated code is updated as follows:

• The column is added to the DECLARE CURSOR statement, for example:

  DECLARE CSR-1 CURSOR FOR SELECT
     A.CustID
  FROM Customer A

• A host variable (into which the column will be read) is added to the INTO clause of the FETCH statement, for example:

  FETCH CSR-1 INTO
     :Customer-CustID:Customer-CustID-NULL

Figure 8-13: Adding Columns to a SELECT Statement

Now select A.Company and A.Phone. Once you have selected all the columns that you want, you can run the query by clicking on the Run the Query button, . The OpenESQL Assistant automatically displays the results of the query:

Figure 8-14: Query Results

8.8 Specifying Search Criteria

You can limit which rows are returned by a SELECT statement by specifying search criteria (a WHERE clause). The OpenESQL Assistant provides a special screen which enables you, quickly and easily, to specify the search criteria used in the WHERE clause.

To limit the number of rows returned by the SELECT statement, click on the Search Criteria tab:

Figure 8-15: Search Criteria Tab

To specify search criteria:

1. Select a column name from the Column list box. This list box displays the column name of every column in the currently selected table(s).

2. Select a conditional operator. Click on the down-pointing arrow to scroll through the valid conditional operators.

3. Select a Target Type. The target type must be a host variable, a literal, a special register or a column name.

4. Select, or enter, a Target Value. If you have selected column name as the target type, a list of all the valid column names generated by the OpenESQL Assistant is displayed in the target value list box. If you have selected host variable as the target type, a list of all the valid host variables generated by the OpenESQL Assistant is displayed in the target value list box. If you have selected either literal or special register, the Edit button to the right of the target value list box is enabled. This means that you can either enter a value directly into the target value list box or you can click on the Edit button to display the Literal Value Editor.

Once you are happy with what you have selected, click on the right-pointing arrow (>). This moves your search criteria across into the right-hand pane and, at the same time, updates the COBOL code in the query window.

For example, you can limit the customer IDs returned by the SELECT statement created above as follows:

1. Select A.City in the Column list box.

2. Select = as the conditional operator.

3. Select Literal as the target type.

4. The Edit button to the right of the target value list box is now enabled. Click on it to display the Literal Value Editor dialog box:

   
   
   Figure 8-16: Literal Value Editor

5. Enter London as the target value. Notice that the OpenESQL Assistant automatically adds the correct delimiters (in this case single quotation marks). Click on the OK button.

6. Now click on the right-pointing arrow to move your search criterion across into the right-hand pane.

Figure 8-17: Specifying Search Criteria

Now when you run this query again (click on the Run the Query button, ), only the customer IDs of those customers with an entry of London in the City columm are displayed:

Figure 8-18: Limiting the Customer IDs by Specifying Search Criteria

Once you have specified the first search criterion and moved it to the right-hand pane, the second and subsequent criteria must follow a logical AND or logical OR which you select by checking the appropriate radio button.

You can select a search criterion displayed in the right-hand pane and click on the left-pointing arrow (<). This removes the search criterion from the right-hand pane and places it in the appropriate boxes in the left-hand pane such that you can edit it. Moving search criteria from the right to the left-hand pane also removes the associated COBOL code from the automatically generated query.

8.9 Disconnecting from a Data Source

To disconnect from a data source, simply click on the Create a New Query button, .

8.10 Creating a Table Join

Where tables have at least one column in common, you can create table JOINs enabling you to retrieve information from more than one table at the same time.

For example, suppose that you want to retrieve from the NetExpress Sample2 the company name and the phone number of all those customers who have ordered a particular product. Product details are held in the Product table. To display them:

1. Start the OpenESQL Assistant and connect to the NetExpress Sample2 data source.

2. Double-click on the Product table, select SELECT (cursor) as the query type and click on the OK button.

3. Right-click on the Product table and click on Select All Columns.

Run the query by clicking on the Run the Query button, . You will see that the product with a Product ID of 16 is pavlova. Suppose that the information that you want is the company name and phone number of all those customers who have ordered pavlova. The Customer table contains the phone number and company name of all the customers but the details of any orders, including the product ID of the product ordered, are held in the Orders table. So, to get the information you want, you need to display the company name and phone number from the Customer table, but use the Product ID column from the Orders table to filter the information. And to do this, you need to create a table join which links the Customer and Orders tables. This is made much easier by the OpenESQL Assistant which, whenever you are building an SQL query and select a second (or subsequent) table, attempts to join the tables for you.

1. De-select the Product table by double-clicking on it and clicking on Yes when prompted to confirm the de-selection.

2. Double-click on the Customer table, select SELECT (cursor) as the query type and click on the OK button.

3. Double-click on the A.Company and A.Phone columns.

The COBOL code needed to generate this query is displayed:

Figure 8-19: SELECT Statement

At this stage, you could run the query and the company name and phone number of every customer would be displayed.

Now, to create the table join, double-click on the Orders table:

Figure 8-20: Add Table to Query

The OpenESQL Assistant will create a table join for you using the first matching column it finds, which is CustID. (You can override this join by clicking on the No button and then specifying your own join via the Search Criteria tab.) To accept the join proposed by the OpenESQL Assistant, click on the Yes button. The WHERE clause is automatically added to the COBOL code:

Figure 8-21: Adding a Join to the COBOL Code

Now, to complete the query:

1. Click on the Search Criteria tab

2. In the Column list box, select B.ProdID

3. In the Conditional Operator list box, select =

4. Under Target Type, select Literal

5. Under Target Value, enter 16

6. Click on the right-pointing arrow

Note that the logical AND is automatically selected and inserted for you.

Figure 8-22: Specifying the Search Criteria

Now click on the Query tab and you will see that the WHERE clause has been expanded to include the new search criteria:

Figure 8-23: Search Criteria Added to Code

Now, run the query again by clicking on Run the Query, , and you will see that only the company name and phone number of those customers who have ordered the product with a product id of 16 (pavlova) are displayed:

Figure 8-24: Running the Code

8.11 Adding Embedded SQL to your Program

Once you are happy with the SQL query that you have created, you can add it to your program by clicking on the Insert Query into Current Program button, . Make sure that the appropriate project is open in Net Express and that the program that you want to add the code to is open for editing. For the purposes of this tutorial:

1. Create a directory called OpenESQL in the Net Express Demo directory.

2. Create a new project called OpenESQL in the OpenESQL directory. Leave this project open.

3. Now create a new program. It doesn't matter, for the purposes of this tutorial, that the program is empty but you cannot save it in this state, so press the Return key and then save it as "reports.cbl". Now add it to the OpenESQL project by clicking on Add Files to Project on the Project menu.

4. Now, position the cursor at the very top of your program and then click on the Insert Query into Current Program button, .

The SQL query is added to your program at the current insertion point:

Figure 8-25: Adding Embedded SQL to your Program

8.12 Adding Auxiliary Code

Once you have added the embedded SQL to your program, there are further code changes that you will need to make before you can run your program successfully. For example, you cannot successfully execute an SQL query which accesses a database until you have inserted a CONNECT statement to connect to the data source.

The OpenESQL Assistant can automatically generate the auxiliary code that you need. To enable this facility, click on the Auxiliary Code tab. The following is displayed:

Figure 8-26: Auxiliary Code Tab

You can use the OpenESQL Assistant to insert any of the following:

• CONNECT Statement

  The OpenESQL Assistant can generate a CONNECT statement using the information it already has about your current data source connection.

• SQLCA Declaration

  The OpenESQL Assistant can generate an INCLUDE statement to include an SQL Communications Area (SQLCA) in your program.

• Host Variable Declarations

  For each table that you have selected, the OpenESQL Assistant generates a copyfile which includes all of the host variable declarations that you need. The OpenESQL Assistant can generate an INCLUDE statement to include each of these copyfiles in your program.

• DISCONNECT Statement

  The OpenESQL Assistant can generate a DISCONNECT statement to disconnect from the current data source.

The OpenESQL Assistant inserts the automatically generated auxiliary code at the current insertion point in your program, so you must make sure that the cursor is positioned at the correct point in your program before you insert the auxiliary code. For example, you must insert a CONNECT statement before any attempt to access a data source. You should insert any INCLUDE statements (for the SQLCA or host variable copyfiles) in the data division of your program.

You can choose which pieces of code you would like the OpenESQL Assistant to insert for you and which you would like to handle yourself.

If you want the OpenESQL Assistant to generate and insert auxiliary code for you:

1. Check the radio button next to the type of code you want to generate, for example, CONNECT Statement.

2. Make sure that the cursor is correctly positioned within your program.

3. Click on the Insert Query into Current Program button, .

The auxiliary code is inserted into your program at the current insertion point:

Figure 8-27: Inserting Auxiliary Code

8.13 Closing the OpenESQL Assistant

To close the OpenESQL Assistant, click on the Close button at the top right-hand corner of the screen.

OpenESQL

DB2