Embedded SQL data types

ASA Programming Guide
Embedded SQL Programming

Embedded SQL data types

To transfer information between a program and the database server, every piece of data must have a data type. The embedded SQL data type constants are prefixed with DT_, and can be found in the sqldef.h header file. You can create a host variable of any one of the supported types. You can also use these types in a SQLDA structure for passing data to and from the database.

You can define variables of these data types using the DECL_ macros listed in sqlca.h. For example, a variable holding a BIGINT value could be declared with DECL_BIGINT.

The following data types are supported by the embedded SQL programming interface:

DT_BIT 8-bit signed integer
DT_SMALLINT 16-bit signed integer.
DT_UNSSMALLINT 16-bit unsigned integer
DT_TINYINT 8-bit signed integer
DT_BIGINT 64-bit signed integer
DT_INT 32-bit signed integer.
DT_UNSINT 16-bit unsigned integer
DT_FLOAT 4-byte floating point number.
DT_DOUBLE 8-byte floating point number.

DT_DECIMAL Packed decimal number.

typedef struct DECIMAL {
  char array[1];
} DECIMAL;

DT_STRING NULL-terminated character string. The string is blank-padded if the database is initialized with blank-padded strings.
DT_DATE NULL-terminated character string that is a valid date.
DT_TIME NULL-terminated character string that is a valid time.
DT_TIMESTAMP NULL-terminated character string that is a valid timestamp.
DT_FIXCHAR Fixed-length blank padded character string.
DT_VARCHAR Varying length character string with a two-byte length field. When supplying information to the database server, you must set the length field. When fetching information from the database server, the server sets the length field (not padded).
```
typedef struct VARCHAR {
   unsigned short int len;
   char array[1];
} VARCHAR;
```
DT_LONGVARCHAR Long varying length character data. The macro defines a structure, as follows:
```
#define DECL_LONGVARCHAR( size )         \
  struct { a_sql_uint32    array_len;    \
           a_sql_uint32    stored_len;   \
           a_sql_uint32    untrunc_len;  \
           char            array[size+1];\
         }
```
The DECL_LONGVARCHAR struct may be used with more than 32K of data. Large data may be fetched all at once, or in pieces using the GET DATA statement. Large data may be supplied to the server all at once, or in pieces by appending to a database variable using the SET statement. The data is not null terminated.

For more information, see Sending and retrieving long values.
DT_BINARY Varying length binary data with a two-byte length field. When supplying information to the database server, you must set the length field. When fetching information from the database server, the server sets the length field.
```
typedef struct BINARY {
  unsigned short int len;
  char array[1];
} BINARY;
```
DT_LONGBINARY Long binary data. The macro defines a structure, as follows:
```
#define DECL_LONGBINARY( size )          \
  struct { a_sql_uint32    array_len;    \
           a_sql_uint32    stored_len;   \
           a_sql_uint32    untrunc_len;  \
           char            array[size];  \
         }
```
The DECL_LONGBINARY struct may be used with more than 32K of data. Large data may be fetched all at once, or in pieces using the GET DATA statement. Large data may be supplied to the server all at once, or in pieces by appending to a database variable using the SET statement.

For more information, see Sending and retrieving long values.
DT_TIMESTAMP_STRUCT SQLDATETIME structure with fields for each part of a timestamp.
```
typedef struct sqldatetime {
   unsigned short year; /* e.g. 1999 */
   unsigned char month; /* 0-11 */
   unsigned char day_of_week; /* 0-6 0=Sunday */
   unsigned short day_of_year; /* 0-365 */
   unsigned char day; /* 1-31 */
   unsigned char hour; /* 0-23 */
   unsigned char minute; /* 0-59 */
   unsigned char second; /* 0-59 */
   unsigned long microsecond; /* 0-999999 */
} SQLDATETIME;
```
The SQLDATETIME structure can be used to retrieve fields of DATE, TIME, and TIMESTAMP type (or anything that can be converted to one of these). Often, applications have their own formats and date manipulation code. Fetching data in this structure makes it easier for a programmer to manipulate this data. Note that DATE, TIME, and TIMESTAMP fields can also be fetched and updated with any character type.

If you use a SQLDATETIME structure to enter a date, time, or timestamp into the database, the day_of_year and day_of_week members are ignored.

For more information, see the DATE_FORMAT, TIME_FORMAT, TIMESTAMP_FORMAT, and DATE_ORDER database options in Database Options.
DT_VARIABLE NULL-terminated character string. The character string must be the name of a SQL variable whose value is used by the database server. This data type is used only for supplying data to the database server. It cannot be used when fetching data from the database server.

The structures are defined in the sqlca.h file. The VARCHAR, BINARY, and DECIMAL types contain a one-character array and are thus not useful for declaring host variables but they are useful for allocating variables dynamically or typecasting other variables.

DATE and TIME database types

There are no corresponding embedded SQL interface data types for the various DATE and TIME database types. These database types are all fetched and updated using either the SQLDATETIME structure or character strings.

For more information see GET DATA statement [ESQL] and SET statement.