Special host-language programming considerations are discussed in the following pages. Included is information on language restrictions, host language specific include files, embedding SQL statements, host variables, and supported data types for host variables.
The following sections describe the FORTRAN language restrictions.
Some API parameters require addresses rather than values in the call variables. The database manager provides the GET ADDRESS, DEREFERENCE ADDRESS, and COPY MEMORY APIs which simplify your ability to provide these parameters. See the API Reference for a full description of these APIs.
Some FORTRAN compilers treat lines with a 'D' or 'd' in column 1 as conditional lines. These lines can either be compiled for debugging or treated as comments. The precompiler will always treat lines with a 'D' or 'd' in column 1 as comments.
There are two methods for including files: the EXEC SQL INCLUDE statement and the FORTRAN INCLUDE statement. The precompiler will ignore FORTRAN INCLUDE statements, and only process files included with the EXEC SQL statement.
To locate the INCLUDE file, the DB2 FORTRAN precompiler searches the current directory first, then the directories specified by the DB2INCLUDE environment variable. Consider the following examples:
If the file specified in the INCLUDE statement is not enclosed in quotes, as above, the precompiler searches for payroll.sqf, then payroll.f (payroll.for on OS/2) in each directory in which it looks.
If the file name is enclosed in quotes, as above, no extension is added to the name. (For OS/2, the file would be specified as 'pay\payroll.for'.)
If the file name in quotes does not contain an absolute path, then the contents of DB2INCLUDE are used to search for the file, prepended to whatever path is specified in the INCLUDE file name. For example, with DB2 for AIX, if DB2INCLUDE is set to '/disk2:myfiles/fortran', the precompiler searches for './pay/payroll.f', then '/disk2/pay/payroll.f', and finally './myfiles/cobol/pay/payroll.f'. The path where the file is actually found is displayed in the precompiler messages. On OS/2, substitute back slashes (\) for the forward slashes, and substitute 'for' for the 'f' extension in the above example.
| Note: | The setting of DB2INCLUDE is cached by the DB2 Command Line Processor. To change the setting of DB2INCLUDE after any CLP commands have been issued, enter the TERMINATE command, then reconnect to the database and precompile as usual. |
The following items affect the precompiling process:
See the DB2 SDK Building Applications book for your operating environment for any other precompiling considerations that may affect you.
By default, the input file has an extension of .sqf, but if you use the TARGET precompile option the input file can have any extension you prefer.
By default, the output file has an extension of .f on UNIX-platforms, and .for on OS/2 and Windows-based platforms, however you can use the OUTPUT precompile option to specify a new name and path for the output modified source file.
The host-language specific include files for FORTRAN have the file extension .f on AIX and UNIX-based platforms, and .for on OS/2. For other platforms, consult the DB2 SDK Building Applications book for that platform for information on file extensions. The following are FORTRAN include files that are intended to be used in your applications.
Two SQLCA files are provided for FORTRAN applications. The default, sqlca_cs.f, defines the SQLCA structure in an IBM SQL compatible format. The sqlca_cn.f file, precompiled with the SQLCA NONE option, defines the SQLCA structure for better performance.
Embedded SQL statements consist of the following three elements:
The end of the source line serves as the statement terminator. If the line is continued, the statement terminator is the end of the last continued line.
For example:
EXEC SQL SELECT COL INTO :hostvar FROM TABLE
The following rules apply to embedded SQL statements:
Note that the actual characters used for end-of-line and TAB vary from platform to platform. For example, OS/2 uses Carriage Return/Line Feed for end-of-line, whereas UNIX-based systems use just a Line Feed.
Host variables are FORTRAN language variables that are referenced within SQL statements. They allow an application to pass input data to the database manager and receive output data from it. After the application is precompiled, host variables are used by the compiler as any other FORTRAN variable. Use the following suggestions when naming, declaring, and using host variables.
The SQL precompiler identifies host variables by their declared name. The following suggestions apply:
An SQL declare section must be used to identify host variable declarations. This alerts the precompiler to any host variables that can be referenced in subsequent SQL statements.
The FORTRAN precompiler only recognizes a subset of valid FORTRAN declarations as valid host variable declarations. These declarations define either numeric or character variables. A numeric host variable can be used as an input or output variable for any numeric SQL input or output value. A character host variable can be used as an input or output variable for any character, date, time or timestamp SQL input or output value. The programmer must ensure that output variables are long enough to contain the values that they will receive. Figure 43 shows the syntax for numeric host variables.
Figure 43. Syntax for Numeric Host Variables in FORTRAN
>>--.-INTEGER*2--------.---------------------------->
|-INTEGER*4--------|
|-REAL*4-----------|
|-REAL *8----------|
'-DOUBLE PRECISION-'
<-,--------------------------------<
>----varname--.---------------------.--------------><
'- / initial-value / -'
|
Notes:
Figure 44 shows the syntax for character host variables.
Figure 44. Syntax for Character Host Variables in FORTRAN
Fixed Length <-,----------------------------->
>>--CHARACTER--.----.----varname--.--------------------.--><
'-*n-' '- / initial-value /-'
Variable Length <-,------------------------->
>>--SQL TYPE IS VARCHAR--(length)-------varname--------------------><
|
Notes:
VARCHAR Example:
Declaring:
sql type is varchar(1000) my_varchar
Results in the generation of the following structure:
character my_varchar(1000+2)
integer*2 my_varchar_length
character my_varchar_data(1000)
equivalence( my_varchar(1),
+ my_varchar_length )
equivalence( my_varchar(3),
+ my_varchar_data )
The application may manipulate both my_varchar_length and my_varchar_data; for example, to set or examine the contents of the host variable. The base name (in this case, my_varchar), is used in SQL statements to refer to the VARCHAR as a whole.
LONG VARCHAR Example:
Declaring:
sql type is varchar(10000) my_lvarchar
Results in the generation of the following structure:
character my_lvarchar(10000+2)
integer*2 my_lvarchar_length
character my_lvarchar_data(10000)
equivalence( my_lvarchar(1),
+ my_lvarchar_length )
equivalence( my_lvarchar(3),
+ my_lvarchar_data )
The application may manipulate both my_lvarchar_length and my_lvarchar_data; for example, to set or examine the contents of the host variable. The base name (in this case, my_lvarchar), is used in SQL statements to refer to the LONG VARCHAR as a whole.
| Note: | In a CONNECT statement, such as in the following example, FORTRAN character
string host variables dbname and userid will have any
trailing blanks removed before processing.
EXEC SQL CONNECT TO :dbname USER :userid USING :passwdHowever, because blanks can be significant in passwords, you should declare host variables for passwords as VARCHAR, and have the length field set to reflect the actual password length: EXEC SQL BEGIN DECLARE SECTION
character*8 dbname, userid
sql type is varchar(18) passwd
EXEC SQL END DECLARE SECTION
character*18 passwd_string
equivalence(passwd_data,passwd_string)
dbname = 'sample'
userid = 'userid'
passwd_length= 8
passwd_string = 'password'
EXEC SQL CONNECT TO :dbname USER :userid USING :passwd
|
Indicator variables should be declared as an INTEGER*2 data type.
Figure 45 shows the syntax for declaring large object (LOB) host variables in FORTRAN.
Figure 45. Syntax for Large Object (LOB) Host Variables in FORTRAN
<-,------------>
>>--SQL TYPE IS--.-BLOB-.--(length-.---.-)----variable-name---><
'-CLOB-' |-K-|
|-M-|
'-G-'
|
Notes:
BLOB Example:
Declaring:
sql type is blob(2m) my_blob
Results in the generation of the following structure:
character my_blob(2097152+4)
integer*4 my_blob_length
character my_blob_data(2097152)
equivalence( my_blob(1),
+ my_blob_length )
equivalence( my_blob(5),
+ my_blob_data )
CLOB Example:
Declaring:
sql type is clob(125m) my_clob
Results in the generation of the following structure:
character my_clob(131072000+4)
integer*4 my_clob_length
character my_clob_data(131072000)
equivalence( my_clob(1),
+ my_clob_length )
equivalence( my_clob(5),
+ my_clob_data )
Figure 46 shows the syntax for declaring large object (LOB) locator host variables in FORTRAN.
Figure 46. Syntax for Large Object (LOB) Locator Host Variables in FORTRAN
<-,-------------<
>>--SQL TYPE IS--.-BLOB_LOCATOR-.----variable-name---><
'-CLOB_LOCATOR-'
|
Notes:
CLOB Locator Example (BLOB locator is similar):
Declaring:
SQL TYPE IS CLOB_LOCATOR my_locator
Results in the generation of the following declaration:
integer*4 my_locator
Figure 47 shows the syntax for declaring file reference host variables in FORTRAN.
Figure 47. Syntax for File Reference Host Variables in FORTRAN
<-,-------------<
>>--SQL TYPE IS--.-BLOB_FILE-.----variable-name----><
'-CLOB_FILE-'
|
Notes:
Example of a BLOB file reference variable (CLOB file reference variable is similar):
SQL TYPE IS BLOB_FILE my_file
Results in the generation of the following declaration:
character my_file(267)
integer*4 my_file_name_length
integer*4 my_file_data_length
integer*4 my_file_file_options
character*255 my_file_name
equivalence( my_file(1),
+ my_file_name_length )
equivalence( my_file(5),
+ my_file_data_length )
equivalence( my_file(9),
+ my_file_file_options )
equivalence( my_file(13),
+ my_file_name )
Certain predefined FORTRAN data types correspond to database manager column types. Only these FORTRAN data types can be declared as host variables.
Table 18 shows the FORTRAN equivalent of each column type. When the
precompiler finds a host variable declaration, it determines the appropriate
SQL type value. The database manager uses this value to convert the
data exchanged between the application and itself.
Table 18. SQL Data Types Mapped to FORTRAN Declarations
| SQL Column Type1 | FORTRAN Data Type | SQL Column Type Description |
|---|---|---|
SMALLINT (500 or 501) | INTEGER*2 | 16-bit, signed integer |
INTEGER (496 or 497) | INTEGER*4 | 32-bit, signed integer |
REAL2 (480 or 481) | REAL*4 | Single precision floating point |
DOUBLE3 (480 or 481) | REAL*8 | Double precision floating point |
DECIMAL(p,s) (484 or 485) | No exact equivalent; use REAL*8 | Packed decimal |
CHAR(n) (452 or 453) | CHARACTER*n | Fixed-length character string of length n where n is from 1 to 254 |
VARCHAR(n) (448 or 449) | SQL TYPE IS VARCHAR(n) where n is from 1 to 4000 | Variable-length character string |
LONG VARCHAR (456 or 457) | SQL TYPE IS VARCHAR(n) where n is from 4001 to 32700 | Long variable-length character string |
CLOB(n) (408 or 409) | SQL TYPE IS CLOB (n) where n is from 1 to 2 147 483 647 | Large object variable-length character string |
CLOB locator variable4 (964 or 965) | SQL TYPE IS CLOB_LOCATOR | Identifies CLOB entities residing on the server |
CLOB file reference variable4 (808 or 809) | SQL TYPE IS CLOB_FILE | Descriptor for file containing CLOB data |
BLOB(n) (404 or 405) | SQL TYPE IS BLOB(n) where n is from 1 to 2 147 483 647 | Large object variable-length binary string |
BLOB locator variable4 (960 or 961) | SQL TYPE IS BLOB_LOCATOR | Identifies BLOB entities on the server |
BLOB file reference variable4 (804 or 805) | SQL TYPE IS BLOB_FILE | Descriptor for the file containing BLOB data |
DATE (384 or 385) | CHARACTER*10 | 10-byte character string |
TIME (388 or 389) | CHARACTER*8 | 8-byte character string |
TIMESTAMP (392 or 393) | CHARACTER*26 | 26-byte character string |
The following is a sample SQL declare section with a host variable declared for each supported data type:
EXEC SQL BEGIN DECLARE SECTION
INTEGER*2 AGE /26/
INTEGER*4 DEPT
REAL*4 BONUS
REAL*8 SALARY
CHARACTER MI
CHARACTER*112 ADDRESS
SQL TYPE IS VARCHAR (512) DESCRIPTION
SQL TYPE IS VARCHAR (32000) COMMENTS
SQL TYPE IS CLOB (1M) CHAPTER
SQL TYPE IS CLOB_LOCATOR CHAPLOC
SQL TYPE IS CLOB_FILE CHAPFL
SQL TYPE IS BLOB (1M) VIDEO
SQL TYPE IS BLOB_LOCATOR VIDLOC
SQL TYPE IS BLOB_FILE VIDFL
CHARACTER*10 DATE
CHARACTER*8 TIME
CHARACTER*26 TIMESTAMP
INTEGER*2 WAGE_IND
EXEC SQL END DECLARE SECTION
The following are additional rules for supported FORTRAN data types:
When using the LANGLEVEL precompile option with a value of SQL92E, the following two declarations may be included as host variables:
EXEC SQL BEGIN DECLARE SECTION;
CHARACTER*5 SQLSTATE
INTEGER SQLCOD
.
.
.
EXEC SQL END DECLARE SECTION
If neither of these is specified, the SQLCOD declaration is assumed during the precompile step. The variable named 'SQLSTATE' may also be 'SQLSTA'. Note that when using this option, the INCLUDE SQLCA statement should not be specified.
For applications that contain multiple source files, the declarations of SQLCOD and SQLSTATE may be included in each source file as shown above.
There are no graphic (multi-byte) host variable data types supported in FORTRAN. Only mixed character host variables are supported through the character data type. It is possible to create a user SQLDA that contains graphic data.
Any graphic data sent from an application running under the Japanese or Traditional-Chinese EUC code set is tagged with the UCS-2 code set identifier so your application must convert a EUC string to UCS-2 before sending it to a database server. Similarly, graphic data retrieved from a database by an application running under an EUC code set is encoded using UCS-2, requiring that your application convert from UCS-2 to the EUC code set internally, unless the user is to be presented with UCS-2 data. The conversions to and from UCS-2 are your responsibility since this conversion must be conducted before the data is copied to, and after it is copied from, the SQLDA. No application-accessible conversion routines are supplied with the DB2 products. Use system calls available from your operating system.
Character host variable data and character constants are tagged as mixed character data with the mixed EUC code page identifier.
For general EUC application development guidelines, see "Japanese and Traditional-Chinese EUC Code Set Considerations".