Tutorial: Build and Test a Query

We are now ready to build a query that we can embed into a COBOL program and run. In this scenario, we are interested in retrieving the first and last names of our employees and the salary for each. So we'll build the query to access the EMP table and return the data from three columns: FIRSTNME, LASTNAME, and SALARY.

Requirements

Before attempting this tutorial, you must complete the following tutorials in the order listed:

• Tutorial: Create a SQL Server Database
• Tutorial: Create and Configure an Eclipse Project
• Tutorial: Set OpenESQL Assistant Configuration Options
• Tutorial: Catalog a Connection

Start OpenESQL Assistant

• In Eclipse, click Run > Tools > Data Tools > OpenESQL Assistant.

Select a Table

1. On the OpenESQL Assistant tree view, check the box adjacent to the OESQLDemo entry. This expands to show all available tables in the database.
   Note: The time required to connect to SQL Server can vary depending on the type and location of the SQL Server database to which you are connecting.
2. Check the box next to EMP.

   The Select Type of Query to Create dialog box enables you to choose the type of query you want to generate. In this case, we want to create a simple SELECT CURSOR statement. OpenESQL Assistant generates a default cursor name that we have the option to override. Here, we'll accept the default.

3. Click SELECT (cursor); then click OK.

   The OpenESQL Assistant marks the table with check mark, generates the query, and places the generated code on the Query tab.

   The columns in your selected table appear on the tree view with an alias of "A" prepended to the column name. For SELECT queries only, OpenESQL Assistant assigns the alias of A to the first table you select for a query, B to the second table if you join another table to the query, C for the next, etc. By prepending the table alias to each column name, you can easily identify the table to which a column belongs even if the same column name appears in more than one table.

Select Columns

We want our SQL query to return the first and last names of our employees along with the pay rate for each employee.

• Check the following column names:
  • A.FIRSTNME
  • A.LASTNAME
  • A.SALARY

  OpenESQL Assistant updates the Query tab with the appropriate code.

Test the Query

Our SQL query is complete at this point so we can test it.

• Click Run Query .

  The OpenESQL Assistant shows the results of the query on the Results tab.

This completes the tutorial. Next, please complete Tutorial: Generate and Embed an SQL Program.