ODBC usage in .NET

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

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

Preparation

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

1) You will need to perform the following steps:

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

We will not review the steps necessary toconfigure the Fujitsu NetCOBOL for .NET environment to enable it toaccess the database via ODBC. The necessary files have already beencreated for you and are contained with in the ZIP file. The ODBCinterface file is called ODBCSamp.INF and the COBOL runtime file iscalled COBOL85.CBR. Both of these files are located in the \bin\debugdirectory. The steps necessary to create these files however are:

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

The Hard Stuff

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

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

In NetCOBOL for .NET, the COBOL projectcreated is a ConsoleApplication and is a standard COBOL program. Theprogram utilizes ACCEPT/DISPLAY statements to interact with the userand embedded SQL statements to access the database. In theWORKING-STORAGE SECTION, the definition of the host variables isaccomplished within the BEGIN DECLARE section as follows:

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

This is really the core of the article, thedefinition of the variables within COBOL to access the ODBC data types.NetCOBOL for .NET will recognize the definitions within COBOL, andthrough marshalling determine how to access and format the data fromthe SQL Server table. Within the NetCOBOL documentation is a tablerepresenting the relationships and providing further detail. Utilizingthe CONTENTS tab of the help system, navigate to the NetCOBOL for .NETReferences and there you will see an entry for Mapping ODBC Data toCOBOL Data. A little hint, until you become familiar with the datamapping, print out this page of the documentation. The NetCOBOLdocumentation system contains a wealth of information. Spend some timesearching through it and you'll find quite a few examples of how toaccomplish a great many tasks.

Accessing the Data

We will not review the PROCEDURE DIVISION ingreat detail. The intent of the article was to present how to establishCOBOL data types that will access ODBC data types and this isaccomplished in the WORKING-STORAGE SECTION with the BEGIN DECLARESECTION and the definition of the HOST variables. The PROCEDUREDIVISION performs the following tasks:

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

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

Wrap Up

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

Happy Coding!

Download Source

About the Author:

Rick Malek is a Systems Engineer with FujitsuSoftware based in Minot, North Dakota. While his primary duties arepre-sales support, Rick also does Consulting and Training. He hasworked with COBOL since 1984, originally working on an IBM mainframewith CICS, VSAM and DB2. You can reach Rick at rmalek@fsw.fujitsu.com.