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. Syntax for Numeric Host Variables in FORTRAN shows the syntax for numeric host variables.
For information on declaring host variables for structured types, see Declaring Structured Type 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:
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:
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.
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:
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 )
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:
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
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:
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 )