5. Host Variable Data Types

Overview

Host variables differ from variables in C or C++ applications in how they are used, in their functionality, and in how they are declared. It follows that host variable data types are different from C data types. The following data types can be used as host variables:

• Data type that can be used as data type of host variable

• Extended data types provided by embedded SQL statements

• Relationship between column type and host variable type

Term Description

This section describes some of the terms that will be used in this chapter

Host Variables

Host variables are variables declared in host variable declaration and used in embedded SQL statements.

Fundamental Variables

All variables declared and used in a C or C++ program.

Host Variable Types (Host Variable Data Types)

Host variable types are data types of host variables, including most data types used in C or C ++ and extended data types provided by embedded SQL statements.

Fundamental Variable Types (Fundamental Variable Data Types)

Fundamental variable types are data types used in C or C ++ programs. In this chapter, they are used to compare with host variable types to help developers understand.

Column Types

The column type is a data type of a column defined in a table of a database server. When determining the data type of a host variable, a compatible data type should be used in consideration of the corresponding column type.

Host Variable Data Type

The following are the data types that can be used as data types of host variables.

• Most data types used in C or C++
• Extended data types provided by embedded SQL statements
• Data type declated in host variable declaration.

Fundamental C/C++ Data Types

Most of the fundamental data types supported for use as C and C++ data types can also be used for host variables. The fundamental C/C++ types that can be used for host variables are set forth below.

Numeric Types

The following numeric types can be used as host variable data types:

Integer Types

int, short int, long int, short, long, long long, unsigned int, unsigned short int, unsigned long int, unsigned short, unsigned long, unsigned long long

Real Number Types

float, double

Unavailable Numeric Type

The long double type is not supported for use as a host variable data type.

Character Types

The following character types can be used as host variable data types:

Character types

char, unsigned char

Precautions

An output host variable corresponding to a CHAR type database column must be declared so that its length is one (1) byte longer than the length of the database column. The reason for this is that the length of the data stored in a CHAR type column is fixed, so the length of the data that are returned will always be the same as the length of the column, and the host variable requires one additional byte to store the NULL terminating character at the end. If a host variable is not declared so that it is at least one byte longer than the database column to which it corresponds, then when a SELECT or FETCH statement is executed, the value returned inthe sqlca.sqlcode variable will be SQL_SUCCESS_WITH_INFO rather than SQL_SUCCESS.

When declaring a host variable for use with a database column, it is common to declare and use a single "input/output" host variable, that is, a host variable that is used as both an input and output variable for the column, rather than declaring separate input and output host variables. Therefore, for the above reason pertaining to output host variables, when declaring an input/output variable of this type for use with a CHAR type database column, it must be declared such that its length is one byte greater than the length of the column.

Pointer Types

All host variable types that are available in APRE can be used as base types for pointers.

char*

A Pointer to a character string can be used as a host variable.

The char* type is convenient for use when using a function argument as a host variable. For more information about using a function argument as a host variable, please refer to Chapter 3: Host Variable Declaration Section.

MAX_CHAR_PTR

When using a pointer to a character string as a host variable, the precompiler assumes that the maximum size of the string to which the host variable points is 65000 bytes, which is predefined in the internally provided MAX_CHAR_PTR macro. This is because the precompiler cannot know the actual allocated size. Therefore, when a smaller amount of memory than the value of the MAX_CHAR_PTR macro is allocated to a char* type output host variable, care must be taken because a character string that is longer than the allocated memory size and smaller than the value of the MAX_CHAR_PTR macro can be stored in the host variable. In this case, memory corruption will occur.

It may become necessary to declare a pointer to a string that is more than 65000 bytes long. In such cases, before declaring the char type host variable, use the MAX_CHAR_PTR macro to redefine the maximum size of a string to which a char type host variable can point.

Redefine the MAX_CHAR_PTR macro as follows:

#define MAX_CHAR_PTR 90000

After the MAX_CHAR_PTR macro has been redefined, it becomes possible to allocate an amount of memory equal to the value of the MAX_CHAR_PTR macro, and to declare a char* type host variable that points to a string that occupies this much memory.

Structure Pointers

A pointer to a structure can be used as a host variable data type. A pointer to a structure is convenient to use when using a function argument as a host variable. For more information about using a function argument as a host variable, please refer to Chapter 3: Host Variable Declaration Section.

After a pointer to a structure has been declared, be sure to allocate an appropriate amount of space in memory. This is critical, because the precompiler has no way of checking whether or not enough space for the structure has been allocated.

Pointer to an Array

The number of array elements should be specified with the FOR clause when replacing pointer type variables to a 1 dimensional array as input host variable within the INSEART statement.

However, unwanted outcome could be caused when using a 2 dimensional pointer array as a host variable or using 1 dimensional array which indicates the point in the INSERT statement or FOR clause.

char, varchar, APRE_BINARY, APRE_BINARY2, APRE_BYTES, APRE_NIBBLE, APRE_NUMERIC, APRE_BLOB, APRE_CLOB, APRE_BIT, APRE_VARBYTES

To use a pointer to an array of integer values as an input host variable in an INSERT statement, use the FOR clause, as shown in the following example:

int sInt[10];
int *sIntptr;
sIntptr = sInt;

EXEC SQL FOR 10 INSERT INTO T2 VALUES ( :sIntptr );

For more information about the use of the FOR clause, please refer to Chapter 9: Using Arrays in Embedded SQL Statements.

Also, it does not work properly when using a 2-dimensional pointer array as a host variable as follows:

int sInt[2][10];
int (*sIntptr)[10];
sIntptr = sInt;
EXEC SQL FOR 10 INSERT INTO T2 VALUES ( :sIntptr );

Example

[Example 1] This example demonstrates the use of the v_ename char* type input host variable.

< Sample Program : argument.sc >

void ins_employee(int v_eno, char* v_ename, short v_dno)
{
EXEC SQL BEGIN ARGUMENT SECTION;
    int    v_eno;
    char*  v_ename;
    short  v_dno;
    EXEC SQL END ARGUMENT SECTION;

    EXEC SQL INSERT INTO TODAY_EMPLOYEE 
VALUES (:v_eno, :v_ename, :v_dno);
}

[Example 2] The following example demonstrates how to define the MAX_CHAR_PTR macro.

#define MAX_CHAR_PTR 90000
EXEC SQL BEGIN DECLARE SECTION;
char* var1;
EXEC SQL END DECLARE SECTION;

Or

EXEC SQL BEGIN DECLARE SECTION;
#define MAX_CHAR_PTR 90000
char* var1;
EXEC SQL END DECLARE SECTION;

[Example 3] The followhing shows various example of defining structure pointers

(1) Declare a structure and a pointer to the structure in the same statement.

struct tag1 
{ 
    int a; 
} *A; 
A = (struct tag1*)(malloc(sizeof(struct tag1))); 
INSERT INTO T1 VALUES ( :A ); or INSERT INTO T1 VALUES (:A->a); 

(2) First declare the structure, and then declare a pointer to the structure in a separate statement.

struct tag1 
{ 
    int a; 
}; 
struct tag1 *A; 
A = (struct tag1*)(malloc(sizeof(struct tag1))); 
SELECT I1 INTO :A FROM T1; or SELECT I1 INTO :A->a FROM T1; 

(3) First declare a structure and define a type based on the structure in the same statement, and then declare a pointer to the type in a separate statement.

typedef struct tag1 
{ 
    int a; 
}tag1; 
tag1 *A; 
A = (tag1*)(malloc(sizeof(tag1))); 
SELECT I1 INTO :A FROM T1; or SELECT I1 INTO :A->a FROM T1; 

In the following example, vDataT2 is a pointer to a structure, and is used as an input host variable.

< Sample Program : pointer.sc >

EXEC SQL BEGIN DECLARE SECTION; 
typedef struct tag 
{    
    char n1[11]; 
    int  n2; 
}tag; 

tag *dataT2; 
EXEC SQL END DECLARE SECTION; 

 void ins_t2(tag* vDataT2) 
{ 
    EXEC SQL BEGIN ARGUMENT SECTION; 
    tag *vDataT2; 
    EXEC SQL END ARGUMENT SECTION; 

    EXEC SQL INSERT INTO T2 VALUES (:vDataT2->n1, :vDataT2->n2); 
}

Structure Types

struct

Structures (struct) can be used as host variable data types.

Using the structure type obviates the need to list multiple host variables one by one in an embedded SQL statement when retrieving data from or inserting data into multiple columns in a table. Instead, it is possible to use a single host variable, which makes the development process much more convenient. For example, a structure-type host variable can be used in the VALUES clause of an INSERT statement, or in the INTO clause of a SELECT statement.

Even arrays of structures and structures containing arrays are valid data types for use as host variables. For more information about the use of arrays, please refer to Chapter 9: Using Arrays in Embedded SQL Statements.

Limitations

• When a host variable is a structure, the corresponding indicator variable must also be a structure, and must have the same number of elements as the host variable.

  Example:

  EXEC SQL BEGIN DECLARE SECTION;
  struct tag1 { int i1; int i2; } var1;
  struct tag2 { int i1_ind; int i2_ind; } var1_ind1;
  struct tag3 { int i1_ind; int i2_ind; int i3_ind; } var1_ind2; 
  EXEC SQL END DECLARE SECTION;
  
  EXEC SQL INSERT INTO T1(I1, I2) 
  VALUES (:var1 :var1_ind1);  (O)
  EXEC SQL INSERT INTO T1(I1, I2) 
  VALUES (:var1 :var1_ind2);  (X)
  

• Nested structures cannot be used as host variables. In other words, a structure cannot have another structure as one of its constituent elements.

  Example:

  EXEC SQL BEGIN DECLARE SECTION;
  struct tag1 
  {   
  int i1; 
  struct tag2 
  { 
  int i2; 
  int i3; 
  } sub_var; 
  } var1;         (X)
  EXEC SQL END DECLARE SECTION;
  

• It is impossible to use an indicator variable with a host variable that is an array of structures. This means that it is necessary to guarantee that no NULL column values are returned when using an array of structures as an output host variable. If a NULL column value is returned, the value stored in the sqlca.sqlcode variable will be SQL_SUCCESS_WITH_INFO.

  Example:

  EXEC SQL BEGIN DECLARE SECTION;
  struct tag1 {int i1; int i2; char i3[11]; } var1[10];
  struct tag2 {int i1_ind; int i2_ind; int i3_ind; } var1_ind[10];
  EXEC SQL END DECLARE SECTION;
  
  EXEC SQL INSERT INTO T1(I1, i2, i3) 
  VALUES (:var1 :var1_ind);   (X)
  

• When using an array of structures as a host variable in the INTO clause of a SELECT or FETCH statement, only one output host variable can be used. In other words, such an output host variable cannot be used with other host variables. Therefore, when using an array of structures as an output host variable in the INTO clause, the underlying structure will need the same number of constituent elements as the number of columns in the select list.
  Similarly, when using an array of structures as a host variable in the VALUES clause of an INSERT statement, only one input host variable can be used. In other words, such an input host variable Host Variable Data Types cannot be used with other host variables. Therefore, when using an array of structures as an input host variable in the VALUES clause, the underlying structure will need the same number of constituent elements as the number of columns in the INSERT statement.

  Example:

  EXEC SQL BEGIN DECLARE SECTION;
  struct tag1 { int i1; int i2; } var1[10];
  int var2[10];
  EXEC SQL END DECLARE SECTION;
  
  EXEC SQL INSERT INTO T1(I1, I2, i3) 
  VALUES (:var1, :var2);      (X)
  

• The last two limitations are due to the internal rule that requires all host variables to be included in the structure when the host variable is a structure array.

Examples

The following example demonstrates the use of the structure type.

In this example, a structure type called goods is defined, and the host variable s_goods, which is of the goods type, is declared. The s_goods variable is then used as an input host variable in an INSERT statement.

< Sample Program : hostvar.h >

EXEC SQL BEGIN DECLARE SECTION;
typedef struct goods
{
    char   gno[10+1];
    char   gname[20+1];
    char   goods_location[9+1];
    int    stock;
    double price;
} goods;
EXEC SQL END DECLARE SECTION;

< Sample Program : insert.sc >

/* specify path of header file */
EXEC SQL OPTION (INCLUDE=./include);
/* include header file for precompile */
EXEC SQL INCLUDE hostvar.h;

EXEC SQL BEGIN DECLARE SECTION;
goods   s_goods;
EXEC SQL END DECLARE SECTION;

strcpy(s_goods.gno, "F111100003");
strcpy(s_goods.gname, "XX-102");
strcpy(s_goods.goods_location, "AD0003");
s_goods.stock = 6000;
s_goods.price = 10200.96;

EXEC SQL INSERT INTO GOODS VALUES (:s_goods);

Extended APRE Data Types

Beyond the fundamental C/C++ data types, APRE provides additional data types for use as host variables and in embedded SQL statements. These extended data types are described below, along with how to use them.

VARCHAR

varchar

The varchar type may be declared in either lower case or upper case, that is, as either varchar or VARCHAR. Internally, it is a kind of structure. For example, if a varchar type variable is declared as follows:

varchar a[10];

The underlying structure would appear thus:

struct { int len; char arr[10] ;}a;

The constituent elements of the varchar type variable can thus be referred to using the period (".") operator in this way: a.rr

The varchar type has its own built-in indicator variable. This role is played by the first constituent element, namely len. Therefore, when it is necessary to use an indicator variable with a varchar type host variable, there is no need to declare a separate indicator variable, thus making the use of the varchar type very convenient.

Although the varchar type comes with its own indicator variable, it is still possible to declare and use a separate indicator variable. This is useful when the varchar type is just one element inside another structure type host variable and a structure type indicator variable is declared for use with the structure type host variable, so that a separate indicator variable corresponding to the varchar type element can be provided inside the structure type indicator variable.

Advantage

Because the varchar type has its own internal indicator variable, there is no need for the user to specify a separate indicator variable. This makes it convenient to use the varchar type when it is necessary to use an indicator variable.

Considerations

• Unless a separate indicator variable is specified for use with a varchar type host variable, len, one of its constituent elements, will function as the indicator variable. Therefore, when using a varchar type variable as an input host variable, it is necessary to expressly specify the value of len when not using a separate indicator variable. If it is desired to input NULL data, then set the value of len to -1. When entering non-NULL data, set the value of len to the actual length of the arr element, excluding the trailing NULL character.

  Example:

  EXEC SQL BEGIN DECLARE SECTION;
  varchar var1;
  EXEC SQL END DECLARE SECTION;
  
  strcpy(var1.arr, "ABC");
  var1.len = strlen(var1.arr);
  EXEC SQL INSERT INTO T1(I1) 
  VALUES (:var1);     (O)
  

• When using the varchar type as an output host variable that will receive a value from a CHAR type column, be sure to declare the length of the host variable so that it is one (1) byte longer than the size of the corresponding CHAR type column. The reason for this is that the length of the data stored in a CHAR type column is fixed, so the length of the data that are returned will always be the same as the length of the column, and the host variable requires one additional byte to store the NULL terminating character at the end. If a host variable is not declared so that it is at least one character longer than the database column to which it corresponds, then when a SELECT or FETCH statement is executed, the value returned in the sqlca.sqlcode variable will be SQL_SUCCESS_WITH_INFO.

• When using a two-dimensional varchar array as a host variable in the INTO clause of a SELECT or FETCH statement, only one output host variable can be used. In other words, such an output host variable cannot be used with other output host variables. Therefore, when using a two-dimensional varchar array as an output host variable in an INTO clause, the corresponding select list can contain only one column.
  Similarly, when using a two-dimensional varchar array as a host variable in the VALUES clause of an INSERT statement, only one input host variable can be used. In other words, such an input host variable cannot be used with other input host variables. Therefore, when using a two-dimensional varchar array as an input host variable in the VALUES clause of an INSERT statement, the VALUES clause can contain only one column value.
  The reason for this is that the varchar type is a structure, and thus the limitation on the use of structures also applies to the varchar type.

  Example:

  EXEC SQL BEGIN DECLARE SECTION;
  varchar var1[10][10+1];
  int var2[10];
  EXEC SQL END DECLARE SECTION;
  
  EXEC SQL INSERT INTO T1(I1, I2) 
  VALUES (:var1, :var2);      (X)
  

Example

The following example shows the use of the varchar type.

In this example, both the input host variable and the output host variable are varchar type host variables. The variable s_cus_job is the input host variable, while the variable s_address is the output host variable. The programmer is responsible for checking the length of s_cus_job.arr and specifying it in s_cus_job.len. Although not shown in the example, after the SELECT statement is executed, it will be necessary to check whether the value of s_address.len is -1, which would indicate that a NULL value was returned.

< Sample Program : varchar.sc >

EXEC SQL BEGIN DECLARE SECTION;
char    s_cname[20+1];
varchar s_cus_job[20+1];
varchar s_address[60+1];
EXEC SQL END DECLARE SECTION;

strcpy(s_cus_job.arr, "WEBMASTER");
s_cus_job.len = strlen(s_cus_job.arr);

EXEC SQL SELECT CNAME, ADDRESS 
INTO :s_cname, :s_address 
FROM CUSTOMERS 
            WHERE CNO = BIGINT'7' 
AND CUS_JOB = :s_cus_job;

Date Types

APRE date types can be used only with DATE type database columns.

Three date types are provided for use within APRE. The developer can choose the date type that is most appropriate for the task at hand.

SQL_DATE_STRUCT

This type consists of year, month, and date elements. Its structure is shown below:

typedef struct tagDATE_STRUCT {
SQLSMALLINT  year;
SQLSMALLINT month;
SQLSMALLINT day;
} DATE_STRUCT;

Example

The following example shows the use of the SQL_DATE_STRUCT type.

In this example, s_date is used as both an input and output host variable.

< Sample Program : date.sc >

EXEC SQL BEGIN DECLARE SECTION;
SQL_DATE_STRUCT s_date;
int s_ind;
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT JOIN_DATE 
INTO :s_date :s_ind 
FROM EMPLOYEES 
WHERE ENO = 3;

s_date.year  = 2003;
s_date.month = 5;
s_date.day   = 9;

EXEC SQL UPDATE EMPLOYEES 
SET JOIN_DATE = :s_date 
WHERE ENO = 3;

SQL_TIME_STRUCT

This type consists of hour, minute, and second elements. Its structure is shown below:

typedef struct tagTIME_STRUCT {
SQLSMALLINT hour;
SQLSMALLINT minute;
SQLSMALLINT second;
} TIME_STRUCT;

Example

The following example shows the use of the SQL_TIME_STRUCT type.

In this example, s_time is used as both an input and output host variable.

< Sample Program : date.sc >

EXEC SQL BEGIN DECLARE SECTION;
SQL_TIME_STRUCT s_time;
int s_ind;
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT JOIN_DATE 
INTO :s_time :s_ind 
FROM EMPLOYEES 
WHERE ENO = 3;

s_time.hour   = 12;
s_time.minute = 12;
s_time.second = 12;

EXEC SQL UPDATE EMPLOYEES 
SET JOIN_DATE = :s_time 
WHERE ENO = 4;

SQL_TIMESTAMP_STRUCT

This type consists of year, month, date, hour, minute, second, and nanosecond elements. Its structure is shown below. The fraction element is the element in which the nanoseconds (i.e. billionths of a second) are stored.

typedef struct tagTIMESTAMP_STRUCT {
SQLSMALLINT  year;
SQLSMALLINT month;
SQLSMALLINT day;
SQLSMALLINT hour;
SQLSMALLINT minute;
SQLSMALLINT second;
SQLINTEGER  fraction;
} TIMESTAMP_STRUCT;

Example

The following example shows the use of the SQL_TIMESTAMP_STRUCT type.

In this example, s_timestamp is used as both an input and output host variable.

< Sample Program : date.sc >

EXEC SQL BEGIN DECLARE SECTION;
SQL_TIMESTAMP_STRUCT s_timestamp;
int s_ind;
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT JOIN_DATE 
INTO :s_timestamp :s_ind 
FROM EMPLOYEES 
WHERE ENO = 3;

s_timestamp.year     = 2003;
s_timestamp.month    = 5;
s_timestamp.day      = 9;
s_timestamp.hour     = 4;
s_timestamp.minute   = 0;
s_timestamp.second   = 15;
s_timestamp.fraction = 100000;

EXEC SQL UPDATE EMPLOYEES 
SET JOIN_DATE = :s_timestamp 
WHERE ENO = 5;    

Real Number Types

SQL_NUMERIC_STRUCT

When using this type, NUMERIC data can be passed

The structure of this type is:

typedef struct tagSQL_NUMERIC_STRUCT
{
    SQLCHAR     precision;
    SQLSCHAR    scale;
    SQLCHAR     sign;   /* 1=pos 0=neg */
    SQLCHAR     val[SQL_MAX_NUMERIC_LEN];
} SQL_NUMERIC_STRUCT;

Example

The following is an example of using the SQL_NUMERIC_STRUCT type

This example uses s_price as an input or output host variable.

< Sample Program : numeric.sc >

/* declare host variables */
EXEC SQL BEGIN DECLARE SECTION;
char                 s_gno[10+1];
char                 s_gname[20+1];
char                 s_goods_location[9+1];
int                  s_stock;
SQL_NUMERIC_STRUCT   s_price;
EXEC SQL END DECLARE SECTION;

int s_price_val = 0;

/* use scalar host variables */
strcpy(s_gno, "F111100002");
strcpy(s_gname, "XX-101");
strcpy(s_goods_location, "FD0003");
s_stock = 5000;

/* set value 123.4 on SQL_NUMERIC_STRUCT */
memset(&s_price, 0, sizeof(s_price));
s_price.precision = 4;
s_price.scale = 1;
s_price.sign = 1;
s_price_val = 1234;
memcpy(&s_price.val, &s_price_val, sizeof(int));

printf("------------------------------------------------------------------\n");
printf("[SQL_NUMERIC_STRUCT Insert]\n");
printf("------------------------------------------------------------------\n");

EXEC SQL INSERT INTO GOODS VALUES (:s_gno, :s_gname, :s_goods_location, :s_stock, :s_price);

memset(s_gname, 0, sizeof(s_gname));
memset(s_goods_location, 0, sizeof(s_goods_location));
s_stock = 0;
memset(&s_price, 0, sizeof(s_price));

printf("------------------------------------------------------------------\n");
printf("[SQL_NUMERIC_STRUCT Select]\n");
printf("------------------------------------------------------------------\n");

EXEC SQL SELECT GNAME, GOODS_LOCATION, STOCK, PRICE INTO :s_gname, :s_goods_location, :s_stock, :s_price FROM GOODS WHERE GNO = :s_gno;
/* check sqlca.sqlcode */
if (sqlca.sqlcode == SQL_SUCCESS)
{
    /* sqlca.sqlerrd[2] holds the rows-processed(inserted) count */
    printf("%d rows select s_gno=%s, s_gname=%s, s_goods_location=%s, s_stock=%d, s_price=%.15G \n\n", 
            sqlca.sqlerrd[2], s_gno, s_gname, s_goods_location, s_stock, APRE_NUMERIC_TO_DOUBLE(s_price));
}
else
{
    printf("Error : [%d] %s\n\n", SQLCODE, sqlca.sqlerrm.sqlerrmc);
}   

Binary Types

The binary type can be used as a host variable type when a column type is blob, BYTE, or NIBBLE.

The binary type is internally defined as following:

typedef char APRE_CLOB;
typedef char APRE_BLOB;
typedef char APRE_BINARY;
typedef char APRE_BINARY2;
typedef char APRE_BYTES;
typedef char APRE_NIBBLE;
typedef char APRE_VARBYTES;

Each of the type is described in detail as follows.

APRE_CLOB

This type can be used only with CLOB type database columns. It is essential that an indicator variable be declared and used with this type.

When using the APRE_CLOB type as an input host variable, set the value of the corresponding indicator variable to -1 to indicate that the value of the host variable is NULL. When the value of the host variable is any other value (i.e. a non-NULL value), set the value of the indicator variable to the length of the data saved in the host variable.

When using this type as an output host variable, NULL is returned when the indicator variable is -1, and if it is greater than 0, the length of the value stored in the host variable is stored in the indicator variable.

Example

The following example demonstrates the use of the APRE_CLOB type.

In this example, ins_clob is an input host variable, and ins_clob_ind is the corresponding input indicator variable. The value of ins_clob_ind is set to the length of the value stored in ins_clob. Meanwhile, sel_clob is an output host variable, and sel_clob_ind is its output indicator variable. After the execution of the SELECT statement, a sel_clob_ind value of -1 means that sel_clob is NULL, whereas a sel_clob_ind value greater than 0 indicates the length of the value stored in sel_clob.

< Sample Program : binary.sc >

EXEC SQL BEGIN DECLARE SECTION;
APRE_CLOB ins_clob[10+1];
APRE_CLOB sel_clob[10+1];
SQLLEN ins_clob_ind;  
SQLLEN sel_clob_ind; 
EXEC SQL END DECLARE SECTION;

memset(ins_clob, 0x41, 10);
ins_clob_ind = 10; /* set length of ins_clob value to indicator variable */

EXEC SQL INSERT INTO T_CLOB
VALUES (:ins_clob :ins_clob_ind);

EXEC SQL SELECT *
INTO :sel_clob :sel_clob_ind
FROM T_CLOB;

APRE_BLOB

This type can be used only with BLOB type database columns.

It is essential that an indicator variable be declared and used with this type.

When using the APRE_BLOB type as an input host variable, set the value of the corresponding indicator variable to -1 to indicate that the value of the host variable is NULL. When the value of the host variable is any other value (i.e. a non-NULL value), set the value of the indicator variable to the length of the data saved in the host variable.

When using this type as an output host variable, a value of -1 in the corresponding indicator variable indicates that a NULL value was returned to the host variable, whereas an indicator variable value greater than 0 indicates that a non-NULL value was returned to the host variable, and furthermore indicates the length of the data saved in the host variable.

Example

The following example demonstrates the use of the APRE_BLOB type.

In this example, ins_blob is an input host variable, and ins_blob_ind is the corresponding input indicator variable. The value of ins_blob_ind is set to the length of the value stored in ins_blob. Meanwhile, sel_blob is an output host variable, and sel_blob_ind is its output indicator variable. After the execution of the SELECT statement, a sel_blob_ind value of -1 means that sel_blob is NULL, whereas a sel_blob_ind value greater than 0 indicates the length of the value stored in sel_blob

< Sample Program : binary.sc >

EXEC SQL BEGIN DECLARE SECTION;
APRE_BLOB ins_blob[10+1];
APRE_BLOB sel_blob[10+1];
SQLLEN ins_blob_ind; 
SQLLEN sel_blob_ind;
EXEC SQL END DECLARE SECTION;

memset(ins_blob, 0x21, 10);
ins_blob_ind = 10; /* set length of ins_blob value to indicator variable */

EXEC SQL INSERT INTO T_BLOB
VALUES (:ins_blob :ins_blob_ind);

EXEC SQL SELECT *
INTO :sel_blob :sel_blob_ind
FROM T_BLOB;

APRE_BINARY

This type is identical to the APRE_BLOB type.

Example

The following example illustrates the use of the APRE_BINARY type.

Use ins_blob as the input host variable and ins_blob_ind as the input indicator variable. ins_blob_ind stores the length of ins_blob. Output sel_blob as output host variable Use the indicator variable sel_blob_ind. After executing the SELECT statement, sel_blob_ind stores -1 if the sel_blob value is NULL, otherwise the length of sel_blob is stored.

< Sample Program : binary.sc >

EXEC SQL BEGIN DECLARE SECTION;
APRE_BINARY ins_blob[10+1]; 
APRE_BINARY sel_blob[10+1];
int ins_blob_ind;   
int sel_blob_ind;  
EXEC SQL END DECLARE SECTION;

memset(ins_blob, 0x21, 10);
ins_blob_ind = 10; /* set length of ins_blob value to indicator variable */

EXEC SQL INSERT INTO T_BLOB 
VALUES (:ins_blob :ins_blob_ind);

EXEC SQL SELECT * 
INTO :sel_blob :sel_blob_ind 
FROM T_BLOB;

APRE_BINARY2

This has the same characteristics as APRE_BLOB and APRE_BINARY. However, when the data size of the input host variable is less than 128KB, the performance is improved, and the maximum data size is 100MB. When the data size exceeds 128KB, the use of the APRE_BLOB and APRE_BINARY types is recommended to improve performance and save memory.

Example

The following example shows how to use the APRE_BINARY2 type.

Use ins_blob as the input host variable and ins_blob_ind as the input indicator variable. ins_blob_ind stores the length of ins_blob.

Output sel_blob as output host variable Use sel_blob_ind as an indicator variable.

After executing the SELECT statement, sel_blob_ind stores -1 if the sel_blob value is NULL, otherwise the length of sel_blob is stored.

< Sample Program : binary.sc >

EXEC SQL BEGIN DECLARE SECTION;
APRE_BINARY2 ins_blob[10+1];
APRE_BINARY2 sel_blob[10+1];
int ins_blob_ind;
int sel_blob_ind;
EXEC SQL END DECLARE SECTION;

memset(ins_blob, 0x21, 10);
ins_blob_ind = 10; /* set length of ins_blob value to indicator variable */

EXEC SQL INSERT INTO T_BLOB
VALUES (:ins_blob :ins_blob_ind);

EXEC SQL SELECT *
INTO :sel_blob :sel_blob_ind 
FROM T_BLOB;

APRE_BYTES

The APRE_BYTES type can be used only with BYTE type database columns. In all other respects, it is identical to the APRE_BLOB type.

Example

The following example illustrates the use of the APRE_BYTES type.

In this example, ins_bytes is an input host variable, and ins_bytes_ind is the corresponding input indicator variable. The value of ins_bytes_ind is set to the length of the value stored in ins_bytes.

Meanwhile, sel_bytes is an output host variable, and sel_bytes_ind is its output indicator variable. After the execution of the SELECT statement, a sel_bytes_ind value of -1 means that sel_bytes is NULL, whereas a sel_bytes_ind value greater than 0 indicates the length of the value stored in sel_bytes.

< Sample Program : binary.sc >

EXEC SQL BEGIN DECLARE SECTION;
APRE_BYTES ins_bytes[5+1]; 
APRE_BYTES sel_bytes[5+1];
int ins_bytes_ind;   
int sel_bytes_ind;  
EXEC SQL END DECLARE SECTION;

memset(ins_bytes, 0x21, 5);
ins_bytes_ind = 5; /* set length of ins_bytes value to indicator variable */

EXEC SQL INSERT INTO T_BYTES 
VALUES (:ins_bytes :ins_bytes_ind);

EXEC SQL SELECT * 
INTO :sel_bytes :sel_bytes_ind 
FROM T_BYTES;

APRE_NIBBLE

The APRE_NIBBLE type can be used only with NIBBLE type database columns.

When using the APRE_NIBBLE type as an input host variable, use an indicator variable to indicate that the value of the host variable is NULL, but use the first byte of the host variable to indicate the length of the host variable when the value of the host value is any other value (i.e. a non-NULL value). Note that the indicator variable will take precedence over the first byte of the host variable. That is, the value of the indicator variable is first checked, and if it is found to be -1, the host variable is handled as having a NULL value. If the value of the indicator variable is anything other than -1, the first byte of the host variable is taken as the length of the input data. Therefore, to input NULL data, the indicator variable must be set to -1, whereas to input other (non-NULL) values, the length of the input data must be specified in the first byte of the host variable.

Because the length of the input data is stored in the first byte, the actual data will be stored starting in the second byte of the host variable. Therefore, the length of the input data, that is, the nibble count, will be counted from the second byte of the host variable. One nibble is 4 bits.

When using the APRE_NIBBLE type as an output host variable, a value of -1 in the corresponding indicator variable indicates that a NULL value was returned to the host variable. An indicator variable value greater than 0 indicates the total length, in bytes, of the data saved in the host variable.

Meanwhile, as mentioned above, the length of the actual output data, in nibbles, (one nibble = 4 bits) is stored in the first byte of the host variable, and the actual data are stored starting in the second byte.

Therefore, when non-NULL data are returned, the data length is indicated by both the indicator variable and the first byte of the host variable. The relationship between the two data length values is as follows:

(indicator variable) = ((first byte of host variable + 1)/2 + 1)

Example

The following example illustrates the use of the APRE_NIBBLE type.

In this example, ins_nibble is an input host variable. Because the value to be input is not NULL, the length of the actual data stored in ins_nibble is set in the first byte of ins_nibble.

Meanwhile, sel_nibble is an output host variable, and sel_nibble_ind is its output indicator variable. After the execution of the SELECT statement, a sel_nibble_ind value of -1 means that sel_nibble is NULL, whereas a sel_nibble_ind value greater than 0 indicates the total length, in bytes, of the value stored in sel_nibble. Additionally, the first byte in sel_nibble (i.e. sel_nibble[0]) contains the total length, in nibbles, of the actual data, which are stored starting from the second byte of sel_nibble (i.e. sel_nibble[1]).

< Sample Program : binary.sc >

EXEC SQL BEGIN DECLARE SECTION;
APRE_NIBBLE ins_nibble[5+2]; 
APRE_NIBBLE sel_nibble[5+2];
int sel_nibble_ind;  
EXEC SQL END DECLARE SECTION;

memset(ins_nibble+1, 0x21, 5);
ins_nibble[0] = 10; /* set length of ins_nibble value to ins_nibble[0] */

EXEC SQL INSERT INTO T_NIBBLE 
VALUES (:ins_nibble);

EXEC SQL SELECT * 
INTO :sel_nibble :sel_nibble_ind 
FROM T_NIBBLE;

APRE_VARBYTES

This is availble to use when a column type is VARBYTE; otherwise, other features are identicl to that of the APRE_BLOB.

Sample Program

varchar.sc

This sample program can be found at $ALTIBASE_HOME/sample/APRE/varchar.sc.

Result of Execution

$ is -f schema/schema.sql
$ make varchar
$ ./varchar
<VARCHAR TYPE>
-----------------------------------------------------------
[Scalar VARCHAR]                                                  
-----------------------------------------------------------
s_cname       = [DKHAN               ]
s_address.arr = [YeongdeungpoGu Seoul]
s_address.len = [20]

-----------------------------------------------------------
[Array of VARCHAR]                                                
-----------------------------------------------------------
CUS_JOB                                                           
-----------------------------------------------------------
ENGINEER
DOCTOR
DESIGNER
ENGINEER
WEBMASTER
WEBPD
PLANER
PD
DESIGNER
NULL
MANAGER
BANKER
ENGINEER
BANKER
MANAGER
PLANER
NULL
ENGINEER
NULL
WEBMASTER

-----------------------------------------------------------
[Structure Included VARCHAR]                                      
-----------------------------------------------------------
Success insert

-----------------------------------------------------------
[Array of Structure Included VARCHAR]                             
-----------------------------------------------------------
3 rows inserted
3 times insert success

date.sc

This sample program can be found at $ALTIBASE_HOME/sample/APRE/date.sc.

Result of Execution

$ is -f schema/schema.sql
$ make date
$ ./date
<DATE TYPE>
------------------------------------------------------
[SQL_DATE_STRUCT]                                                 
------------------------------------------------------
JOIN_DATE of ENO is 3 : 2000/1/11

------------------------------------------------------
[SQL_TIME_STRUCT]                                                 
------------------------------------------------------
JOIN_DATE of ENO is 3 : 0:0:0

------------------------------------------------------
[SQL_TIMESTAMP_STRUCT]                                            
------------------------------------------------------
JOIN_DATE of ENO is 3 : 2000/1/11 0:0:0:0

------------------------------------------------------
[SQL_DATE_STRUCT]                                                 
------------------------------------------------------
Success update with SQL_DATE_STRUCT
1 rows updated

------------------------------------------------------
[SQL_TIME_STRUCT]                                                 
------------------------------------------------------
Success update with SQL_TIME_STRUCT
1 rows updated

------------------------------------------------------
[SQL_TIMESTAMP_STRUCT]                                            
------------------------------------------------------
Success update with SQL_TIMESTAMP_STRUCT
1 rows updated

------------------------------------------------------
[Array of Structure Included Date Type]                           
------------------------------------------------------
Success insert
3 rows inserted
3 times insert success

binary.sc

This sample program can be found at $ALTIBASE_HOME/sample/APRE/binary.sc.

Result of Execution

$ is -f schema/schema.sql
$ make binary
$ ./binary
<BINARY TYPE>
------------------------------------------------------
[APRE_CLOB]                                                     
------------------------------------------------------
Success insert with APRE_CLOB
sel_clob     = AAAAAAAAAA
sel_clob_ind = 10

------------------------------------------------------
 [APRE_BLOB]                                                     
------------------------------------------------------
Success insert with APRE_BLOB
sel_blob     = !!!!!!!!!!
sel_blob_ind = 10
------------------------------------------------------
[APRE_BINARY]                                                      
------------------------------------------------------
Success insert with APRE_BINARY
sel_blob     = !!!!!!!!!!
sel_blob_ind = 10

------------------------------------------------------
[APREBYTES]                                                       
------------------------------------------------------
Success insert with APRE_BYTES
sel_bytes     = !!!!!
sel_bytes_ind = 5

------------------------------------------------------
[APRE_NIBBLE]                                                      
------------------------------------------------------
Success insert with APRE_NIBBLE
sel_nibble     = !!!!!
sel_nibble_ind = 6
sel_nibble[0]  = 10

Column and Host Variable Type Conversion

Various host variable types can be used with each database column type. The following tables set forth the type conversions that are possible between host variable types and column types.

Input Host Variables

The following table lists the available input host variable types by column type. In addition, Host Variable Type with Minimum Conversion Cost' is the recommended type and you can expect performance improvement when using this type.

Column Type		Host variable types that can be converted into the column type	Host variable types that incur the minimum conversion expense
Character type	CHAR	char, varchar, short, int, long, long long, double, float, SQL_DATE_STRUCT, SQL_TIME_STRUCT, SQL_TIMESTAMP_STRUCT,	char, varchar
	VARCHAR	char, varchar, short, int, long, long long, double, float, SQL_DATE_STRUCT, SQL_TIME_STRUCT, SQL_TIMESTAMP_STRUCT,	char, varchar
Integer type	SMALLINT	char, varchar, short, int, long, long long, double, float	short
	INTEGER	char, varchar, short, int, long, long long, double, float	int
	BIGINT	char, varchar, short, int, long, long long, double, float	long, long long
Real number type	NUMERIC NUMBER DECIMAL	char, varchar, short, int, long, long long, double, float, SQL_NUMERIC_STRUCT	char, long, long long, float, double
	FLOAT	char, varchar, short, int, long, long long, double, float	float
	REAL	char, varchar, short, int, long, long long, double, float	double
	DOUBLE	char, varchar, short, int, long, long long, double, float	double
Date Type	DATE	char, SQL_DATE_STRUCT, SQL_TIME_STRUCT, SQL_TIMESTAMP_STRUCT	char, SQL_DATE_STRUCT, SQL_TIME_STRUCT, SQL_TIMESTAMP_STRUCT
Binary Type	CLOB	APRE_CLOB	APRE_CLOB
	BLOB	APRE_BLOB, APRE_BINARY, APRE_BINARY2	APRE_BLOB, APRE_BINARY, APRE_BINARY2
	BYTE	APRE_BYTES, APRE_VARBYTES	APRE_BYTES, APRE_VARBYTES
	NIBBLE	APRE_NIBFBLE	APRE_NIBBLE
	VARBYTE	APRE_BYTES, APRE_VARBYTES	APRE_BYTES, APRE_VARBYTES

Output Host Variables

This table sets forth the output host variable types into which each database column type can be converted. In addition, 'Host variable type with minimum conversion cost' is the recommended type and can expect performance improvement when using this type.

Column Type		Host variable types into which the column type can be converted	Host variable types that incur the minimum conversion expense
Character Type	CHAR	char, varchar, APRE_BINARY, APRE_BINARY2	char, varchar
	VARCHAR	char, varchar, APRE_BINARY, APRE_BINARY2	char, varchar
Integer Type	SMALLINT	char, varchar, short, int, long, long long, double, float, APRE_BINARY, APRE_BINARY2	short
	INTEGER	char, varchar, short, int, long, long long, double, float, APRE_BINARY, APRE_BINARY2	int
	BIGINT	char, varchar, short, int, long, long long, double, float, APRE_BINARY, APRE_BINARY2	long, long long
Real Number Type	NUMERIC NUMBER DECIMAL	char, varchar, short, int, long, long long, double, float, APRE_BINARY, APRE_BINARY2, SQL_NUMERIC_STRUCT	char, long, long long, float, double
	FLOAT	char, varchar, short, int, long, long long, double, float, APRE_BINARY, APRE_BINARY2	float
	REAL	char, varchar, short, int, long, long long, double, float, APRE_BINARY, APRE_BINARY2	double
	DOUBLE	char, varchar, short, int, long, long long, double, float, APRE_BINARY, APRE_BINARY2	double
Data Type	DATE	char, SQL_DATE_STRUCT, SQL_TIME_STRUCT, SQL_TIMESTAMP_STRUCT, APRE_BINARY, APRE_BINARY2	char, SQL_DATE_STRUCT, SQL_TIME_STRUCT, SQL_TIMESTAMP_STRUCT
Binary Type	CLOB	APRE_CLOB	APRE_CLOB
	BLOB	APRE_BLOB, APRE_BINARY, APRE_BINARY2	APRE_BLOB, APRE_BINARY, APRE_BINARY2
	BYTE	APRE_BYTES, APRE_VARBYTES, APRE_BINARY, APRE_BINARY2	APRE_BYTES, APRE_VARBYTES
	NIBBLE	APRE_NIBBLE, APRE_BINARY, APRE_BINARY2	APRE_NIBBLE
	VARBYTE	APRE_BYTES, APRE_VARBYTES, APRE_BINARY, APRE_BINARY2	APRE_BYTES, APRE_VARBYTES

The APRE_BINARY type can be used as an output host variable for all column types. The APRE_BINARY type does not involve any type conversion, because assigning a value to this type merely invokes the memcpy() function to store the contents that were retrieved from the database in the host variable without change. It is thus necessary to understand how each column type stores data in memory and to be able to interpret the contents of memory in order to use the APRE_BINARY type as a host variable.

Therefore, although the lack of type conversion means that performance will likely improve when the APRE_BINARY type is used as a host variable, the requirement to understand how data are stored in memory complicates development tasks. Therefore, in most cases it is recommended that the APRE_BINARY type be used only with BLOB type columns.