Programming Fortran applications that issue SQL statements
You can code SQL statements in a Fortran program wherever you can place executable statements. If the SQL statement is within an IF statement, the precompiler generates any necessary THEN and END IF statements.
Fortran source statements must be fixed-length 80-byte records. The DB2® precompiler does not support free-form source input.
Each SQL statement in a Fortran program must begin with EXEC SQL. The EXEC and SQL keywords must appear on one line, but the remainder of the statement can appear on subsequent lines.
You might code the UPDATE statement in a Fortran program as follows:
EXEC SQL
C UPDATE DSN8A10.DEPT
C SET MGRNO = :MGRNUM
C WHERE DEPTNO = :INTDEPT
You cannot follow an SQL statement with another SQL statement or Fortran statement on the same line.
Fortran does not require blanks to delimit words within a statement, but the SQL language requires blanks. The rules for embedded SQL follow the rules for SQL syntax, which require you to use one or more blanks as a delimiter.
- Comments
- You can include Fortran comment lines within embedded SQL statements
wherever you can use a blank, except between the keywords EXEC and
SQL. You
can include SQL comments in any embedded SQL statement.
The DB2 precompiler does not support the exclamation point (!) as a comment recognition character in Fortran programs.
- Continuation for SQL statements
- The line continuation rules for SQL statements are the same as those for Fortran statements, except that you must specify EXEC SQL on one line. The SQL examples in this topic have Cs in the sixth column to indicate that they are continuations of EXEC SQL.
- Delimiters in Fortran
- Delimit an SQL statement in your Fortran program with the beginning
keyword EXEC SQL
and an end of line or end of last continued line.
- Declaring tables and views
- Your Fortran program should also include the DECLARE TABLE statement to describe each table and view the program accesses.
- Dynamic SQL in a Fortran program
- In general, Fortran programs can easily handle dynamic SQL statements.
SELECT statements can be handled if the data types and the number
of returned fields are fixed. If you want to use variable-list SELECT
statements, you need to use an SQLDA, as described in Defining SQL descriptor areas.
You can use a Fortran character variable in the statements PREPARE and EXECUTE IMMEDIATE, even if it is fixed-length.
- Including code
- To include SQL statements or Fortran host variable declarations
from a member of a partitioned data set, use the following SQL statement
in the source code where you want to include the statements:
EXEC SQL INCLUDE member-name
You cannot nest SQL INCLUDE statements. You cannot use the Fortran INCLUDE compiler directive to include SQL statements or Fortran host variable declarations.
- Margins
- Code the SQL statements between columns 7 through 72, inclusive. If EXEC SQL starts before the specified left margin, the DB2 precompiler does not recognize the SQL statement.
- Names
-
You can use any valid Fortran name for a host variable. Do not use external entry names that begin with 'DSN' or host variable names that begin with 'SQL'. These names are reserved for DB2.
Do not use the word DEBUG, except when defining a Fortran DEBUG packet. Do not use the words FUNCTION, IMPLICIT, PROGRAM, and SUBROUTINE to define variables.
- Sequence numbers
- The source statements that the DB2 precompiler generates do not include sequence numbers.
- Statement labels
- You can specify statement numbers for SQL statements in columns 1 to 5. However, during program preparation, a labeled SQL statement generates a Fortran CONTINUE statement with that label before it generates the code that executes the SQL statement. Therefore, a labeled SQL statement should never be the last statement in a DO loop. In addition, you should not label SQL statements (such as INCLUDE and BEGIN DECLARE SECTION) that occur before the first executable SQL statement, because an error might occur.
- WHENEVER statement
- The target for the GOTO clause in the SQL WHENEVER statement must be a label in the Fortran source code and must refer to a statement in the same subprogram. The WHENEVER statement only applies to SQL statements in the same subprogram.
- Special Fortran considerations
- The following considerations apply to programs written in Fortran:
- You cannot use the @PROCESS statement in your source code. Instead, specify the compiler options in the PARM field.
- You cannot use the SQL INCLUDE statement to include the following statements: PROGRAM, SUBROUTINE, BLOCK, FUNCTION, or IMPLICIT.
DB2 supports Version 3 Release 1 (or later) of VS Fortran with the following restrictions:- The parallel option is not supported. Applications that contain SQL statements must not use Fortran parallelism.
- You cannot use the byte data type within embedded SQL, because byte is not a recognizable host data type.
Handling SQL error return codes in Fortran
You can use the subroutine DSNTIR to convert an SQL return code into a text message. DSNTIR builds a parameter list and calls DSNTIAR for you. DSNTIAR takes data from the SQLCA, formats it into a message, and places the result in a message output area that you provide in your application program. For concepts and more information on the behavior of DSNTIAR, see Displaying SQLCA fields by calling DSNTIAR.
You can also use the MESSAGE_TEXT condition item field of the GET DIAGNOSTICS statement to convert an SQL return code into a text message. Programs that require long token message support should code the GET DIAGNOSTICS statement instead of DSNTIAR. For more information about GET DIAGNOSTICS, see Checking the execution of SQL statements by using the GET DIAGNOSTICS statement.
DSNTIR syntax
CALL DSNTIR ( error-length, message, return-code )
- error-length
- The total length of the message output area.
- message
- An output area, in VARCHAR format, in which DSNTIAR places the
message text. The first halfword contains the length of the remaining
area; its minimum value is 240. The output lines of text are put into this area. For example, you could specify the format of the output area as:
where ERRLEN is the total length of the message output area, ERRTXT is the name of the message output area, and ICODE is the return code.INTEGER ERRLEN /1320/ CHARACTER*132 ERRTXT(10) INTEGER ICODE ⋮ CALL DSNTIR ( ERRLEN, ERRTXT, ICODE )
- return-code
- Accepts a return code from DSNTIAR.
An example of calling DSNTIR (which then calls DSNTIAR) from an application appears in the DB2 sample assembler program DSN8BF3, which is contained in the library DSN8A10.SDSNSAMP. See Sample applications supplied with DB2 for z/OS for instructions on how to access and print the source code for the sample program.