Data access with host variables

You can use host variables, host-variable arrays, and host structures in your application program to exchange data between the application and the DBMS.

A host variable is a data item that you declare in a program for use within an SQL statement. You can:

  • Retrieve data into the host variable for your application program's use.
  • Place data into the host variable to insert into a table or to change the contents of a row.
  • Use the data in the host variable when evaluating a WHERE or HAVING clause.
  • Assign the value in the host variable to a special register. A special register is a storage area that Db2 defines for a process to hold information that SQL statements can reference.

    For example, the CURRENT SQLID special register contains the SQL authorization ID of a process, which is set in an SQL statement. Db2 replaces the register name with the value of the authorization ID when the SQL statement runs.

  • Use the host variable to indicate a null value

How you code a host variable varies according to the programming language that you use. Some languages require a separate declaration section for SQL variables. In this case, you can code the BEGIN and END DECLARE SECTION statements in an application program wherever variable declarations can appear according to the rules of the host language. A host variable declaration section starts with the BEGIN DECLARE SECTION statement and ends with the END DECLARE SECTION statement. The host variable must be preceded with a :hostvar

The INTO clause of the SELECT statement names one or more host variables to contain the returned column values. For host variables and host-variable arrays, the named variables correspond one-to-one with the list of column names in the SELECT list.

Example

The example that follows uses a host variable to retrieve a single row of data.

Begin general-use programming interface information.Suppose that you want to retrieve the EMPNO, LASTNAME, and DEPT column values from a single row in the EMP table. You can define a host variable in your program to hold each column. The host variable consists of the local variable name, preceded by a colon. You then can name the data areas with an INTO clause, as shown: 
EXEC SQL
  SELECT EMPNO, LASTNAME, DEPT
    INTO :CBLEMPNO, :CBLNAME, :CBLDEPT
    FROM EMP
    WHERE EMPNO = :EMPID
END-EXEC.
End general-use programming interface information.

You must declare the host variables CBLEMPNO, CBLNAME, and CBLDEPT in the data declaration portion of the program. The data types of the host variables must be compatible with the SQL data types of the columns EMPNO, LASTNAME, and DEPT of the EMP table.

Suppose that you don't know how many rows Db2 will return, or you expect more than one row to return. In either case, you must use an alternative to the SELECT ... INTO statement. Using a Db2 cursor, an application can process a set of rows and retrieve rows from the result table.