Application Development Guide

Using Host Variables

Host variables are variables referenced by embedded SQL statements. They transmit data between the database manager and an application program. When you use a host variable in an SQL statement, you must prefix its name with a colon, (:). When you use a host variable in a host language statement, omit the colon.

Host variables are declared in compiled host languages, and are delimited by BEGIN DECLARE SECTION and END DECLARE SECTION statements. These statements enable the precompiler to find the declarations.

Note:

Java JDBC and SQLJ programs do not use declare sections. Host variables in Java follow the normal Java variable declaration syntax.

Host variables are declared using a subset of the host language. For a description of the supported syntax for your host language, see:

• Programming in C and C++
• Programming in Java
• Programming in COBOL
• Programming in FORTRAN
• Programming in REXX.

The following rules apply to host variable declaration sections:

• All host variables must be declared in the source file before they are referenced, except for host variables referring to SQLDA structures.
• Multiple declare sections may be used in one source file.
• The precompiler is unaware of host language variable scoping rules.

  With respect to SQL statements, all host variables have a global scope regardless of where they are actually declared in a single source file. Therefore, host variable names must be unique within a source file.

  This does not mean that the DB2 precompiler changes the scope of host variables to global so that they can be accessed outside the scope in which they are defined. Consider the following example:

  foo1(){
    .
    .
    .
    BEGIN SQL DECLARE SECTION;
    int x;
    END SQL DECLARE SECTION;
  x=10;
    .
    .
    .
  }
   
   
  foo2(){
    .
    .
    .
    y=x;
    .
    .
    .
  }
  

  Depending on the language, the above example will either fail to compile because variable x is not declared in function foo2() or the value of x would not be set to 10 in foo2(). To avoid this problem, you must either declare x as a global variable, or pass x as a parameter to function foo2() as follows:

    foo1(){                                
    .
    .
    .
      BEGIN SQL DECLARE SECTION;
      int x;
      END SQL DECLARE SECTION;
      x=10;
      foo2(x);
    .
    .
    .
    }                                      
   
   
    foo2(int x){
    .
    .
    .
      y=x;
    .
    .
    .
    }
  

For further information on declaring host variables, see:

• Declaration Generator - db2dclgn to generate host variable declaration source code automatically with the db2dclgn tool
• Table 4 for examples of how host variables appear in source code
• Table 5 for examples of how to reference host variables in the supported host languages
• Naming Host Variables in REXX and Referencing Host Variables in REXX for information on naming and referencing host variables in REXX

Declaration Generator - db2dclgn

The Declaration Generator speeds application development by generating declarations for a given table in a database. It creates embedded SQL declaration source files which you can easily insert into your applications. db2dclgn supports the C/C++, Java, COBOL, and FORTRAN languages.

To generate declaration files, enter the db2dclgn command in the following format:

db2dclgn -d database-name -t table-name [options]

For example, to generate the declarations for the STAFF table in the SAMPLE database in C in the output file staff.h, issue the following command:

db2dclgn -d sample -t staff -l C

The resulting staff.h file contains:

     struct
     {
       short id;
       struct
       {
         short length;
         char data[9];
       } name;
       short dept;
       char job[5];
       short years;
       double salary;
       double comm;
     } staff;

For detailed information on db2dclgn, refer to the Command Reference.

Table 4. Declaring Host Variables

Language	Example Source Code
C/C++	EXEC SQL BEGIN DECLARE SECTION; short dept=38, age=26; double salary; char CH; char name1[9], NAME2[9]; /* C comment */ short nul_ind; EXEC SQL END DECLARE SECTION;
Java	// Note that Java host variable declarations follow // normal Java variable declaration rules, and have // no equivalent of a DECLARE SECTION short dept=38, age=26; double salary; char CH; String name1[9], NAME2[9]; /* Java comment */ short nul_ind;
COBOL	EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 age PIC S9(4) COMP-5 VALUE 26. 01 DEPT PIC S9(9) COMP-5 VALUE 38. 01 salary PIC S9(6)V9(3) COMP-3. 01 CH PIC X(1). 01 name1 PIC X(8). 01 NAME2 PIC X(8). * COBOL comment 01 nul-ind PIC S9(4) COMP-5. EXEC SQL END DECLARE SECTION END-EXEC.
FORTRAN	EXEC SQL BEGIN DECLARE SECTION integer*2 age /26/ integer*4 dept /38/ real*8 salary character ch character*8 name1,NAME2 C FORTRAN comment integer*2 nul_ind EXEC SQL END DECLARE SECTION

Table 5. Referencing Host Variables

Language	Example Source Code
C/C++	EXEC SQL FETCH C1 INTO :cm; printf( "Commission = %f\n", cm );
JAVA (SQLJ)	#SQL { FETCH :c1 INTO :cm }; System.out.println("Commission = " + cm);
COBOL	EXEC SQL FETCH C1 INTO :cm END-EXEC DISPLAY 'Commission = ' cm
FORTRAN	EXEC SQL FETCH C1 INTO :cm WRITE(*,*) 'Commission = ', cm