Client/Server on the Web

Chapter 4: Data Access

Applications written in COBOL are traditionally heavily data-driven, relying on substantial joint file processing or relational database management system (RDBMS) access. This chapter explains how to use the enhanced data access features available in NetExpress.

4.1 Challenge: Accessing an RDBMS

There can be many areas of difficulty in accessing a relational database from a COBOL program in a client/server application architecture. Perhaps the most direct and flexible technique is to use the Open Database Connectivity (ODBC) drivers provided by the database vendor. But calling these directly means understanding how to translate your query into API calls, how to format data and how to adhere to the calling conventions used. Fortunately, you can avoid all these issues by using the powerful data access features provided in NetExpress.

The recommended way of accessing relational data from a COBOL program is to use Open Embedded SQL, which enables you to embed Structured Query Language (SQL) statements within EXEC and END-EXEC statements directly in your program. The embedded statements are preprocessed by an SQL preprocessor, and converted to calls to your Open Database Connectivity (ODBC) device driver.

4.1.1 Solution: OpenESQL

Open Embedded SQL is a mechanism used in NetExpress that enables you to write standard SQL statements and embed them between EXEC and END-EXEC statements in your COBOL programs. Communication between your program and the database is established using ODBC, a de facto industry-standard call interface that has been embraced and promoted by Microsoft for databases. Because your program communicates only with the ODBC driver, it does not have to be aware of the nature of the database, and so a database connected through an ODBC driver is usually referred to as an ODBC data source.

4.1.1.1 Practical Considerations

OpenESQL provides a powerful and extremely simple way of communicating with an RDBMS. However, there are a number of practical considerations that you need take into account for an application to work effectively.

Devising the SQL statements you need

Of course, you can also write your own SQL statements or modify those produced by OpenESQL Assistant. Most vendors provide SQL Reference documentation with their ODBC driver software.You can find more information about the supported statements, including their full syntax, in this documentation. There are a number of useful sources of SQL information on the Web. You can find these by entering SQL as the search string in your favorite Web search engine - for example, Yahoo! or Alta Vista. To get you started, try https://www.microfocus.com/NetExpress/nxbooks/links.htm#sql1

We advise you to use only a single SQL statement in each EXEC SQL / END-EXEC statement. For example, if you want to define a cursor and fetch multiple rows from a database, we advise you to use several EXEC SQL statements, as follows:

 EXEC SQL
 DECLARE mycursor CURSOR
 FOR SELECT mycolumn_NAME FROM mytable
 END-EXEC

 EXEC SQL
 OPEN mycursor
 END-EXEC

 EXEC SQL
 FETCH mycursor INTO :myfirstname
 END-EXEC

 EXEC SQL
 FETCH mycursor INTO :mysecondname
 END-EXEC

 EXEC SQL
 CLOSE mycursor
 END-EXEC

Some ODBC drivers enforce this recommendation by not accepting API calls that result from more than one SQL statement per EXEC SQL / END-EXEC statement.

Connecting to the database

Before your program can access data in an ODBC data source, it must make a connection to the database. There are two methods you can use to connect to a database:

• Implicit connection.

  Use this in the simple case where your program connects to only one database, and always uses the same username. You need to know the name of the ODBC data source, the username and the password at the time you build your program. You can specify these parameters using the SQL compiler directive - for example:

  SQL(DBMAN=ODBC,INIT,DB=gasops,PASS=public.meters)

  Alternatively, you can enter the parameters in a dialog box by clicking the Advanced pushbutton on the Compile tab of the Build Settings for your .cbl program file.

• Explicit connection.

  If your program needs to connect to more than one database, or needs to connect using more than one username, you should make an explicit connection using the CONNECT statement in your ESQL. For example:

   EXEC SQL
   CONNECT TO "gasops" USER "public.meters"
   END-EXEC

When your program has finished working with a database, it should disconnect from the database. If you are using implicit connection, OpenESQL automatically disconnects from the data source when the program terminates. If you are using explicit connection, you should disconnect using the DISCONNECT statement in your ESQL:

Host variables

Host variables are data items defined in a COBOL program and used as variable parameters in the ESQL section of the program. They are used to pass values to and receive values from an ODBC data source. Host variables can be defined in the File Section, Working-Storage Section, Local-Storage Section or Linkage Section of your COBOL program and have any level number between 1 and 48. Level 49 is reserved for data items of the SQL data type varchar. When a host variable name is used within an ESQL statement, the data item name must begin with a colon (:) to enable the Compiler to distinguish between host variables and tables or columns with the same name. Host variables can be used in ESQL statements wherever ODBC allows a parameter marker (indicated in your ODBC driver documentation by a question mark, ?). To avoid potential problems with some ODBC drivers, we recommend that you restrict the length of data-names of host variables to 26 characters or less.

Date and time formats

COBOL has no predefined date or time data types that correspond to types such as datetime or timestamp that are typically found in SQL databases. For this reason, OpenESQL converts these formats to their character representations using a pic x(n) data item. The ODBC standard defines the character format of dates and times as yyyy-mm-dd for a date, and hh:mm:ss for a time. Note that these might not correspond to the native date/time formats for the data source in use.

COBOL does not allow the use of the characters "-" and ":" in an edited field, so they must be defined using a group data item. However, the ODBC standard requires that group data items are expanded into their constituent parts when passed to the database. It is therefore necessary to redefine the group item to create a single data item. For example:

 01 mydate.
    03   myyear   pic x(4).
    03   filler   pic x value "-".
    03   mymonth  pic x(2).
    03   filler   pic x value "-".
    03   myday    pic x(2).
 01 SQLmydate redefines mydate pic x(10).

The Gas Services example later in this chapter shows how you can ACCEPT a date and pass it to the database in the correct form.

Varchar data type

The SQL varchar data type consists of a character string of variable length. There are two ways of defining a COBOL equivalent:

• Define a fixed-length character data item pic x(n). where n is the maximum length of the string.

• Define a group item with usage comp or comp-5, where the first item contains the length of the string and the second item contains the string itself. Both items must have a level number of 49. For example:

   01 varchar1.
      49 varchar1-len      pic 9(4) comp-5.
      49 varchar1-data     pic x(200).
  
   01 Longvarchar1.
      49 Longvarchar1-len  pic 9(4) comp.
      49 Longvarchar1-data pic x(30000).

If the data being copied to an SQL char, varchar or long varchar data type is longer than the defined length, the data is truncated and the sqlwarn1 flag in the sqlca data structure is set. If the data is smaller than the defined length, a receiving char data type might be padded with blanks.

Error handling

If an SQL statement fails to complete, an error code is returned to your program. You should check for an SQL error after each SQL statement. The ODBC driver returns the error information in a data block called the SQL communications area, or sqlca. The sqlca contains two variables (sqlcode and sqlstate) plus a number of warning flags that indicate whether an error has occurred in the most recently executed SQL statement. For more detailed information on the SQL communications area (sqlca) and the sqlstate variable, look up sqlcode and sqlstate in the help index.

Testing the value of sqlcode is the most common way of determining the success or failure of an embedded SQL statement. The possible values for sqlcode are:

To include the sqlca data block in your program, use the EXEC SQL INCLUDE SQLCA END-EXEC statement in the data division, as in the following example:

working-storage section.
...
EXEC SQL INCLUDE SQLCA END-EXEC

procedure division.
EXEC SQL
  SELECT company, city INTO :pcompany, :pcity
                       FROM customer
                       WHERE custid = :pcustid
END-EXEC

if sqlcode not = 0
  if sqlcode = 100
    display "No customer found"
  else
    display sqlcode
    display sqlerrmc
  end-if
else
  display "Company for " pcustid " is " pcompany
  display "City    for " pcustid " is " pcity
end-if

Explicitly checking the value of sqlcode or sqlstate after each embedded SQL statement can involve writing a lot of code. An alternative is to check the status of the SQL statement by using a WHENEVER statement in your program. The WHENEVER statement is not an executable statement; it is simply a way of saving you typing by directing the Compiler to automatically generate code to handle errors after each executable embedded SQL statement.

A further refinement is to declare a data item called MFSQLMESSAGETEXT. If this data item exists, it is updated with a description of the exception condition whenever sqlcode is non-zero. MFSQLMESSAGETEXT must be declared as a character data item, pic x(n), where n can be any legal value. This is particularly useful as ODBC error messages often exceed the 70-byte sqlca message field. Note that you do not need to declare sqlca, sqlcode, sqlstate or MFSQLMESSAGETEXT as host variables:

working-storage section.
01 MFSQLMESSAGETEXT     pic x(512).
EXEC SQL INCLUDE SQLCA END-EXEC

procedure division.
EXEC SQL WHENEVER SQLERROR PERFORM ERROR-PROC END-EXEC

EXEC SQL
...
END-EXEC

stop run.

error-proc.
display "SQL Error"
display "SQLCODE = " sqlcode
display "SQLERRMC = " sqlerrmc
display "MFSQLMESSAGETEXT = "
display mfsqlmessagetext.

Porting to UNIX

If you are developing a program that will be ported to a UNIX server, we recommend that you code and debug using NetExpress on Windows, and recompile the finished program on UNIX using the RDBMS vendor's COBOL ESQL precompiler for the target database. You need to ensure that you use only SQL statements and data types that are supported by the target database. In practise, most precompilers support ANSI SQL92 syntax, but you should be particularly careful in the following areas:

• CONNECT and DISCONNECT statements
• COMMIT and ROLLBACK statements
• Functions such as MAX or AVERAGE.

You must also take care to use COBOL data types that are portable to UNIX. In particular, be careful when using COBOL data items with usage comp-5. These data items might not port as you expect, because of the different default byte-ordering between the two platforms. Use comp-x (or display) instead.

4.1.1.2 Example

The Web application for Phase 1 of the Gas Services scenario (see the section Example Scenarios for more details) provides a simple example of an OpenESQL query embedded in a COBOL server-side program. In this case, the application updates an SQL database with data entered by the user on an HTML form; then it queries the database to return the updated information back to the user. The query is not necessary to obtain the information, but is used to provide reliable confirmation that the database was updated correctly.

The database, which has the ODBC data source name gasops, is updated by adding a row to a table called transact. This table has one row for each gas transaction, and consists of the following columns:

For the purposes of this example, we have made the following assumptions about the database:

• The unique transaction number for each row of the table must be calculated by the COBOL program (that is, the database does not create this number automatically)

• Date columns do not default to the current date

The entry is confirmed to the user by querying the table for the latest meter reading on the specified account number.

4.1.1.2.1 Program structure

In order to keep the user interface logic (contained in the CGI program) and the business logic separate, we created a separate module to handle the interaction with the database. The tasks performed by this module are as follows:

1. Connect to the database.
2. Get a new transaction number.
3. Update the database with the customer's new meter reading.
4. Read the database to check that the update has been correctly made.
5. Disconnect from the database.

With this in mind, we wrote the skeleton of a new program, metersql.cbl, as follows:

 data division.
 working-storage section.

 linkage section.
 copy "mycgi.cpy".
 01 Outputdate    pic x(10).

 procedure division using FormFields,
                          Outputdate.
 perform dbconnect
 perform dbgettrans
 perform dbupdate
 perform dbread
 perform dbdisconnect
 exit program.
 stop run.

 dbconnect section.

 dbgettrans section.

 dbupdate section.

 dbread section.

 dbdisconnect section.

This program will be called from the CGI program and will return its results to the CGI program. The only data items in which the CGI program knows about when it is first generated are those that are represented on the input form. These are defined in the copyfile mycgi.cpy that was automatically generated when the form was created. We can obtain access to these data items simply by including the copyfile in the linkage section of our new program. However, there is a further data item that we want to return to our HTML output page that is not represented on the form: the current date. We can arrange to pass this back to our CGI program by defining it in the working-storage section of the CGI program, and using it as one of the calling parameters of metersql.cbl. This means that it must be declared in the linkage section of metersql.cbl.

Adding the SQL statements

We used OpenESQL Assistant to add the necessary SQL statements to our skeleton program.

We decided to add the ESQL statements first. The procedure was basically the same for all of them. For example, we added the INSERT statement as follows:

1. We clicked under the line in our program that says dbupdate section.
2. We clicked OpenESQL Assistant on the View menu of NetExpress.
3. We double-clicked on our ODBC data source in the left-hand pane.
4. We selected the correct data source name when the ODBC Select Data Source dialog box appeared.
5. We entered our userid and password on the SQL Server Login dialog box to gain access to the data source.
6. We double-clicked on the table called transact.
7. We chose INSERT on the Select Type of Query dialog box because we wanted to add a new row to the database. (The Select Type of Query dialog box tells you what each type of statement does when you click on it).
8. We right-clicked on the table transact in the left-hand pane, then clicked Select All Columns on the popup menu.
9. We clicked the icon to add the SQL statement to our program.

Figure 4-1: Adding the INSERT statement using OpenESQL Assistant

There were also two SELECT statements to add to our program: one to get the last transaction number and one to read back the data added to the database by our program.

The first of these was added to the dbgettrans section:

1. We chose a SELECT (Singleton) query type, because we wanted to retrieve a single row of data.
2. We double-clicked the column A.ACC_TRANS_NO in the left-hand pane.
3. We clicked the icon to add the SQL statement to our program.

However, the SELECT statement as generated retrieves all transaction numbers from the database table, and we only want the most recent. The most recent number will also be the highest number, so we modified the SQL statement to read:

 EXEC SQL
  SELECT
     MAX(A.ACC_TRANS_NO)
  INTO
     :transact-ACC-TRANS-NO
   FROM transact A
 END-EXEC

Although most data sources support a MAX function, the syntax used depends on the RDBMS. The syntax shown above is correct for our target system, Microsoft SQLServer.

The second SELECT statement we needed belongs in the dbread section of our program:

1. We chose a SELECT (Singleton) query type, because we wanted to retrieve a single row of data.
2. We double-clicked A.TRANS_UNITS in the left-hand pane.
3. We needed to narrow down the number of rows returned, so we clicked the Search Criteria tab.
4. In the Column list, we chose A.ACCOUNT_NO.
5. In the Target Value entry field, we typed :Input1
6. We clicked the > button to add this expression to the search criteria listbox.
7. In the Column list, we chose A.TRANS_TYPE.
8. We changed the Target Type to Literal.
9. In Target Value, we typed the number 9 (because 9 is the transaction type code for a customer meter reading in the Gas Services system).
10. We clicked the > button to add this criterion to the listbox.
11. We clicked the Query tab, then we clicked the icon to add the SQL statement to our program.

The SQL statement that was added looked like this:

EXEC SQL
 SELECT 
      A.TRANS_UNITS
 INTO 
      :transact-TRANS-UNITS
   FROM transact A
  WHERE ( A.ACCOUNT_NO = :Input1 )
   AND  ( A.TRANS_TYPE = 9 )
END-EXEC 

This query looks up the rows in the table transact where the account number corresponds to the user's account number, and the transaction type code is 9 (customer meter reading). In practise, a customer might have several rows of customer meter readings in the database (all taken at different times). So at this point the results set could consist of several rows. To filter the result down to the one row we want, we ordered the rows by descending transaction number, which also corresponds to their reverse chronological order. The results set could still be several (ordered) rows, but the SELECT keyword only ever returns a single row - the first row in the set, which in this case corresponds to the most recent customer meter reading. So we edited the generated SQL statement as follows:

 EXEC SQL
  SELECT 
       A.TRANS_UNITS
  INTO 
       :transact-TRANS-UNITS
    FROM transact A
   WHERE ( A.ACCOUNT_NO = :Input1 )
    AND  ( A.TRANS_TYPE = 9 )
  ORDER BY A.ACC_TRANS_NO DESC
 END-EXEC 

Host variables and form variables

OpenESQL Assistant makes it very simple to add the correct host variables to a program, and automatically ensures that the COBOL data types are correct for each column in the database table.

To add host variables to our program, we did the following:

1. We clicked under the line working-storage section.
2. We clicked the Auxiliary Code tab in OpenESQL Assistant.
3. We clicked the Host Variable Declarations radio button.
4. We clicked the icon to add the SQL statement to our program.

OpenESQL Assistant added a copyfile, transact.cpy to our program, containing host variable declarations for each column in the table.

The host variables that correspond to columns in the database have the same names as the columns (with underscores translated to hyphens), but are prefixed by the table name. So, for example, the column ACC_TRANS_NO has a corresponding host variable, transact-ACC-TRANS-NO. The table-name prefix is an option that you can change by clicking Embedded SQL on the Options menu of NetExpress.

The other variables used in this example are Input1, Input2 and Outputdate. These are all related to the input form and output page of the CGI program rather than with accessing the database, but as they are used to pass data between the two programs we had to ensure the data types were compatible.

Outputdate is a data item defined in the linkage section of metersql.cbl and the working-storage section of mycgi.cbl. It is used to pass the current date back to the CGI program. It can simply be defined as a pic x(10), the same as SQLtoday.

Input1 holds data entered in the first entry field of the input form. It contains the customer's account number, and corresponds to the ACCOUNT_NO column in the database. Input2 holds data entered in the second entry field of the input form. It contains the customer's meter reading, and corresponds to the column TRANS_UNITS.

The data declaration for columns in the transact table is contained in the copyfile transact.cpy, which was created by OpenESQL Assistant:

******************************************************************
      * COBOL DECLARATION FOR TABLE transact *
      ******************************************************************
 01  DCLtransact.
     03 transact-ACC-TRANS-NO            PIC S9(09)  COMP-5.
     03 transact-ACCOUNT-NO              PIC S9(09)  COMP-5.
     03 transact-TRANS-TYPE              PIC S9(09)  COMP-5.
     03 transact-TRANS-TEXT              PIC X(80).
     03 transact-TRANS-UNITS             PIC S9(09)V9(02) COMP-3.
     03 transact-TRANS-RATE              PIC S9(06)V9(02) COMP-3.
     03 transact-TRANS-VALUE             PIC S9(06)V9(02) COMP-3.
     03 transact-TRANS-TAX-RATE          PIC X(1).
     03 transact-TRANS-POST-DATE         PIC X(26).
     03 transact-TRANS-EFFECT-DATE       PIC X(26).

From this, you can see that we needed to define Input1 as a pic s9(9) comp-5 and Input2 as a pic s9(9)v9(2) comp-3.

However, the variables Input1 and Input2 were defined by Form Designer when we painted the input form. Their definitions have been added to the COBOL program in the copyfile gascgi.cpy as follows:

 01 FormFields.
       03 Input1                 PIC X(15).
       03 Input2                 PIC X(15).

You should not change the data definitions in this copyfile, as it is overwritten by any changes that you make to the form in Form Designer. Instead, you should open the form in Form Designer and change the COBOLPicture property for the relevant controls to correspond to their SQL data type equivalents. For example, we made the following change to the COBOLPicture property for the Input2 edit field:

Figure 4-2: Changing the COBOLPicture property in Form Designer

Similarly, we changed the COBOLPicture property of the ACNumber edit field to pic s9(9) comp-5.

Each control for which you specify a COBOLPicture has a corresponding data item that is used to communicate with the form. This data item is created automatically by Form Designer in the skeleton CGI program. It is always a pic x item, regardless of the data type you use in your program, and has the same name as the control, but with the prefix f-. For example, the second entry field in the Gas Services form has two related data items defined in the skeleton CGI program:

• 01 MeterReading pic s9(9)v9(2) comp-3.(defined in the copyfile gascgi.cpy)
• 01 f-MeterReading pic x(13). (defined in the copyfile gascgi.cpf)

The size of this data item is calculated from the COBOLPicture property - in our example, ACNumber is equivalent in size to a pic x(10), and MeterReading is equivalent in size to a pic x(13) data item.

Because HTML consists of plain text, the pic x data item is the only one that can be displayed by the browser. Data conversion routines in the skeleton CGI program move the data from f-MeterReading to MeterReading when the input form is submitted; and from MeterReading to f-MeterReading when the output form is displayed. Note that this means that if you create one of the forms outside of Form Designer (as we have done, because we wanted an HTML page for the output instead of a form), you must remember to use the correct data name (the one with the f- prefix) as a placeholder in the HTML.

The final program

The following listing shows the completed program for our prototype, with some added explanatory comments.

 data division.
 working-storage section.
 EXEC SQL INCLUDE transact END-EXEC

 01 transno         pic x(4) comp-x.
 01 today.
    03   thisyear   pic x(4).
    03   filler     pic x value "-".
    03   thismonth  pic x(2).
    03   filler     pic x value "-".
    03   thisday    pic x(2).
 01 SQLtoday redefines today pic x(10).
 01 acceptdate      pic x(8).
 01 dateparts redefines acceptdate.
    03 yearpart     pic x(4).
    03 monthpart    pic x(2).
    03 daypart      pic x(2).
 01 textnote.
    03 fixedtext    pic x(19) value "Customer reading - ".
    03 mreading     pic x(9).
*>SQLtextnote is needed to concatenate literal text (which in ESQL must
*>be specified in single quotes) and the host variable data (which
*>cannot be in quotes because it is a variable)
 01 SQLtextnote redefines textnote pic x(28).
 01 tempint         pic 9(9).

 linkage section.
*>This is the copyfile that was generated when we created the input
*>form
 copy "mycgi.cpy".
 01 Outputdate    pic x(10).

 procedure division using FormFields, Outputdate.
 accept acceptdate from date yyyymmdd
 move yearpart to thisyear
 move monthpart to thismonth
 move daypart to thisday
 perform dbconnect
 perform dbgettrans
 perform dbupdate
 perform dbread
 perform dbdisconnect
 move SQLtoday to Outputdate
 exit program.
 stop run.

 dbconnect section.
 EXEC SQL
   CONNECT TO 'boxtest' USER 'rjh.rtfm'
 END-EXEC
 .
 dbgettrans section.
 EXEC SQL
   SELECT
     MAX(A.ACC_TRANS_NO)
   INTO
     :transact-ACC-TRANS-NO
   FROM transact A
 END-EXEC
 add 1 to transact-ACC-TRANS-NO
 move function integer-part(Input2) to tempint
 move tempint to mreading
 .
 dbupdate section.
 move ACNumber to transact-ACCOUNT-NO
 move 9 to transact-TRANS-TYPE
 move SQLtextnote to transact-TRANS-TEXT
 move MeterReading to transact-TRANS-UNITS
 move SQLtoday to transact-TRANS-POST-DATE,
                  transact-TRANS-EFFECT-DATE
*>We have replaced three of the host variables in the following
*>statement with literals, because they are always the same in this
*>program
 EXEC SQL
   INSERT INTO transact
   (ACC_TRANS_NO
   ,ACCOUNT_NO
   ,TRANS_TYPE
   ,TRANS_TEXT
   ,TRANS_UNITS
   ,TRANS_RATE
   ,TRANS_VALUE
   ,TRANS_TAX_RATE
   ,TRANS_POST_DATE
   ,TRANS_EFFECT_DATE
   ) VALUES
   (:transact-ACC-TRANS-NO
   ,:transact-ACCOUNT-NO
   ,:transact-TRANS-TYPE
   ,:transact-TRANS-TEXT
   ,:transact-TRANS-UNITS
   ,0
   ,0
   ,'N'
   ,:transact-TRANS-POST-DATE
   ,:transact-TRANS-EFFECT-DATE
   )
 END-EXEC
 .
 dbread section.
 initialize Input2
 EXEC SQL
   SELECT
     A.TRANS_UNITS
   INTO
     :transact-TRANS-UNITS
   FROM transact A
   WHERE ( A.ACCOUNT_NO = :Input1 )
   AND  ( A.TRANS_TYPE = 9 )
   ORDER BY A.ACC_TRANS_NO DESC
 END-EXEC
 move transact-TRANS-UNITS to Input2
 .
 dbdisconnect section.
 EXEC SQL
   DISCONNECT CURRENT
 END-EXEC
 .

Building the application

Before we build the application, we had to enable OpenESQL support for the data handling module by setting the SQL Compiler directive. We did this by clicking the Advanced pushbutton on the Compile tab of the Build Settings for metersql.cbl. We made the following selections on the dialog box:

Figure 4-3: Adding the SQL Compiler directive using the Advanced Directives dialog box

When we clicked OK, the SQL Compiler directive (with appropriate parameters) was added to the list on the Compile tab of the Build Settings. These settings apply to this COBOL file only. An alternative way of achieving the same thing would be to add the following line of code to the program:

$set SQL(DBMAN=ODBC,AUTOCOMMIT,TARGETDB=MSSQLSERVER)

4.1.2 Solution: Internet Application Wizard

Internet Application Wizard provides a simple way of automatically generating an application for browsing and updating an ODBC data source. It takes you step by step through the process, asking questions to find out your requirements, then generates the HTML forms and CGI programs needed to run the application.

4.1.2.1 Practical Considerations

Client/Server on the Web