Define and Test a Query

Provides step-by-step instructions for using the OpenESQL Assistant to define and test an SQL query.

You are now ready to use the OpenESQL Assistant to define a query that you can embed into a COBOL program and run. In this scenario, you are interested in retrieving the first and last names of your employees and the salary for each. To do this, you define the query to access the EMP table, and return the data from three columns: FIRSTNME, LASTNAME, and SALARY.

Start the OpenESQL Assistant

From the Eclipse main menu, click Run > Tools > Data Tools > OpenESQL Assistant.

Select a table

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.
Check the box next to EMP(dbo).
The Select Type of Query to Create dialog box enables you to choose the type of query you want to generate. In this case, you want to create a simple SELECT CURSOR statement. OpenESQL Assistant generates a default cursor name that you can override; however, in this case you should accept the default.
Click SELECT (cursor); then click OK.
OpenESQL Assistant 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

You want this SQL query to return the first and last name of each employee along with the pay rate for each.

Check the following column names:
- A.FIRSTNME
- A.LASTNAME
- A.SALARY
OpenESQL Assistant updates the Query tab with the appropriate code.

Test the query

The SQL query code is complete at this point, so you can test it.

Click Run Query .
OpenESQL Assistant shows the results of the query on the Results tab.