Declaration of fixed-length and variable length character host variables in FORTRAN embedded SQL applications

You must declare character host variables when you program an embedded SQL application in FORTRAN. Host variables are treated like FORTRAN variables, and allow for the exchange of data between the embedded application and the database manager.

The syntax for fixed-length character host variables is:

Fixed length

Syntax for character host variables in FORTRAN: fixed length

Read syntax diagramSkip visual syntax diagramCHARACTER*n ,varname / initial-value / 

Following is the syntax for variable-length character host variables.

Variable length

Read syntax diagramSkip visual syntax diagramSQL TYPE IS VARCHAR(length) ,varname
Character host variable considerations:
  1. *n has a maximum value of 254.
  2. When length is between 1 and 32 672 inclusive, the host variable has type VARCHAR(SQLTYPE 448).
  3. When length is between 32 673 and 32 700 inclusive, the host variable has type LONG VARCHAR(SQLTYPE 456).
  4. 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 can 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 can 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, the 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