ODBC usage in .NET

Advertisements

COBOL and ODBC Data Types

This article will provide a template of how to represent a smallint, integer, decimal, varchar and other ODBC data types that are used in the SQL Server environment in COBOL.

 The use of relational database systems is nothing new to COBOL. For years developers have utilized DB/2 on the IBM Mainframes as well as Oracle. On the Microsoft platforms, SQL Server is the prevalent database of choice. For COBOL programmers on a Windows platform the issue has always been how to create variables that represent a smallint, integer, decimal, varchar or other such data types that are used in the SQL Server environment.

Preparation

Our environment consisted of Visual Studio 2003 Enterprise Edition, SQL Server 2000 and Fujitsu NetCOBOL for .NET V2.0.  There is a backup of the sample database in the zip file. The backup file is named coboldata.bkp. We will not review how to create or restore a SQL Server database in this article. For assistance with this please consult with your local IT personnel or DBA.

You will need to perform the following steps:

    Unzip the zip file into a clean directory.
    Create a database in SQL Server called COBOLData.
    Restore the file coboldata.bkp to the database created in Step #1.
    Create an ODBC data source pointing to COBOLData and call it ODBCSample.
    Rebuild the solution.

We will not review the steps necessary to configure the Fujitsu NetCOBOL for .NET environment to enable it to access the database via ODBC. The necessary files have already been created for you and are contained with in the ZIP file. The ODBC interface file is called ODBCSamp.INF and the COBOL runtime file is called COBOL85.CBR. Both of these files are located in the indebug directory. The steps necessary to create these files however are:

    Create an Interface file utilizing the NetCOBOL ODBC Setup Tool.
    Create a Runtime Initialize file using the NetCOBOL Run-Time Environment Setup Tool.
    Within the Runtime Environment Setup tool, set the variable @ODBC_INF to point to the INF file created in Step #1.
    Both of these Tools can be found under the TOOLS menu item.



The Hard Stuff

This article is about configuring COBOL data types to access ODBC data types. To begin with we have to have a table with variables in SQL Server. The table we will be using is CBLDatTbl within the COBOLData database. It is defined as follows:

Each of the data types we will be using in the sample is defined above. The naming convention used is 'db' and then the name of the data type, such as SmallInt, or Integer. For the CHAR and VCHAR data types we accepted the default size values of 10 and 50 respectively.

In NetCOBOL for .NET, the COBOL project created is a ConsoleApplication and is a standard COBOL program. The program utilizes ACCEPT/DISPLAY statements to interact with the user and embedded SQL statements to access the database. In the WORKING-STORAGE SECTION, the definition of the host variables is accomplished within the BEGIN DECLARE section as follows:

Notice each of the variables is also defined as the data type we are accessing with a 'WS-' preceding each name. Each of the names in the WORKING-STORAGE SECTION contain the same name as the database definition (except for the prefix), so it will make relating the two data definitions a bit easier.

This is really the core of the article, the definition of the variables within COBOL to access the ODBC data types. NetCOBOL for .NET will recognize the definitions within COBOL, and through marshalling determine how to access and format the data from the SQL Server table. Within the NetCOBOL documentation is a table representing the relationships and providing further detail. Utilizing the CONTENTS tab of the help system, navigate to the NetCOBOL for .NET References and there you will see an entry for Mapping ODBC Data to COBOL Data. A little hint, until you become familiar with the data mapping, print out this page of the documentation. The NetCOBOL documentation system contains a wealth of information. Spend some time searching through it and you'll find quite a few examples of how to accomplish a great many tasks.

Accessing the Data

We will not review the PROCEDURE DIVISION in great detail. The intent of the article was to present how to establish COBOL data types that will access ODBC data types and this is accomplished in the WORKING-STORAGE SECTION with the BEGIN DECLARE SECTION and the definition of the HOST variables. The PROCEDURE DIVISION performs the following tasks:

    Connects to the database
    Declares a CURSOR to retrieve the data
    Opens the cursor
    Executes an in-line PERFORM statement where a FETCH is done to retrieve the data from the SQL table.
    Disconnects from the database and then exits the program.

Within the PERFORM loop you will notice the FETCH statement returns the data INTO the host variables we defined. There is no further conversion work required. The only formatting of data is on the DECIMAL field to properly display the decimal point and values to the right of it.

Wrap Up

Accessing ODBC data from COBOL is a matter of proper definition. With the WORKING-STORAGE fields defined with the equivalent PIC clauses as the ODBC data types your programs should have no problems processing data. Feel free to utilize this sample to experiment with adding records, deleting records or updating data. And as mentioned before, spend some time with the NetCOBOL Documentation. There is a wealth of information there just waiting to help you.