Contents

Host Variables 

See also: Supported data types, Creating an FESQLC Program, SQL statements, Dynamic SQL

Overview

Host variables are C variables that you can use in SQL statements as if they were literal values. Host variables can store:

Declare host variables in your program, and precede the variable with an indicator (:) whenever it is used in an embedded SQL statement:

EXEC SQL BEGIN DECLARE SECTION;
   varchar firstname[31];
   int stateid;
   varchar statename[51];
EXEC SQL END DECLARE SECTION;
EXEC SQL select fname into :firstname from custtab;
EXEC SQL insert into statetab values (:stateid, :statename);

Variable name

As in C, the host variable name can consist of letters, digits, and underscores, consistent with the requirements of your C compiler. Begin the name with a letter rather than an underscore, to avoid potential conflicts with internal FESQLC names. 

int x;
char str1[11];

A variable name is case-sensitive; firstname is not the same as FirstName.

Variable declaration

Choose a data type for the host variable compatible with the SQL data type of the database column, if the variable is used to transfer data between the program and the database.  See Supported Data Types for a list of the SQL data types and the compatible FESQLC or C data types.  

You can also use data structures such as arrays, struct, and pointers as host variables in your program.  

Declare the host variable in your program,  using the same syntax that you use to declare a C variable. Put the statements in an FESQLC declare section preceded with the keywords EXEC SQL BEGIN DECLARE SECTION, and terminate it with EXEC SQL END DECLARE SECTION (preferred ANSI standard): 

EXEC SQL BEGIN DECLARE SECTION;
    int x;
    char str1[11];
    char str4[11];
EXEC SQL END DECLARE SECTION;

For backward compatibility with Informix ESQL/C, the FESQLC compiler also supports host variable declaration with the $ symbol:

$int x;
$char str1[11];

The variable can be declared only once within a C block.

Scope

The scope of an FESQLC host variable is the same as that of a C variable, which is dependent on the placement of the declaration within the file:

Initialization

FESQLC allows you to use normal C initializer expressions with host variables:

EXEC SQL BEGIN DECLARE SECTION;
  int var1 = 128;
  string var2[21] = "A test string";
EXEC SQL END DECLARE SECTION;

Local datetime or interval variables are automatically initialized to set the datetime or interval qualifier; local variables of any other data type have an undefined value and must always be initialized before use.

EXEC SQL BEGIN DECLARE SECTION;
  datetime year to second dt;
  interval hour to second t1_iv1;
EXEC SQL END DECLARE SECTION;

The FESQLC preprocessor does not check the validity of initialization statements; the C compiler handles this.

Using character variables

Although you can use char/varchar/string/fixchar data types for host variables in your program, we recommend that you match the data type of the host variable to the data type of the corresponding database column.

See Character and String functions for a list of FESQLC functions to manipulate character data.

Declaring Size

The char/varchar/string host variables include the string terminator; you must allow for the terminator when declaring one of these variables.  For example, to correspond to a database CHAR data type, declare the size of the char host variable as (dblength+1). 

  EXEC SQL BEGIN DECLARE SECTION;
  char p_state_code[3]  /* length of the column state.state_code is 2 */

Although char, varchar, and string host variables contain null terminators, FESQLC never inserts these characters into a database column.

Using char/varchar Pointers

It is strongly recommended that you use fixed-size char and varchar variables instead of pointers. The problem with char/varchar pointers is that the database interface cannot determine the size of the char/varchar input parameter or fetch buffer, which is required for binding host variables to SQL statements.

Host variables defined as char or varchar pointers cannot be used to fetch data. If you must use a char/varchar pointer to insert data, use an sqlda structure and specify the exact size of the corresponding CHAR/VARCHAR SQL type in sqlvar->sqllen.

Fetching and inserting CHAR data

When a value from a CHAR database column is fetched into a char host variable, FESQLC pads the value with trailing blanks up to the size of the host variable, leaving one space for the null terminator. If the column data being fetched does not fit into the character host variable, FESQLC truncates the data, setting the SQLSTATE variable to 01004, and setting the value of any indicator variable to the size of the character data in the column.

When inserting a value into a CHAR database column, FESQLC pads or truncates the value to the size of that column, if necessary.

Fetching and inserting VARCHAR data

When a value from a VARCHAR database column is fetched into a char host variable, FESQLC truncates and null terminates the value if the source is longer, and sets any indicator variable; if the destination is longer, FESQLC pads the value with trailing spaces and null terminates it.

When a character value is inserted into a VARCHAR database column, any trailing spaces are not counted.

Using the fixchar data type

The fixchar data type is a character string data type that does not have a null terminator.  When a value from a CHAR or VARCHAR column is fetched into a fixchar, FESQLC pads the value with trailing blanks. 

Do not use fixchar to insert data into a VARCHAR column. Even if the length of the data is shorter than the fixchar, trailing blanks will be stored by the database server.

Do not copy a null-terminated C string into a fixchar variable.  When the variable value is inserted into the database column, the null character at the end of the string will also be inserted, complicating subsequent searches for the data value.

Using the string data type

The FESQLC string data type holds character data terminated by a null character, and does not have trailing blanks.

When values from a CHAR/VARCHAR column are fetched into a string host variable, the value is usually stripped of trailing blanks and null-terminated. However, if the value is a string of blanks ("     "), a single blank and the null-terminator are stored in the string host variable, to distinguish an empty string from a null string.

Using decimal variables

FESQLC supports the dec_t and decimal data types to handle DECIMALs. Although you can use the dec_t data type to fetch decimal data, it cannot be used to insert data as there is no precision or scale; we recommend the use of the FESQLC decimal data type, which supports the DECIMAL and MONEY SQL data types, for inserting and fetching data.

A decimal host variable must be defined with a precision and scale:

EXEC SQL BEGIN DECLARE SECTION;
   decimal(6,2) dec4;

See Decimal Functions for a list of functions to manipulate decimal values.  Use only decimal functions on decimal data types; otherwise, you may get unpredictable results.

Using interval and datetime variables

The interval FESQLC data type encodes a span of time. The datetime FESQLC data type encodes a specific point in time. The accuracy of the data types is specified by a qualifier (year to second, etc.) Declare host variables for INTERVAL or DATETIME database columns using these FESQL data types:

EXEC SQL BEGIN DECLARE SECTION;
  datetime year to second dtm2;
  interval hour(5) to second inv2;
EXEC SQL END DECLARE SECTION;

A datetime or interval data type is stored as a decimal number with a scale of zero and a precision dependent on the qualifier used when defining the data type.

Datetime and interval host variables must have the qualifier initialized. This is done by using datetime or interval data types as shown in the above example.  If you use dtime_t or intrvl_t data types, use the following macros to initialize their qualifiers:

EXEC SQL BEGIN DECLARE SECTION;
   dtime _t dtml;
   intrvl_t inv1;
EXEC SQL END DECLARE SECTION;

dtml.dt_qual = TU_DTENCODE(TU_YEAR, TU_SECOND);
invl.in_qual = TU_TENCODE(5, TU_HOUR, TU_SECOND);

Macros for the qualifiers are:

See Datetime and Interval Functions for a list of functions to manipulate these data types. 

Data structures

Arrays

You can declare an array of host variables in your program; provide the number of elements in the array:

EXEC SQL BEGIN DECLARE SECTION;
  int ordid[20];
 EXEC SQL END DECLARE SECTION;

An array can be one-dimensional or two-dimensional.

Your program can reference an element of the array as follows:

EXEC SQL DELETE FROM ordertab WHERE order_num = :ordid[1];

Structures (struct)

You can declare a C language structure struct in your program, specifying the members of the structure:

EXEC SQL BEGIN DECLARE SECTION;
  struct {
    int ordid;
    date orddate; 
    char ordname[26];
  } order_rec;
EXEC SQL END DECLARE SECTION;

Here struct order_rec defines a host variable with three members: ordid, orddate, and ordname.

The individual members of the structure are referenced in your program as structname.membername:

EXEC SQL insert into ordertab (order_num, order_date)
    values (:order_rec.ordid :order_rec.orddate);

If an SQL statement allows a list of host variables, you can specify the structure name and FESQLC will expand it:

EXEC SQL insert into ordertab values (:order_rec);

The values list in this SQL statement will expand to order_rec.ordid, order_rec.orddate, order_rec.ordname.

Type definitions (typedef)

FESQLC supports C typedef statements and allows the use of typedef names in the declaration of the types of host variables: 

EXEC SQL BEGIN DECLARE SECTION;
typedef struct {
     int key;
     varchar name[21];
     datetime year to fraction(5) tstamp;
} mytype;
mytype myrecord;
EXEC SQL END DECLARE SECTION;

You cannot use a typedef statement that names a multidimensional array, a union, or a function pointer as the type of a host variable.

Function parameters

You can declare host variables as function parameters; precede the variable name with the PARAMETER keyword.  

func1(ord_id, ord_date)
EXEC SQL BEGIN DECLARE SECTION;
  PARAMETER int ord_id;
  PARAMETER date ord_date;
EXEC SQL END DECLARE SECTION;
{
  ...
  EXEC SQL INSERT INTO oldorders VALUES (:ord_id, :ord_date);
  ...
}

Notes:

Pointers

Host variables can be declared as pointers to SQL identifiers, prepared statements for example: 

EXEC SQL BEGIN DECLARE SECTION;
    char *stmt;
    int ord_num;
EXEC SQL END DECLARE SECTION;
...
stmt = "SELECT order_num FROM orders";
EXEC SQL PREPARE curs1 FROM :stmt;
EXEC SQL OPEN curs1;
EXEC SQL FETCH curs1 INTO :ord_num;

Host variables declared as pointers can also be used as input parameters in SQL insert statements. However, we strongly recommend that you use fixed-size char and varchar host variables instead of  char/varchar pointers to hold SQL CHAR/VARCHAR data.  

EXEC SQL BEGIN DECLARE SECTION;
  int *v_id;
  char v_name[26];
EXEC SQL END DECLARE SECTION;
...
INSERT INTO vendors values (:v_id, :v_name);

Data conversion

We recommend that you use the FESQLC data type that corresponds to the SQL data type of the database column being referenced in your statements.

When necessary, FESQLC will try to convert the data type of a value if a discrepancy exists in your program.