|
|
|
|
|
|
|
|
Up and Running with ALLBASE/SQL: HP 3000 and HP 9000 Computer Systems > Chapter 4 Building an ALLBASE/SQL\COBOL Application in MPE/iX
Creating an Embedded SQL COBOL Application |
|
This section describes how to create a simple application in COBOL under MPE/iX that accesses the Albums table in MUSICDBE. The application will prompt the user for an album code, and it will display the title, recording company, and any comment on the album. Before you start, make sure you have created the DBEnvironment MUSICDBE according to the instructions in the tutorial "Setting up a Database with ISQL" earlier in this book. First, log into the group and account in which you have created the DBEnvironment MUSICDBE. Then, use the following command to copy the template file TMPLCBL1 into your current working directory:
Use an editor to examine this file. At several locations in the file, you will see notations like the following:
In the sections that follow, you will insert specific sequences of SQL and/or COBOL statements following these comments in the template. Follow instructions carefully. The first SQL code to be embedded in the template is an INCLUDE statement, coded as follows:
Insert these lines in the template file so that the result looks like the following:
Note the use of the EXEC SQL prefix and the END-EXEC suffix. These are required before and after each SQL statement in the application. Be sure to begin in column 8, following COBOL conventions. The SQLCA is a communications area in which information is passed from ALLBASE/SQL to your application at run time. The SQLCA includes a variable known as SQLCODE, which can be examined to determine whether or not a particular SQL statement was successful. The variables which transfer data between your application and the DBEnvironment are known as host variables. You can use these variables both inside SQL statements and in ordinary program statements. When used in an SQL statement, a host variable must be preceded by a colon (:). You must declare host variables inside a host variable declaration section. This is simply an area of code that begins with a BEGIN DECLARE SECTION statement and ends with an END DECLARE SECTION statement. In the template file, insert the following host variable declarations following the words "Insert Host Variables here." Begin in column 8 for 01 level declarations.
This example shows a special indicator variable COMMENTIND with the data type SQLIND accompanying the COMMENT variable. You declare a variable of this kind to accompany each column that permits null values. If a null is encountered, the indicator variable is set to -1. If the column contains a non-null value, the indicator variable is set to 0. As in ISQL, you must CONNECT to the DBEnvironment before you can perform queries against a database. In the template file, code the following statement to perform the CONNECT:
Similarly, code the following statement to perform the RELEASE:
Note that you do not need to mention the name of the DBEnvironment in the RELEASE statement. The main routine in the sample program is a procedure named B100-SELECT-DATA. This routine prompts the user for an album code, then issues the following SELECT statement, which you should include in the template after the tag, "Insert Select Statement."
This statement performs a query on the Albums table, based on the value of ALBUMCODE, which is supplied by the user. Inside the SQL statement, a colon precedes each host variable. Elements that are not preceded by a colon are database objects--the table name in the FROM clause, and column names in the SELECT list and the WHERE clause. Note the placement of the indicator variable :COMMENTIND immediately following the host variable :COMMENT with no separation by a comma. During the SELECT, the indicator variable is set to -1 if the content of the corresponding column (COMMENT) is null. After the query has extracted information from the database, you need to present the data to the user in some fashion. One way to do this is to use a DISPLAY statement to write the properly formatted data to the screen. The following statements should be inserted into the template file to display the output of the SQL SELECT statement:
If the value in the Comment column in the Albums table is NULL, the indicator variable (COMMENTIND) will be set to -1. In this case, you initialize COMMENT, as the example shows. After including all the embedded SQL statements, save the file SHOWALBS. Then preprocess the file, as follows:
If the preprocessor has found SQL errors, including syntax errors, they will be included in a temporary file called SQLMSG, which you should examine. (Most COBOL syntax errors are not included; these are displayed later, at compile time.) You must correct any preprocessing errors before going on to the next step. The most common SQL errors come about because of misspelling of column names or host variable names. Another common cause of problems is omitting punctuation, such as commas in a SELECT list, colons before host variables, or periods at the end of each END-EXEC. When the preprocessing has completed without error, display the temporary files in your group (file sizes may differ from those shown in this example):
Notice that the preprocessor has created several temporary files in addition to SQLMSG. The file SQLOUT is the modified source file which you will soon compile. The files SQLVAR and SQLCONST contain definitions required for compiling and linking the program. Take a few moments to examine the content of these files with an editor. The file SQLMOD contains a module which could be installed in another DBEnvironment. This is a binary file which is used with the ISQL INSTALL command. Use the following command to compile and link the modified COBOL source program that was generated by the preprocessor:
This command runs the COBOL compiler and the MPE/iX linker to create an executable program SHOWALBP. If there are any compile or link errors, you must make corrections in the original source file, that is, SHOWALB, then you must repreprocess and recompile.
When the program compiles and links without error, you can execute it as follows:
This example assumes you have loaded the Albums table according to the instructions in "Setting Up a Database with ISQL." Run the program again, using different album codes between 2001 and 2010. The ALLBASE/SQL software includes a set of UDC's that let you preprocess and compile in one step. Here is an example, using the source program SHOWALBS:
After the program has compiled without error, you can perform ordinary debugging tasks by running the program and observing the results. Remember, though, that you must edit the embedded SQL source file, not the preprocessor output, when you modify the program. If you edit the modified source file generated by the preprocessor, your changes will be lost the next time the program is preprocessed, since the preprocessor will overwrite that file. Once the program runs correctly, skip ahead to the section entitled "Adding a Multiple Row SELECT Statement."
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||
|
|
|
|||||||||
|
|||||||||
