The OpenESQL preprocessor enables you to access a data source via ADO.NET data providers.
You need to look at the differences between ODBC and ADO.NET to understand OpenESQL with ADO.NET. ODBC was designed to enable maximum interoperability between different database systems. ADO.NET was designed to maximize scalability of data handling components in distributed systems. This difference in objectives means that ODBC and ADO.NET sometimes work very differently. For example, ODBC provides comprehensive support for pessimistic (locking) concurrency control, while ADO.NET is strongly biased in favour of optimistic (non-locking) concurrency control.
Another major difference between ADO.NET and ODBC is the support for disconnected or offline DataSets. A DataSet is like a miniature database, complete with tables, columns, keys and constraints, which can be filled from a database. When a DataSet is modified, both current and original values of data are kept in the DataSet. This allows the DataSet to be synchronized with the database from which it was filled after being passed between servers and components in a distributed system. Used in this way, a DataSet can be regarded as a lightweight replication tool. For example, data for a customer order is extracted from a database and processed remotely from the database server. Later, the database is synchronized with the changes made to the DataSet. Processing could take place on either a Web server or a laptop. Because the DataSet has no connection to the database while it is being processed, optimistic concurrency must be used when the database is updated.
DataSet contents can also be loaded from and saved to data sources other than relational databases. For example, XML files or software components, where the mechanism used to store data does not concern the application that processes the contents of the DataSet, can be used. DataSets also have excellent support for data binding to user interface controls in WinForms and WebForms. This means that DataSets are useful for communicating data in mixed language applications.
For this COBOL development system, a new family of EXEC ADO statements has been added to OpenESQL. These statements operate with DataSets rather than databases but their syntax will be familiar to anyone with a knowledge of SQL and Embedded SQL. Other EXEC ADO statements have been added which allow direct access to the underlying ADO.NET objects used by OpenESQL at run time. With these statements it is possible for OpenESQL to share connections and transactions with other software components or for COBOL programmers to use low level properties and methods of objects that have been created by OpenESQL.
This COBOL development system supports backwards compatibility with applications already developed or maintained using OpenESQL. Many existing applications will work with ADO.NET simply by changing the compiler directive SQL(DBMAN=ODBC) to SQL(DBMAN=ADO). It is possible for an application to use both ADO.NET and ODBC but a source program can use only one. ODBC and ADO.NET cannot share database connections and transactions.
Note that ADO.NET does not have all of the features of ODBC when connected to a database and that while an existing program can work with ADO.NET, its performance characteristics and storage requirements may be very different from working with ODBC. For example, any cursor used for scrolling fetches or positioned updates has its result set read completely and buffered on the client system in a DataSet when it is opened. ADO.NET provides no native support for positioned updates and so OpenESQL converts them to searched updates using original column values and optimistic concurrency. For this to provide the same degree of integrity as ODBC, an application must use repeatable read transaction isolation and the result set must contain columns that will uniquely identify the row being updated.
To obtain ADO.NET support, you must:
See the chapter OpenESQL Assistant for details.
OpenESQL supports ADO.NET data providers. To use OpenESQL with ADO.NET data providers, you must use the SQL compiler directive:
dbman=ado
This enables EXEC SQL statements to communicate with the ADO.NET data provider specified in the CONNECT statement.
OpenESQL also supports the use of EXEC ADO statements to create and manipulate ADO.NET objects. See the section Using Embedded EXEC ADO Statements for more information.
When you use dbman=ado with EXEC SQL statements, be aware that the following OpenESQL functions are not currently supported:
Calling stored procedures is allowed. However, when calling stored procedures, be aware that the following items are not currently supported:
Notes:
When you compile your program, you must specify the SQL Compiler directive and its appropriate options such that the preprocessor converts the embedded SQL statements into function calls to the data source. The ADO.NET data provider that your program calls depends on the particular data source that you are accessing.
You can use the $SET statement in your program to specify options for the SQL Compiler directive. For example:
$SET SQL(DBMAN=ADO)
The table below lists the SQL Compiler directive options common to ODBC and ADO.NET data sources.
| Option | Description |
|---|---|
| [NO]ANSI92ENTRY | If this is set, OpenESQL conforms to the SQL ANSI 92 entry level standard. |
| [NO]AUTOCOMMIT | If this is set, each SQL statement is treated as a separate transaction and is committed immediately upon execution. If this is not set, and the ODBC driver you are using supports transactions, statements must be explicitly committed (or rolled back) as part of a transaction. |
| BEHAVIOR={ANSI | MAINFRAME} |
Set this option to ANSI or MAINFRAME to use pessimistic locking with COBOL cursors. This feature is only available under MS SQL Server. See the section Pessimistic Locking with COBOL Cursors for more information. |
| CHECKDUPCURSOR | If this is set, OpenESQL returns SQLCODE -19516 if the cursor is already open and the program is compiled with directive NOANSI92ENTRY. the default behavior when a program is compiled with NOANSI92ENTRY is to automatically close the cursor and then re-open it. |
| CHECKSINGLETON | Causes OpenESQL to check if singleton SELECTs return more than one row when executed. If this occurs, OpenESQL sets SQLCODE to a -811. |
| DBMAN=preprocessor | Specifies the preprocessor to use. For ODBC drivers, this should always be set to ODBC, that is DBMAN=ODBC. For ADO.NET data providers, this should always be set to ado, that is DBMAN=ADO. This directive is not required when compiling programs with OpenESQL as the default is DBMAN=ODBC. |
| [NO]ESQLVERSION | Set OpenESQL syntax level. |
| [NO]NIST | If this is set, OpenESQL conforms to the NIST interpretation of the SQL ANSI 92 entry level standard. |
| PF_UPD_CURSOR=numberofrows | Set this option along with the UPD_CONCURRENCY=LOCK option to perform multiple-row fetches. See the section Multiple-row Fetches for more information. |
| [NO]TARGETDB={MSQLSERVER | ORACLEOCI | ORACLE | INFORMIX | SYBASE | DB2 | ORACLE7} | Set this directive if you want to optimize performance for a specific data source or have the application generate database calls using ORACLE OCI rather than ODBC calls. |
| UPD_CONCURRENCY=LOCK | Set this to use pessimistic locking with COBOL cursors. This feature is only available under MS SQL Server. See the section Pessimistic Locking with COBOL Cursors for more information. |
See the topic SQL Compiler Directive Options for information on ODBC-only SQL Compiler directive options.
You can specify pessimistic locking with COBOL Cursors under ADO.NET when using MS SQL Server. To do this, compile using the SQL Compiler directive as follows:
SQL(DBMAN=ADO TARGETDB=MSSQLSERVER BEHAVIOR=ANSI UPD_CONCURRENCY=LOCK)
You can retrieve more than one row at a time using pessimistic locking under ADO.NET by compiling using the SQL Compiler directive as follows:
SQL(DBMAN=ADO TARGETDB=MSSQLSERVER BEHAVIOR=MAINFRAME UPD_CONCURRENCY=LOCK PF_UPD_CURSOR=numberofrows)
Where numberofrows is the number of rows you want to simultaneously fetch from the database server to the OpenESQL managed runtime. Rows are returned to the application one row at a time unless the application specifies an array FETCH.
Before your program can access any data in a database, it must make a connection to the database.
Your program must use the following method to connect to a database:
The CONNECT statement is typically used if the program is designed to access different data sources whose names are not known at compilation time or if the program is going to access multiple databases.
Note: Implicit connection and implicit disconnect and rollback are not currently implemented for ADO.NET data providers.
A number of ADO.NET demonstration projects are supplied in the Examples directory under your COBOL development system's base installation directory.
Before you can use any of the demonstration projects, you need to set up the data sources used by the demonstrations. You can do this with the OpenESQL Assistant Connection Editor. See the chapter OpenESQL Assistant for details.
The OpenESQL demonstration projects all produce a console log displaying their progress and, possibly, query results. They all terminate on receipt of an error, after displaying an error message.
ConnectedDemo uses the SQL Server Northwind database. You must set up a connection to the Northwind database to run the ConnectedDemo project.
The ConnectedDemo.cbl program fills a DataSet from a data source, modifies the DataSet and then synchronizes the changes in the DataSet with the data source. The modified contents of the DataSet are displayed in the Output Window.
ConnectedDemo demonstrates three methods for refreshing server generated primary keys in a DataSet after rows are added; a statement batch, a separate TO REFRESH statement and a stored procedure with output parameters. ConnectedDemo also shows you how to synchronize changes in the DataSet with the data source using either the main DataSet or a separate DataSet which contains only changed rows. You could use the latter method to minimize network traffic in a distributed application.
You must set up a connection to the SQL Server Northwind database to run the CursorDemo project. This project demonstrates how to intermix OpenESQL with direct calls to ADO.NET.
The TestADO.cs program performs a cursor fetch loop in C#. The NoFetch.cbl program uses EXEC SQL to open a cursor and then EXEC ADO to fetch the results. The TestCursor.cbl program performs an EXEC SQL FETCH. The CursorDemo.cbl program populates a DataTable.
The DisconnectedDemo.cbl program manipulates offline data in a DataSet. The results of the data manipulation are displayed in the Output Window.
DisconnectedDemo demonstrates how to declare a DataSet from an XSD file generated by another project and how to load a DataSet from an XML file generated by another application.
This project shows how ADO.NET can be used in mixed language projects to insert and select large objects (LOBs). LobDemo shows OpenESQL working with LOB columns in a SQL Server table.
LOB columns are columns that can be up to 2GB in size. In this demo, the LOB columns are image columns and the OpenESQL object reference host variable.
The LobDemo.cbl program includes instructions for changing LobDemo to use DB2 UDB and BLOB columns.
The OrderStatusDemo project shows how ADO.NET can be used in mixed language projects. OrderStatusDemo uses COBOL and C#.
The netorderstatus.cbl program generates an XSD file from a DataSet declaration. The XSD file is then imported into the C# form. This simplifies building the user interface by enabling data binding with a strongly typed DataSet generated from the XSD file.
OrderStatusDemo also demonstrates the use of ADO.NET in legacy reuse not directly related to DBMS access. The data source used in this project is an existing COBOL subroutine.
The techniques used in this demo are applicable to CICS ECI transactions.
We recommend that you use the SQL data types described in the Data Types chapter of your Database Access book.
Note: This is especially important when using ADO.NET for handling dates and times as COBOL PIC X fields.
We also support the use of the following .NET native data types as host variables. See the section .NET Native Data Types in the chapter Data Types of your Database Access book.
XML support is implemented in a different way for ADO.NET data providers. See the section Using Embedded EXEC ADO Statements for more information.
OpenESQL provides extended support for ADO.NET data providers with EXEC ADO statements. You can embed these statements into a COBOL program in the same way as EXEC SQL statements. Embedded EXEC ADO statements enable you to:
The four types of EXEC ADO statements are:
See the topic Embedded ADO Statements for more information on individual statements.
Disconnected model statements enable you to define, create and manipulate ADO.NET objects in COBOL. They are commonly used to:
The following disconnected model statements are available to you:
Note that, this statement does not commit changes to a database. To commit changes to a database, you must use the connected model to synchronize the DataSet with the database and then use the EXEC SQL COMMIT statement.
Note that, this statement does not roll back changes made to a database. To roll back changes in a database, you must use the connected model to synchronize the DataSet with the database and then use the EXEC SQL ROLLBACK statement.
Connected model statements enable you to interact with a data source. The FILL commands retrieve data from a data source and place it into the DataTables of a DataSet. The UPDATE commands ensure that any Data Manipulation Language (DML) changes (INSERT, UPDATE, and DELETE) are returned to the data source.
The following connected model statements are available to you:
ADO.NET cursor statements enable you to define, create and manipulate an ADO.NET DataRows cursor. These statements are similar to SQL cursor statements. However, instead of a selection criteria based on a table in a data source, an ADO.NET DataRows cursor is based on a DataTable in a DataSet.
The following ADO.NET cursor statements are available to you:
Sharing ADO.NET objects statements enable you to share ADO.NET objects (Connections, DataSets and DataReaders) between COBOL programs and other .NET applications. The GET statements retrieve objects OpenESQL has generated for EXEC SQL commands where dbman=ado. The BIND statements allow COBOL to access ADO.NET objects created by other .NET applications and the UNBIND statements disassociate COBOL from those objects.
The following sharing ADO.NET objects statements are available to you:
Copyright © 2009 Micro Focus (IP) Ltd. All rights reserved.