|Client/Server on the Web|
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 Net Express.
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 Net Express.
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.
Open Embedded SQL is a mechanism used in Net Express 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.
Technical: OpenESQL implementation
The SQL statements embedded in the EXEC SQL / END-EXEC statement in your program are processed by the OpenESQL preprocessor. The preprocessor handles the passing of data items (host variables) and converts the SQL into corresponding calls to the ODBC interface, implemented by means of an ODBC device driver. The ODBC driver for a particular database is normally supplied by the RDBMS vendor with their database, although third-party drivers are also available. The driver acts as an interpreter, translating standard ODBC calls into the native API calls required for the database. Because the vendor writes the driver, it might not implement the full ODBC standard, or it might use proprietary extensions to ODBC syntax in addition to the standard syntax. There are three ODBC API compliance levels and three ODBC SQL grammar compliance levels. Not all drivers, particularly those for desktop data sources, support advanced SQL features. The range of data types supported, and their names, also differs from driver to driver. All ODBC drivers should implement a minimum subset of functionality, but you should check the documentation for the target ODBC driver before you code your SQL program.
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.
You don't need to understand SQL to use the OpenESQL capabilities of Net Express. Net Express can generate the SQL needed for many applications using either OpenESQL Assistant or the Internet Application Wizard. Use the Internet Application Wizard to create the framework of an application for browsing and updating a database without writing any COBOL yourself. Use OpenESQL Assistant to add SQL statements to your own COBOL programs. For more information on OpenESQL Assistant, see the chapter Open ESQL Assistant in your Database Access online book.
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 http://www.merant.com/ads/docs/nx/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.
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:
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:
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.
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 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.
Note: If you design your input form in Form Designer and generate a skeleton CGI program using the Internet Application Wizard, host variables are automatically created to contain the data from any entry fields on the form. The host variable name is the same as the name that appears in the tree view of controls in Form Designer. You can control the data type of the host variable by setting the COBOLPicture property of the appropriate control.
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.
The SQL varchar data type consists of a character string of variable length. There are two ways of defining a COBOL equivalent:
pic x(n).where n is the maximum length of the string.
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.
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:
|0||The statement ran without error.|
|1||The statement ran, but a warning was generated. The values of the sqlwarn flags should be checked to determine the type of error.|
|100||Data matching the query was not found or the end of the results set was reached. No rows were processed.|
|< 0 (negative)||The statement did not run due to an application, database, system, or network error.|
To include the sqlca data block in your program, use the
INCLUDE SQLCA END-EXEC statement in the data division, as in the
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.
If you are developing a program that will be ported to a UNIX server, we recommend that you code and debug using Net Express 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:
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.
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:
|ACC_TRANS_NO||integer||A unique number that identifies each transaction|
|ACCOUNT_NO||integer||The customer account number involved in the transaction|
|TRANS_TYPE||integer||A code for the type of transaction|
|TRANS_TEXT||char(80)||A text description of the transaction|
|TRANS_UNITS||decimal(11,2)||The number of items involved in the transaction|
|TRANS_RATE||decimal(8,2)||The value per unit - if any - of the transaction|
|TRANS_VALUE||decimal(8,2)||The total value of the transaction|
|TRANS_TAX_RATE||char(1)||A code for the rate of tax to be applied|
|TRANS_POST_DATE||datetime||The date and time the transaction was entered|
|TRANS_EFFECT_DATE||datetime||The date and time the transaction takes effect|
For the purposes of this example, we have made the following assumptions about the database:
The entry is confirmed to the user by querying the table for the latest meter reading on the specified account number.
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:
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.
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:
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:
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:
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
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:
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 Net Express.
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
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 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 .
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:
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.
Technical: Internet Application Wizard implementation
Internet Application Wizard generates CGI programs that can read and write to an ODBC data source. It uses Embedded SQL to communicate with the data source, and one step of the wizard uses a version of OpenESQL Assistant to generate the SQL statements needed. You can modify the generated CGI code and HTML forms to suit your application. Look up CGI code generation in the online help Index for further information.
For each application that you want to create, you should consider whether it would be best to generate a program using Internet Application Wizard or to write your own program and use OpenESQL Assistant to generate the SQL. In general terms, Internet Application Wizard is most suitable for applications of the following types:
OpenESQL Assistant would be more suited to the following types of applications:
For more information on Internet Application Wizard, see the chapter Data Access Applications in your Internet Applications online book, or look up Internet Application Wizard in the online help Index.
Copyright © 2000 MERANT International Limited. All rights reserved.
This document and the proprietary marks and names used herein are protected by international law.
|Client/Server on the Web|