Host variables

Part V. The SQL Anywhere Programming Interfaces

Chapter 34. The Embedded SQL Interface
Host variables

Host variables are C variables which are identified to the SQL preprocessor. Host variables can be used to send values to the database engine or receive values from the database engine.

Host variables are quite easy to use, but they have some restrictions. "Static vs dynamic SQL" describes dynamic SQL and the more general way of passing information to and from the database engine using a structure known as the SQL Descriptor Area (SQLDA). This section contains information on the following topic:

Host variable declarations

Host variables are defined by putting them into a declaration section. According to the IBM SAA and the ANSI Embedded SQL standards, host variables are defined by surrounding the normal C variable declarations with:

     EXEC SQL BEGIN DECLARE SECTION;
     /* C variable declarations */
     EXEC SQL END DECLARE SECTION;

These host variables can then be used in place of value constants in any SQL statement. When the database engine executes the command, the value of the host variable will be used. Note that host variables cannot be used in place of table or column names; dynamic SQL is required for this. The variable name is prefixed with a colon (':') in an SQL statement to distinguish it from other identifiers allowed in the statement.

A standard SQL preprocessor does not scan C language code except inside a declare section. Thus, typedef types and structures are not allowed. Initializers on the variables are allowed inside a declare section.

Example

The following sample code illustrates the use of host variables on an INSERT command. The variables are filled in by the program and then inserted into the database:

     EXEC SQL BEGIN DECLARE SECTION;
     long employee_number;
     char employee_name[50];
     char employee_initials[8];
     char employee_phone[15];
     EXEC SQL END DECLARE SECTION;
     /* program fills in variables with appropriate values
     */
     EXEC SQL INSERT INTO Employee
     VALUES (:employee_number, :employee_name,
     :employee_initials, :employee_phone );

For a more extensive example, see "Static cursor example".

C host variable types

Only a limited number of C data types are supported as host variables. Also, certain host variable types do not have a corresponding C type.

There are macros defined in the SQLCA.H header file that can be used to declare a host variable of these types: VARCHAR, FIXCHAR, BINARY, PACKED DECIMAL, or SQLDATETIME structure. They are used as follows:

     EXEC SQL BEGIN DECLARE SECTION;
     DECL_VARCHAR( 10 ) v_varchar;
     DECL_FIXCHAR( 10 ) v_fixchar;
     DECL_BINARY( 4000 ) v_binary;
     DECL_DECIMAL( 10, 2 ) v_packed_decimal;
     DECL_DATETIME v_datetime;
     EXEC SQL END DECLARE SECTION;

The preprocessor recognizes these macros within a declaration section and will treat the variable as the appropriate type.

The following table illustrates C variable types allowed for host variables and their corresponding Embedded SQL interface data types.

C Data Type Embedded SQL Interface Type Description

     short i;      short int i;      unsigned short int i;
DT_SMALLINT 16 bit, signed integer

     long l;      long int l;      unsigned long int l;
DT_INT 32 bit, signed integer

     float f;
DT_FLOAT 4 byte floating point

     double d;
DT_DOUBLE 8 byte floating point

     DECL_DECIMAL(p,s)
DT_DECIMAL(p,s) Packed decimal

     char a; /*n=1*/ DECL_FIXCHAR(n) a; DECL_FIXCHAR a[n];
DT_FIXCHAR(n) Fixed length character string blank padded

     char a[n]; /*n>=1*/
DT_STRING(n) NULL-terminated string

     char *a;
DT_STRING(32767) NULL-terminated string- see note

     DECL_VARCHAR(n) a;
DT_VARCHAR(n) Varying length character string with 2 byte length field

     DECL_BINARY(n) a;
DT_BINARY(n) Varying length binary data with 2 byte length field

     DECL_DATETIME a;
DT_TIMESTAMP_STRUCT SQLDATETIME structure

C Data Type	Embedded SQL Interface Type	Description
`short i;` `short int i;` `unsigned short int i;`	DT_SMALLINT	16 bit, signed integer
`long l;` `long int l;` `unsigned long int l;`	DT_INT	32 bit, signed integer
`float f;`	DT_FLOAT	4 byte floating point
`double d;`	DT_DOUBLE	8 byte floating point
`DECL_DECIMAL(p,s)`	DT_DECIMAL(p,s)	Packed decimal
`char a; /n=1/ DECL_FIXCHAR(n) a; DECL_FIXCHAR a[n];`	DT_FIXCHAR(n)	Fixed length character string blank padded
`char a[n]; /n>=1/`	DT_STRING(n)	NULL-terminated string
`char *a;`	DT_STRING(32767)	NULL-terminated string- see note
`DECL_VARCHAR(n) a;`	DT_VARCHAR(n)	Varying length character string with 2 byte length field
`DECL_BINARY(n) a;`	DT_BINARY(n)	Varying length binary data with 2 byte length field
`DECL_DATETIME a;`	DT_TIMESTAMP_STRUCT	SQLDATETIME structure

Pointers to char
A host variable declared as a pointer to char (char *a) is considered by the database interface to be 32,767 bytes long. Thus, you must ensure that any host variable of type pointer to char used to retrieve information from the database points to a buffer large enough to hold any value that could possibly come back from the database. Note that this is potentially quite dangerous as somebody could change the definition of the column in the database to be larger than it was when the program was written. This could cause random memory corruption problems. If you are using a 16-bit compiler, requiring 32,767 bytes could result in overflowing the program stack. It is better to use a declared array, even as a parameter to a function where it is passed as a pointer to char. This lets the PREPARE statements know the size of the array.

Scope of host variables

A standard host variable declaration section can appear anywhere that C variables can normally be declared. This includes the parameter declaration section of a C function. The C variables have their normal scope (available within the block in which they are defined). However, since the SQL preprocessor does not scan C code, it does not respect C blocks. As far as the SQL preprocessor is concerned, host variables are global; two host variables cannot have the same name (a warning will result). The SQL preprocessor has one exception to this rule. It will allow two host variables of the same name if they have identical types (including any necessary lengths).

Host variable usage

Host variables can be used in SELECT, INSERT, UPDATE and DELETE statements in any place where a number or string constant is allowed.
Host variables can be used in the INTO clause of SELECT and FETCH statements.
Host variables can also be used in place of a statement name, a cursor name, or an option name in Embedded SQL specific commands.
For CONNECT, DISCONNECT, and SET CONNECT a host variable can be used in place of a userid, password, connection name or database environment name.
For SET OPTION and GET OPTION, a host variable can be used in place of a userid, option name or option value.
Host variables cannot be used in place of a table name or a column name in any statement.

For releases starting with 5.0.02, you can declare SQLSTATE and SQLCODE as host variables within a declare section. This is provided for ISO/ANSI compatibility.

Before release 5.0.02, SQLSTATE and SQLCODE were macros for values in the sqlca, and this is still true if neither is specified in your program. However, if you declare SQLSTATE or SQLCODE anywhere in the program, then the macro definitions are removed. This means that if a program contains a declaration of SQLCODE/SQLSTATE, then a SQLSTATE/SQLSTATE must be within the scope of every Embedded SQL statement in the program.

Examples

The following is a valid program:

     INCLUDE SQLCA;
     long SQLCODE;
     sub1() {
         char SQLSTATE[6];
         exec sql CREATE TABLE ...
     }

The following is an invalid program:

     INCLUDE SQLCA;

     sub1() {
         char SQLSTATE[6];
         exec sql CREATE TABLE...
     }

     sub2() {
         exec sql DROP TABLE...
         // No SQLSTATE in scope of this statement
     }

The case of SQLSTATE and SQLCODE is important, and the ISO/ANSI standard requires that their definitions be exactly as follows:

     long SQLCODE;

     char SQLSTATE[6];

Indicator variables

The NULL value is a value that can be put into certain columns in the database. It represents either an unknown attribute or inapplicable information. See "NULL value" for a complete description of NULLs.

NULL in SQL and C
NULL is not to be confused with the C language constant by the same name (NULL). The C constant is used to represent a non-initialized or invalid pointer. When NULL is used in this book, it refers to the SQL database meaning given above. The C language constant will be referred to as the null pointer (lower case).

NULL is not the same as any value of the column's defined type. Thus, in order to pass NULL values to the database or receive NULL results back, something extra is required beyond regular host variables. Indicator variables are used for this purpose. An indicator variable is a host variable of type short int that is placed immediately following a regular host variable in an SQL statement. For example, the previous insert statement example could include an indicator variable as follows:

     EXEC SQL BEGIN DECLARE SECTION;
     short int employee_number;
     char employee_name[50];
     char employee_initials[6];
     char employee_phone[15];
     short int ind_phone;
     EXEC SQL END DECLARE SECTION;
     
     /*
     program fills in empnum, empname,
     initials and homephone
     */
     if( /* phone number is unknown */ ) {
         ind_phone = -1;
     } else {
         ind_phone = 0;
     }
     EXEC SQL INSERT INTO Employee
         VALUES (:employee_number, :employee_name,
         :employee_initials, :employee_phone:ind_phone );

In this example, a NULL is assigned to the phone number if the phone number is unknown. This is accomplished by assigning negative one (-1) to the indicator variable.

Indicator variables are also used when receiving data from the database. They are used to indicate that a NULL value was fetched (indicator is negative). If a NULL value is fetched from the database and an indicator variable is not supplied, then an error is generated (SQLE_NO_INDICATOR). Errors are explained in the next section.

Indicator variables are also used to indicate whether any fetched values were truncated to fit into the host variables (indicator has positive value). In this case, the indicator variable contains the actual length of the database value before truncation. If the length of the value is greater than 32767, then the value must be truncated and the indicator variable contains 32767.

For more information on retrieving long values, see "GET DATA statement".

See the following table for a more complete description of indicator variable usage.

Indicator Value Supplying Value to database Receiving value from database

> 0 Host variable value Retrieved value was truncated --- actual length in indicator variable

= 0 Host variable value Fetch successful

= -1 NULL value NULL result

= -2 NULL value Conversion error SQLCODE will indicate a conversion error

< -2 NULL value NULL result

Indicator Value	Supplying Value to database	Receiving value from database
> 0	Host variable value	Retrieved value was truncated --- actual length in indicator variable
= 0	Host variable value	Fetch successful
= -1	NULL value	NULL result
= -2	NULL value	Conversion error SQLCODE will indicate a conversion error
< -2	NULL value	NULL result