Application Development Guide

Host Variables

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.

Naming Host Variables

The SQL precompiler identifies host variables by their declared name. The following suggestions apply:

Specify variable names up to 255 characters in length.
Begin host variable names with prefixes other than SQL, sql, DB2, or db2, which are reserved for system use.

Declaring Host Variables

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. Syntax for Numeric Host Variables in FORTRAN shows the syntax for numeric host variables.

Syntax for Numeric Host Variables in FORTRAN
 
>>-+-INTEGER*2--------+----------------------------------------->
   +-INTEGER*4--------+
   +-REAL*4-----------+
   +-REAL *8----------+
   '-DOUBLE PRECISION-'
 
      .-,-----------------------------------.
      V                                     |
>--------varname--+---------------------+---+------------------><
                  '- / initial-value / -'

Numeric Host Variable Considerations:

REAL*8 and DOUBLE PRECISION are equivalent.
Use an E rather than a D as the exponent indicator for REAL*8 constants.

Syntax for Character Host Variables in FORTRAN: Fixed Length shows the syntax for character host variables.

Syntax for Character Host Variables in FORTRAN: Fixed Length
 
                      .-,-----------------------------------.
                      V                                     |
>>-CHARACTER--+----+-----varname--+---------------------+---+--><
              '-*n-'              '- / initial-value / -'

Variable Length

                                  .-,----------.
                                  V            |
>>-SQL TYPE IS VARCHAR--(length)-----varname---+---------------><

Character Host Variable Considerations:

*n has a maximum value of 254.
When length is between 1 and 32 672 inclusive, the host variable has type VARCHAR(SQLTYPE 448).
When length is between 32 673 and 32 700 inclusive, the host variable has type LONG VARCHAR(SQLTYPE 456).
Initialization of VARCHAR and LONG VARCHAR host variables is not permitted within the declaration.

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 :passwd
However, 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

Indicator variables should be declared as an INTEGER*2 data type.

LOB Declarations in FORTRAN

Syntax for Large Object (LOB) Host Variables in FORTRAN shows the syntax for declaring large object (LOB) host variables in FORTRAN.

Syntax for Large Object (LOB) Host Variables in FORTRAN
 
                                              .-,----------------.
                                              V                  |
>>-SQL TYPE IS--+-BLOB-+---(length-+---+--)------variable-name---+->
                '-CLOB-'           +-K-+
                                   +-M-+
                                   '-G-'
 
>--------------------------------------------------------------><

LOB Host Variable Considerations:

GRAPHIC types are not supported in FORTRAN.
SQL TYPE IS, BLOB, CLOB, K, M, G can be in either uppercase, lowercase, or mixed.
For BLOB and CLOB 1 <= lob-length <= 2 147 483 647.
The initialization of a LOB within a LOB declaration is not permitted.
The host variable name prefixes 'length' and 'data' in the precompiler generated code.

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 )

LOB Locator Declarations in FORTRAN

Syntax for Large Object (LOB) Locator Host Variables in FORTRAN shows the syntax for declaring large object (LOB) locator host variables in FORTRAN.

Syntax for Large Object (LOB) Locator Host Variables in FORTRAN
 
                                   .-,----------------.
                                   V                  |
>>-SQL TYPE IS--+-BLOB_LOCATOR-+------variable-name---+--------><
                '-CLOB_LOCATOR-'

LOB Locator Host Variable Considerations:

GRAPHIC types are not supported in FORTRAN.
SQL TYPE IS, BLOB_LOCATOR, CLOB_LOCATOR can be either uppercase, lowercase, or mixed.
Initialization of locators is not permitted.

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

File Reference Declarations in FORTRAN

Syntax for File Reference Host Variables in FORTRAN shows the syntax for declaring file reference host variables in FORTRAN.

Syntax for File Reference Host Variables in FORTRAN
 
                                .-,----------------.
                                V                  |
>>-SQL TYPE IS--+-BLOB_FILE-+------variable-name---+-----------><
                '-CLOB_FILE-'

File Reference Host Variable Considerations:

Graphic types are not supported in FORTRAN.
SQL TYPE IS, BLOB_FILE, CLOB_FILE can be either uppercase, lowercase, or mixed.

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 )

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]