How to...This chapter describes the configuration and use of the OpenESQL Assistant.
The OpenESQL Assistant is an interactive tool that makes it easy for you to:
Before creating SQL queries using OpenESQL Assistant, you can set options that determine the behavior of the OpenESQL Assistant and specify how it generates SQL. Do this from the Options dialog box by expanding Micro Focus COBOL Tools > OpenESQL Assistant. On this dialog box, you can set the following options:
Set this option before connecting to a data source. When you set this option to ADO.NET, you also can also set the following additional options:
When using the ADO.NET runtime (i.e., compiling programs with the SQL(DBMAN-ADO directive), set the Type COBOL Variable option to Object.
Set this option to -1 to display all rows when running a query.
Select one of three statement types:
Note: If you are running Visual Studio 2008 under Windows Vista and you use the ADO.NET Connection Editor from within OpenESQL Assistant to add, update, or delete connection information, you must run OpenESQL Assistant as an Administrator.
By default, OpenESQL Assistant starts docked to the Net Express with .NET IDE. Uncheck this option if you want OpenESQL Assistant to start as a window that can be docked but is not docked automatically.
By default, OpenESQL Assistant prompts you when disconnecting from a data source. This prevents you from inadvertently losing a query that you have built. Uncheck this option, if you do not want be be prompted on disconnecting from a data source.
OpenESQL under .NET supports both EXEC SQL and EXEC ADO access to managed data sources. There currently exists no common repository for sharing connection information between ADO.NET data providers. However, Net Express provides a common repository that all COBOL applications can use to save connection information. This also allows OpenESQL to be source code compatible when using EXEC SQL CONNECT statements. If you check this option, OpenESQL Assistant automatically transfers the ODBC connection information into the repository when you CONNECT to a data source.
OpenESQL Assistant also includes an ADO.NET Connection Editor which allows you to manually add connection information.
Once you have cataloged all your data sources in the repository you can uncheck this option.
By default, OpenESQL Assistant displays all tables in the tree view and builds all queries unqualified. However, if the data source has multiple tables with the same table name, you can set OpenESQL Assistant to show table names in the tree view with their owner names immediately following in parenthesis. Queries performed also return table names qualified with the owner name.
By default, OpenESQL Assistant does not enclose column and table names with the quote identifier associated with the data source unless the column or table name contains embedded blanks, a special character such as $, or DBCS characters. You can set OpenESQL Assistant to enclose all table and column names with the appropriate quote identifier.
By default, OpenESQL Assistant generates SQL statements using SQLCODE checking. You change this to set OpenESQL Assistant to generate SQL statements using SQLSTATE checking instead.
By default, OpenESQL Assistant generates SQL statements for use by any application. You can also set OpenESQL to generate SQL statements that can be used as as Web service.
By default, OpenESQL Assistant lists items in the tree view in the sequence that they are returned from the system catalog. You can also set OpenESQL Assistant to list items in table name sequence instead. When you set OpenESQL Assistant to list items in table name sequence, owner names are not considered.
By default, OpenESQL Assistant generates EXEC SQL statements. Leave this radio button selected if you want to build EXEC SQL queries.
OpenESQL Assistant can alternatively build EXEC ADO statements. These are similar to EXEC SQL statements except that they only work with ADO.NET data providers. Select this radio button if you want to build EXEC ADO queries, and if you want to generate EXEC ADO Disconnected or Connected statements as auxiliary code.
By default, OpenESQL Assistant generates SQL queries and copybooks using a combination of upper and lower case. You can also set OpenESQL Assistant to generate in all uppercase or all lowercase.
By default, OpenESQL Assistant only returns the first 50 rows when you run a query. This is to prevent queries from returning huge numbers of rows, potentially crashing your machine or overloading your network. However, you can set the maximum number of rows returned to a number of your choice. We recommend that you choose the number based on the amount of data you expect to be returned, or based on your testing needs.
By default, OpenESQL Assistant generates a host variable as a PIC X(n) field for VARCHAR columns. When data is mapped to the host variable, it is null terminated. However, you can set OpenESQL Assistant to generate the host variable with two level-49 variables; one for the length of the data mapped and one for the actual text data.
By default, OpenESQL Assistant generates COBOL host variables using SQL TYPE definitions where appropriate. This allows the SQL precompiler to better determine host variable usage. You can also set OpenESQL Assistant to generate host variables using the traditional method, which is appropriate when using the copybook with array fetches.
By default, OpenESQL Assistant generates the structure name for the copybook as DCLtablename. You can also generate the structure name using just the table name or you can supply your own structure name as long as it is a valid COBOL name. OpenESQL Assistant converts all underscores into hyphens.
By default, OpenESQL Assistant creates host variables using a combination of the column name and a prefix of the table name, converting all underscores to hyphens to create a valid COBOL name. You can also set OpenESQL Assistant to always generate host variables using only the column name (no prefix) or to use an alphabetic character prefix. Host variables generated with alphabetic character prefixes use "A" for the first table selected, "B" for the second table, etc.
Note: If a generated host variable name would result in a name that is either longer than 31 characters in length or that would contain invalid characters, OpenESQL Assistant generates the host variable name as the column number with a prefix of FLD regardless of the method specified.
By default, OpenESQL Assistant generates indicator variables at the end of a copybook. You can also set OpenESQL Assistant to generate indicator variables after each host variable instead. Your third option is to set OpenESQL Assistant so that no indicator variables are generated.
By default, OpenESQL Assistant requires you to provide a user ID and password when you connect to a data source. However, if you always connect to the same data source or always use the same logon information to connect to data sources, you can specify a user ID and password for OpenESQL Assistant to use automatically each time you connect. If you are using ADO.NET mode and you choose not to specify a user ID or password here, OpenESQL Assistant prompts you for your user ID and password when connecting to a data source.
By default, the OpenESQL Assistant tree view shows all tables, views, aliases, and synonyms found in a data source. For certain types of data sources, the list can be long taking extra time to build and display. To reduce the amount of time it takes to build the list, you can set OpenESQL Assistant to return a qualified list representing less data. To do this, you specify a qualifier, table name, an owner, or a combination of the three. You can use the following wildcard characters in any of these specifications to expand the returns.
For example, if you wanted to restrict the tree view list to only those tables that begin with the letters XYZ, you would specify XYZ%.
See your Visual Studio documentation for information on setting properties.
Use the OpenESQL Assistant to easily design and build SQL queries and embed those queries into your COBOL code. As you make specifications for a query, OpenESQL Assistant generates the necessary code for you. You can also test the queries you create to ensure they are producing the proper results before you embed them into your programs. And, you can use OpenESQL Assistant to generate and embed the auxiliary code necessary to support your queries in COBOL.
You can configure the OpenESQL Assistant's dockable window to appear when you start Visual Studio or you can simply invoke it when you need it.
Depending on how OpenESQL is configured, you might be prompted to supply a user name and password.
Before you can use OpenESQL Assistant to generate SQL, you must first connect to the data source that contains the data you want to access. All data sources cataloged on your workstation are listed in the tree view of the OpenESQL Assistant. You can connect to one data source at a time. After you have connected, all of the table names in the data source show on the tree view.
If you are running in ODBC mode and have checked the Automatically catalog data source on CONNECT option for OpenESQL Assistant, all ODBC data sources on your workstation are automatically cataloged as an ODBC ADO.NET data provider. See your Microsoft documentation for information on setting up ODBC data sources.
Notes:
The process of building a query using OpenESQL includes at least some and potentially all of the following steps:
The following sections provide information about this process.
You can select one table at a time or you can select all of the tables in a data source. OpenESQL Assistant prompts you to select the type of query you would like to perform on the table or tables by presenting the Select Type of Query to Create dialog box. This dialog box contains a list of query types you can perform on your selection. Depending on the type of query you select, you can also change the default cursor name, specify that your query be generated as a Stored Procedure, and specify the stored procedure (SP) name.
Selected tables appear in the tree view with a check mark next to the
table icon, 
.
Once you have selected a query, the COBOL code for the query you have selected is automatically generated and displayed underneath the Query tab. At the same time, a list of all the columns in the table is displayed underneath the table name.
For SELECT queries, a table alias is generated automatically by the OpenESQL Assistant. For example:
SELECT FROM Customer A
The first table selected generates an alias of the letter A. If you select a second table, the OpenESQL Assistant generates an alias of B and so on.
Each column name is prefixed by its alias (for example, A.CustID, A.Company). This enables you to distinguish the columns in one table from those in another.
Some databases use special characters in the names of system tables. For example, Oracle can use system tables whose names contain a dollar sign ($). When the OpenESQL Assistant encounters a special character in the name of system table that would render the generated table name illegal, it automatically encloses the column and table names in quotes.
If the name generated from the column name (including any prefixes and suffixes) is longer than 31 characters or contains illegal characters would be illegal in COBOL, OpenESQL generates a host variable using the column number (for example, COL005) rather than the column name.
Once you have your table and query type selected, you can select one or more columns to process in your query.
Selected columns appear in the tree view with a check mark next to the
column icon, 
.
You can specify an aggregate function for any column in a SELECT (cursor) or SELECT DISTINCT (cursor) query. An aggregate function retrieves certain information about the data returned from a column.
OpenESQL returns the following information for each group of returned data:
| Aggregate Function | Value for each group of returned data |
|---|---|
| Sum | Sum of all values returned (numerical columns only) |
| Avg | Average of all values returned (numerical columns only) |
| Max | Maximum value returned |
| Min | Minimum value returned |
| Count | Number of records returned |
You can limit which rows are returned by a query by specifying search criteria on the OpenESQL Assistant Search Criteria tab. OpenESQL Assistant generates a WHERE clause based on the criteria you specify.
You can order the rows that are returned by a query by specifying which columns to sort using the OpenESQL Assistant Sort tab. OpenESQL Assistant generates an ORDER BY clause based on the order you specify.
Where tables have at least one column in common, you can create a table join enabling you to retrieve information from more than one table at the same time. The OpenESQL Assistant creates a table join using the first matching column it finds. If OpenESQL does not find any matching columns, it cannot generate the join automatically. In this case, you can specify the join using the Search Criteria feature.
This feature is only available for SELECT queries.
This section presents some features of OpenESQL Assistant and recommends some procedures that can enhance your ability to create queries
The Details tab in the OpenESQL Assistant shows detailed information on the columns in a table. The columns on the Details tab describe the columns in the selected table as follows:
The column name of each column in the table. Notice that each column name is prefixed by the table alias. For example, a table with the name CustID shows on in the Column Name column as A.CustID. A tick in the box to the left of Column Name indicates that the column is currently selected.
The data type of the column. 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.
OpenESQL Assistant can generate a copybook, tablename.cpy, in the current directory that declares all of the necessary host variables, matching them with COBOL picture clauses generated using column data types. See the section Auxiliary Code later in this chapter for more details.
The total number of digits in numerical columns or the length of the column for text columns.
The number of digits to which the column is rounded, where relevant.
The value of the generated host variable for the column. Generated host variable names take the form:
:<table-name>-<column-name>
The value of the generated indicator variable for the column. Generated indicator variable names take the form:
:<table-name>-<column-name>-NULL
When you disconnect from a data source, OpenESQL discards the current query. You must disconnect from one data source before you can connect to another.
When you close a query, OpenESQL discards the generated code, enabling you to start a new query.
Most queries you create using OpenESQL Assistant access one table only. If you remove that table from the query, OpenESQL closes the query and you lose the code you've generated for it. However, if you have created a SELECT query that contains one or more table joins, you can safely remove a table without closing the query. When you initiate the removal of a table from a SELECT query, you're prompted to confirm your decision. Upon confirmation, OpenESQL removes the generated code that pertains to the table from the query. It also removes any code pertaining to columns that belong to that table.
OpenESQL Assistant doesn't directly generate code for array fetches. However, you can modify the SELECT (cursor) code generated by OpenESQL Assistant to handle array fetches.
You can use OpenESQL Assistant to test the queries you create before committing them to program code. To do this, you simply run a query from the OpenESQL Assistant. The results for all SELECT statements appear automatically on the Results tab. Other query types return the following information in a message box:
Occasionally, the query you create will require additional specifications for it to execute correctly. If OpenESQL Assistant cannot execute the code, you are prompted to correct it.
Note: When you run a query in OpenESQL Assistant, any UPDATE, INSERT, and DELETE statements do execute against the database, potentially altering its contents.
Once you are happy with the SQL query that you have created, you can insert it into your COBOL program.
The SQL queries you generate with OpenESQL often require additional code outside of the query itself in order to execute properly from your COBOL programs. For example, under DB2 UDB you cannot successfully execute an SQL query that accesses a database until you have inserted a CONNECT statement to connect to the data source. Once you have embedded your query, you can use OpenESQL Assistant to generate and embed this auxiliary code.
OpenESQL Assistant can generate the following types of EXEC SQL auxiliary code:
Generates a CONNECT statement based on known information about your current data source connection.
Generates an INCLUDE statement to include an SQL Communications Area (SQLCA) in your program.
For each table that you have selected, the OpenESQL Assistant generates a copybook that includes all of the host variable declarations you need. The OpenESQL Assistant can also generate the INCLUDE statement necessary to include the copybook in your program.
Generates a DISCONNECT statement to disconnect from the current data source.
Generates a COMMIT statement to commit changes to a data source issued from a previous SQL statement.
Generates a ROLLBACK statement to rollback changes to a data source issued by a previous SQL statement or statements.
Generates a skeleton SQL program that includes the INCLUDE SQLCA, CONNECT and DISCONNECT SQL statements and a default SQL error routine.
If you have enabled OpenESQL Assistant to generate EXEC ADO code by checking the Generate EXEC ADO option when you set options for OpenESQL, you can generate auxiliary code for both the EXEC ADO disconnected and connected models.
OpenESQL Assistant embeds the dataset name you specify in the Dataset name field on the Auxiliary Code tab into all generated statements that cite a dataset.
Note: You can mix EXEC ADO and EXEC SQL statements in the same program providing that you compile your COBOL program with the directive SQL(DBMAN=ADO).
OpenESQL Assistant can generate the following types of EXEC ADO auxiliary code. For a description of each, see the section Using Embedded EXEC ADO Statements in the chapter Using OpenESQL with ADO.NET.
Generates a skeleton SQL program that includes the INCLUDE SQLCA statement and a default SQL error routine.
Generates a skeleton SQL program that includes the INCLUDE SQLCA, CONNECT and DISCONNECT SQL statements and a default SQL error routine.
Certain EXEC ADO statements require a significant amount of user input to build them correctly. When you use the OpenESQL Assistant Auxiliary Code tab to generate one of these statements, you invoke a wizard that walks you through the various options for that statement.
The statements generated by these wizards are very similar to the EXEC ADO DELETE, INSERT, SELECT, and UPDATE statements except that you cannot execute them from the OpenESQL Assistant. Because the Wizards need table and column names to work correctly, you must create a query first.
OpenESQL Assistant provides the following wizards for use in generating auxiliary code:
Use this wizard to build a WHERE clause that limits what values to delete from a data source when the data source is updated. The wizard provides a page similar to the Search Criteria tab. It is not necessary for you to supply a Target type or Value because these are dependent upon the column selected.
Embed the generated TO DELETE USE statement into your program after an EXEC ADO PREPARE TO UPDATE statement and before the EXEC ADO UPDATE DATASOURCE statement.
Use this wizard to limit the number of rows and columns that are returned from the data source when the EXEC ADO FILL DATASET statement is executed. The wizard provides pages that enable you to:
Note: If you specify a literal value as part of your WHERE clause, surround it with either single or double quotes depending on which identifier your DBMS uses to enclose literals.
For the generated statement to work properly you must have DECLARE DATATABLE and DECLARE DATASET statements coded into your program, and you must embed the TO FILL USE statement into your program after a PREPARE TO FILL statement.
The ConnectedDemo application provided by Micro Focus shows detailed examples of using the TO FILL USE statement in an application. For more information, see the section Demonstration Projects in the chapter Using OpenESQL with ADO.NET.
Use this wizard limit what values to insert into a data source when the data source is updated. The wizard provides pages that enable you to:
You should opt to generate this clause if you want data to be returned to your application after updating a data source with newly inserted rows.
You can specify these only if you have opted to generate the WITH REFRESH clause. The pre-defined source value “@@IDENTITY” is provided to work with Microsoft SQL Server data providers.
Embed this statement into your program after an EXEC ADO PREPARE TO UPDATE statement and before the EXEC ADO UPDATE DATASOURCE statement.
Use this wizard to generate an EXEC ADO TO REFRESH statement to update values in your dataset after updating the data source. Generate this statement in lieu of placing the REFRESH clause in TO INSERT or TO UPDATE EXEC ADO statements. The wizard provides one page that enables you to specify the source data and column you want to return. The pre-defined source value “@@IDENTITY” is provided to work with Microsoft SQL Server data providers.
Embed this statement into your program after an EXEC ADO PREPARE TO UPDATE statement and before the EXEC ADO UPDATE DATASOURCE statement.
Use this wizard to limit what columns and values to update to a data source. The wizard provides pages that enable you to:
You should opt to generate this clause if you want data to be returned to your application after updating a data source with new values.
It is not necessary for you to supply a Target type or Value when building your WHERE clause because these are dependent upon the column selected.
You can specify these only if you have opted to generate the WITH REFRESH clause. The pre-defined source value “@@IDENTITY” is provided to work with Microsoft SQL Server data providers.
Embed this statement into your program after an EXEC ADO PREPARE TO UPDATE statement and before the EXEC ADO UPDATE DATASOURCE statement.
You embed auxiliary code in the same manner as you embed your SQL queries.
This section provides information on generating stored procedures and testing them using OpenESQL Assistant.
Note: Multiple types of SQL statements can exist within a stored procedure. OpenESQL Assistant is not designed to generate stored procedures with multiple types of SQL statements. You need tools, such as the ones provided by Microsoft, to do this.
The intention of OpenESQL Assistant is to allow you to quickly learn how to generate simple stored procedures and the COBOL code required to invoke those stored procedures.
Microsoft provides tools to create, edit, and test stored procedures. It is then the task of the programmer to generate the code to invoke these stored procedures from a COBOL client program. The OpenESQL Assistant makes this process easier.
When using Microsoft SQL Server data sources, you have the option to use OpenESQL Assistant to generate the query as a stored procedure. OpenESQL Assistant generates both the SQL statements to create the stored procedure and the client code to invoke it.
OpenESQL Assistant generates two SQL statements that drop the stored procedure and then create it. You typically place these SQL statements in a server program because they only need to be executed once.
OpenESQL Assistant also generates the code that goes into the client program and invokes the stored procedure. Depending on the type of query you select, OpenESQL Assistant might generate a DECLARE CURSOR statement that invokes the stored procedure or it might generate SQL CALL statements only.
Once you have built the stored procedure query, you can test it in much the same manner as you test other queries. However, because multiple SQL statements are created by OpenESQL Assistant for a stored procedure, you need to run the query multiple times. Each time you run the query, you execute the next SQL statement.
The first time you run the query, the DROP PROCEDURE statement is executed, and the following message appears:
Cannot drop the procedure 'spname,' because it does not exist...
This message is expected and will change if you execute the DROP PROCEDURE multiple times.
The second time you run the query, the CREATE PROCEDURE statement is executed. A message appears indicating that the statement executed successfully.
To execute a stored procedure CALL statement with search criteria that requires you to provide a literal value, you are prompted to provide that literal value.
When all SQL statements have been executed, OpenESQL shows the results on the Results tab.
You can repeat this process until you are satisfied with the results.
This procedure is also similar to that of embedding a standard SQL query into a program.
OpenESQL Assistant generates statements for both client and server programs. You can delete the statements which are not needed. If your program is the client program, the DROP and CREATE PROCEDURE statements are not necessary.
Note: If you test the query as outlined above, you do not need to run either the DROP or CREATE PROCEDURE statements in a program as the stored procedure has already been created. If the stored procedure has not already been created, then you need to either add a SQL COMMIT statement or add directive SQL(AUTOCOMMIT) to the program that issues the CREATE PROCEDURE. Otherwise the stored procedure will not be saved to the data source.
Copyright © 2009 Micro Focus (IP) Ltd. All rights reserved.