DB2 Server for VSE & VM: Application Programming

Rules for Using SQL in FORTRAN

The FORTRAN SQL preprocessor supports programs written for the VS FORTRAN compiler with the LANGLVL (77) option specified. Only FIXED-FORM source statements are supported.

If FORTRAN labels are placed on SQL declarative statements, the label will be removed and an information message given.

The FORTRAN preprocessor supports a maximum of 255 program units per input source file (254 subprograms in addition to the main program).

DB2 Server for VM
All the restrictions that apply to extended dynamic statements apply to all FORTRAN programs.

Placing and Continuing SQL Statements

All SQL statements must be placed in columns 7 to 72. Columns 73 to 80 may contain sequence numbers and information; columns 1 to 5 may also contain statement numbers.

The rules for continuation of tokens from one line to the next are the same as the FORTRAN rules for the continuation of words and constants.

An SQL statement may use up to 124 continuation lines in addition to the first line (for a total of 125 lines including blanks and comments). A continuation line can be:

• A continued line (that is, a line that does not have a blank or zero in column 6).
• A blank line
• A comment line.

These lines must fall between the start of the SQL statement and the next statement.

Notes:

1. The maximum length of an SQL statement is 8 192 characters
2. This restriction also applies to FORTRAN IF and ELSE statements
3. A statement is terminated by another statement or by end-of-file.

Placing Data Statements

The FORTRAN Release 3.0 compiler restricts the placement of data statements in FORTRAN programs or subroutines. Some precaution is necessary in order to eliminate the following warning message during compilation of the program or subroutine:

   WARNING MSGIFX1935I
   DATA STATEMENT PRECEDES AN EXPLICIT TYPE STATEMENT

During preprocessing, the FORTRAN preprocessor places inline calls at the end of the DB2 Server for VSE & VM declare section, if one exists; otherwise, the calls are placed at the beginning of the program or subroutine. These calls contain data statements that must be preceded by all declares.

If an SQL declare section does not exist, place the following dummy SQL declare section after all other program declares to avoid the above warning message:

   EXEC SQL BEGIN DECLARE SECTION
   EXEC SQL END DECLARE SECTION

Since the preprocessor replaces EXEC SQL INCLUDE SQLCA with the declaration of the SQLCA structure, the SQLCA must be included before the declare section.

The FORTRAN preprocessor does not recognize a FUNCTION keyword if it is preceded by a type declaration. The FUNCTION keyword must, therefore, be the first word in the FUNCTION statement.

Using FORTRAN Common Areas (DB2 Server for VSE)

For VSE single user mode, items in a FORTRAN COMMON statement must be initialized in a BLOCK DATA subroutine and the COMMON statement must be assigned a name.

Identifying Rules for Case

Mixed case can be used in your FORTRAN program. The SQL preprocessor will change the lowercase into uppercase, except for text within quotation marks, which will be left in the original case.

Declaring Host Variables

Host variables must be explicitly declared to be used in SQL statements. The following example shows an SQL declare section for a FORTRAN program:

   EXEC SQL BEGIN DECLARE SECTION  (at beginning of section)
         .
         .
   (Data description entries for host variables)
         .
         .
   EXEC SQL END DECLARE SECTION    (at end of section)

Place the data description entries for all the host variables within the SQL declare sections. You may use the variables appearing in these SQL declare sections in regular FORTRAN statements as well as in SQL statements.

A host variable declared within the SQL DECLARE SECTION may not be continued. The host variable declaration must appear on a single line in order to be recognized by the preprocessor.

You can also place data description entries for non-host variables in the SQL declare section as the FORTRAN preprocessor ignores data description entries within the SQL declare section that it does not recognize as valid host variable declarations. No error message is generated; instead, the statement is left for the FORTRAN compiler to process. Thus it is possible, though not recommended, to place all data description entries within an SQL declare section.

The rules for declaring variables within SQL declare sections are:

• Host variables must be valid FORTRAN variable names according to the version of the FORTRAN compiler that is being used. FORTRAN host variable names are restricted to a length of 18 bytes.
• Variables named in the SQL declare sections must have data descriptions like those in Figure 125.
• Variables cannot be any of the following:
  • Vector or array declarations
  • Constants defined by a PARAMETER statement
  • Any declarations that use expressions to define the length of the variables
  • Character variables declared with an undefined length, such as CHARACTER*(*).
• You should not give any variable a name beginning with SQL, because these names are reserved for database manager use.
• When host variables are declared as INTEGER, and you are using the OPTIMIZE(2) or OPTIMIZE(3) compile option, the host variables should be declared as COMMON.

  Under OPTIMIZE 2 or 3, FORTRAN may make register assignments to the program variables if they are not in COMMON storage. Under some circumstances, this can result in the database manager using an inaccurate variable value.

  In the following example, NUM must be declared as COMMON if OPTIMIZE 2 or 3 is specified:

         EXEC SQL DECLARE CURSOR C1 FOR INSERT INTO T1 VALUES (:NUM)
         EXEC SQL OPEN C1
         DO 20  NUM=1,10
            EXEC SQL PUT C1
   20    CONTINUE
   *
         EXEC SQL CLOSE C1
  

• Only the NONE value of the AUTODBL FORTRAN compile option is supported. AUTODBL changes the precision of declared variables without altering the source code. The preprocessor runs before the FORTRAN compiler and interprets variable types based strictly on their declaration.

A host variable must be declared earlier than the first use of the variable in an SQL statement in the program.

Embedding SQL Statements

You must precede each SQL statement in your program with EXEC SQL. No delimiter should be used at the end of each statement.

FORTRAN source statements and SQL statements cannot be contained on the same line or within the same continued statement, except when an SQL statement is used as the imperative statement of a logical IF. Also, only one SQL statement can be contained in a single line, or within the same continued statement.

Using Host Variables in SQL Statements

When you place host variables within an SQL statement, you must precede each one by a colon (:), to distinguish it from the SQL identifiers (such as a column name). When you place a host variable outside of an SQL statement, do not use a colon.

A host variable can represent a data value, but not an SQL identifier. For example, you cannot assign a character constant such as 'MUSICIANS' to a host variable, and then use that host variable in a CREATE TABLE statement to represent the table name. This pseudocode sequence is invalid:

Using Variable Length Character Strings

FORTRAN does not support variable length character strings (VARCHAR, LONG VARCHAR). However, it is possible to circumvent this restriction in the following way:

1. Declare INTEGER*2 to contain the length of the string
2. Declare a CHARACTER*(length) string of data
3. Declare a CHARACTER*(2 + length of string)
4. Declare a COMMON block containing (1) and (2) above
5. Use the EQUIVALENCE statement (name of (1) above, name of (3) above)
6. Specify a DATA BLOCK subroutine to initialize (1) and (2) above
7. When referencing the string in an SQL statement, use (3) above.
8. After preprocessing the FORTRAN program (but before compilation), change all occurrences of the data code for the variable(s) in the input or output data structure(s) from the CHARACTER data code to the corresponding VARCHAR or LONG VARCHAR data code. For information on how to interpret the data codes returned in SQLTYPE, see the DB2 Server for VSE & VM SQL Reference manual.

Figure 123 shows an example of how to INSERT a row into the INVENTORY table using a VARCHAR variable for description.

Note:

It is necessary to set the length field (STRNGL) to the corresponding length of the character string (STRING) before the insert statement is executed.

When the character string is fetched, the first two bytes of the string (STRNGW) contain the length. The variable STRNGL determines the length.

Figure 123. Using a VARCHAR Variable

C*** DB2 Server for VSE & VM STATEMENT *** C EXEC SQL BEGIN DECLARE SECTION CHARACTER ID*8 CHARACTER PW*8 INTEGER*2 STRNGL CHARACTER*24 STRING CHARACTER*26 STRNGW COMMON /SDATA/ STRNGL,STRING EQUIVALENCE (STRNGL,STRNGW) ... ... C*** DB2 Server for VSE & VM STATEMENT *** C EXEC SQL END DECLARE SECTION ... ... C*** DB2 Server for VSE & VM STATEMENT *** C EXEC SQL INSERT INTO SQLDBA.ACTIVITY C 1 VALUES(190, 'TSTSYS',:STRNGW) C SQI002( 3, 1) = 1 SQI002( 1, 2) = 452 * SQSHHW + 26 ---> Change 452 to 448 SQI002( 2, 2) = SQLADD(STRNGW) SQI002( 3, 2) = 0 SQCALL = 'EXECUTE ' SQSTMT = -1 SQLTYP = '0' SQLCTL(1) = SQLADD ( SQCALL ) SQLCTL(2) = SQLADD ( SQCOLL ) SQLCTL(3) = SQLADD ( SQPROG ) SQLCTL(4) = SQLADD ( SQSTMT ) SQLCTL(5) = SQLADD ( SQI002 ) SQLCTL(6) = 0 SQLCTL(7) = 0 SQLCTL(8) = 8 SQLCTL(9) = SQLADD ( SQLISL ) SQLCTL(10) = SQLADD ( SQLDAT ) SQLCTL(11) = SQLADD ( SQLTIM ) SQLCTL(12) = SQLADD ( SQLCNT ) SQLCTL(13) = SQLADD ( SQLTYP ) CALL ARIFOR ( SQLCTL ) ... ... END ********************************************************************* * BLOCK DATA SUBROUTINE ********************************************************************* BLOCK DATA COMMON /SDATA/ STRGNL,STRING INTEGER*2 STRGNL/3/ CHARACTER*24 STRING/'SYSTEM TESTING'/ END

Using DBCS Characters in FORTRAN

The rules for the format and use of DBCS characters in SQL statements are the same for FORTRAN as for other host languages supported by the system. For a discussion of these rules, see Using a Double-Byte Character Set (DBCS).

FORTRAN does not provide a way to define graphic host variables. If you want to add graphic data to or retrieve it from DB2 Server for VSE & VM tables, you must execute the affected statements dynamically. By doing so, the data areas that are referenced by each statement can be described in an SQLDA. In the SQLDA, you must set the data type of the areas containing graphic data to one of the graphic data types. (For a discussion of the SQLDA, refer to the DB2 Server for VSE & VM SQL Reference manual.)

Using the INCLUDE Statement

To include the external secondary input, specify the following at the point in the source code where the secondary input is to be included:

   EXEC SQL INCLUDE text_name

Text_name is the G-Type source member of a VSE library. Text_name is the file name of a CMS file (with a "FORTCOPY" file type) located on a CMS minidisk accessed by the user.

Using FORTRAN Variables in SQL: Data Conversion Considerations

Host variables must be type-compatible with the columns with which they are to be used.

A column of type INTEGER, SMALLINT, or DECIMAL is compatible with a FORTRAN variable of INTEGER, INTEGER*2, or INTEGER*4. Of course, an overflow condition may occur if, for example, an INTEGER data item is retrieved into an INTEGER*2 variable, and its current value is too large to fit.

Fixed-length and varying-length character data (CHAR, VARCHAR, and LONG VARCHAR) are considered compatible. A varying-length string is automatically converted to a fixed-length string, and a fixed-length string is automatically converted to a varying-length string, when necessary. If a varying-length string is converted to a fixed-length string, it is truncated or padded on the right with blanks to the correct length.

The database manager also considers the datetime data types to be compatible with character data types (CHAR and VARCHAR, but not LONG VARCHAR and VARCHAR > 254).

Refer to Converting Data for a data conversion summary.