Data types in embedded SQL

UltraLite Embedded SQL User's Guide
Data Access Using Embedded SQL
Using host variables

Data types in embedded SQL

To transfer information between a program and the database server, every piece of data must have a data type. You can create a host variable with any one of the supported 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.

Macros defined in the sqlca.h header file can be used to declare a host variable of type VARCHAR, FIXCHAR, BINARY, DECIMAL, or SQLDATETIME. These macros 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 treats the variable as the appropriate type.

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

16-bit signed integer.
```
short int i;
unsigned short int i;
```
32-bit signed integer.
```
long int l;
unsigned long int l;
```
4-byte floating point number.
```
float f;
```
8-byte floating point number.
```
double d;
```

Packed decimal number.

DECL_DECIMAL(p,s)
typedef struct TYPE_DECIMAL {
   char array[1];
} TYPE_DECIMAL;

NULL-terminated blank-padded character string.

char a[n]; /* n > 1 */
char *a; /* n = 2049 */

Because the C-language array must also hold the NULL terminator, a char a[n] data type maps to a CHAR(n - 1) SQL data type, which can hold n - 1 characters.

Pointers to char, WCHAR, TCHAR

The SQL preprocessor assumes that a pointer to char points to a character array of size 2049 bytes and that this array can safely hold 2048 characters, plus the NULL terminator. In other words, a char* data type maps to a CHAR(2048) SQL type. If that is not the case, your application may corrupt memory. If you are using a 16-bit compiler, requiring 2049 bytes can make the program stack overflow. Instead, use a declared array, even as a parameter to a function, to let the SQL preprocessor know the size of the array. WCHAR and TCHAR behave similarly to char.

NULL terminated UNICODE or wide character string.

Each character occupies two bytes of space and so may contain UNICODE characters.
```
WCHAR a[n]; /* n > 1 */
```
NULL terminated system-dependent character string.

A TCHAR is equivalent to a WCHAR for systems that use UNICODE (for example, Windows CE) for their character set; otherwise, a TCHAR is equivalent to a char. The TCHAR data type is designed to support character strings in either kind of system automatically.
```
TCHAR a[n]; /* n > 1 */
```

Fixed-length blank padded character string.

char a; /* n  = 1 */
DECL_FIXCHAR(n) a; /* n >= 1 */

Variable-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).
```
DECL_VARCHAR(n) a; /* n >= 1 */
typedef struct VARCHAR {
   unsigned short int len;
   TCHAR array[1];
} VARCHAR;
```
Variable-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.
```
DECL_BINARY(n) a; /* n >= 1 */
typedef struct BINARY {
   unsigned short int len;
   unsigned char array[1];
} BINARY;
```
SQLDATETIME structure with fields for each part of a timestamp.
```
DECL_DATETIME a;
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 via, 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. While these options cannot be set during execution of an UltraLite program, their values are identical to the settings in the reference database used to generate the program.
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.
```
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.

The structures are defined in the sqlca.h file. The VARCHAR, BINARY, and TYPE_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 fetched and updated either using the SQLDATETIME structure or using character strings.

There are no embedded SQL interface data types for LONG VARCHAR and LONG BINARY database types.