skip to main content
Schema definition and management : Schema objects : Columns catalog table OA_COLUMNS : Specifying data types
 

Specifying data types

The data type of each column must be specified in the DATA_TYPE and TYPE_NAME columns of the OA_COLUMNS table. The TYPE_NAME is included to allow the client applications to query the schema and obtain a human readable value for the data type. The DATA_TYPE field contains an integer representing the type and is used by the OpenAccess SDK SQL engine for processing. The data type is the type exposed to the client and has nothing to do with how the data is actually stored in the database. In the following table, SQL Type Name contains the valid values for the TYPE_NAME field, and DATA TYPE contains the valid values for the DATA_TYPE field. The IP implementer may choose to expose everything as a character string since this is the final output of reporting and GUI applications.
 
Table 24: Data type name to number mapping  

SQL Type Name
Data
type

ODBC Ttpe

OLE DB type

Description
BIGINT
-5
SQL_BIGINT
DBTYPE_I8
Signed 64-bit integer.
BINARY
-2
SQL_BINARY
DBTYPE_BYTES
Binary data of fixed length n (1 <= n <= 255).
BIT
-7
SQL_BIT
DBTYPE_BOOL
Single bit binary data (0 or 1).
CHAR
1
SQL_CHAR
DBTYPE_STR
Character string of fixed string length n (1<= n<=254).
DATE
9
SQL_DATE
DBTYPE_DBDATE
Date containing year, month and day.
DOUBLE
8
SQL_DOUBLE
DBTYPE_R8
Signed, approximate, numeric value with a mantissa precision 15 (zero or absolute value 10-308 to 10308).
FLOAT
6
SQL_FLOAT
DBTYPE_R8
Signed, approximate, numeric value with a mantissa precision 15 (zero or absolute value 10-308 to 10308).
INTEGER
4
SQL_INTEGER
DBTYPE_I4
Exact numeric value with precision 10 and scale 0. (signed: -231 <=n <=231-1, unsigned:0<=n<=232-1).
LONGVARBINARY
-4
SQL_
LONGVARBINARY
DBTYPE_BYTES
Variable length binary data of any size < 2 GB.
LONGVARCHAR
-1
SQL_
LONGVARCHAR
 
Variable length character string up to 2 GB in length.
NUMERIC
2
SQL_NUMERIC
DBTYPE_STR
Signed, exact, numeric value with a precision p and scale s (1<=p<=40, 0<=s<=p).
REAL
7
SQL_REAL
DBTYPE_R4
Signed, approximate, numeric value with a mantissa precision 7 (zero or absolute value 10-38 to 1038).
SMALLINT
5
SQL_SMALLINT
DBTYPE_I2
Exact numeric value with precision 5 and scale 0. (signed: -32768 <=n <=32767, unsigned:0<=n<=65535).
TIME
10
SQL_TIME
DBTYPE_DBTIME
Time containing hour, minute and seconds.
TIMESTAMP
11
SQL_TIMESTAMP
DBTYPE_
DBTIMESTAMP
Date/time data.
TINYINT
-6
SQL_TINYINT
DBTYPE_I1
Exact numeric value with precision 3 and scale 0. (signed: -128 <=n<=127, unsigned: 0<=n<=255).
VARBINARY
-3
SQL_VARBINARY
DBTYPE_BYTES
Variable length binary data with maximum size N as defined in the schema definition. Optimized for N <=4 KB.
VARCHAR
12
SQL_VARCHAR
DBTYPE_STR
Variable length character string with maximum size N as defined in the schema definition. Optimized for N <= 4 KB.
WCHAR
-8
SQL_WCHAR
DBTYPE_STR
Unicode character string of fixed length n (1<= n<=254).
WLONGVARCHAR
-10
SQL_
WLONGVARCHAR
DBTYPE_STR
Variable length Unicode string of any size < 2 GB.
WVARCHAR
-9
SQL_WVARCHAR
DBTYPE_STR
Variable length Unicode string with maximum size N as defined in the schema definition. Optimized for N <= 4 KB.

Support case-insensitive and ignore case for all/some columns

The IP can control the case sensitive setting and how blanks are treated when string values are compared during comparison and LIKE operations. The default behavior of the OpenAccess SDK SQL engine is to ignore case for LIKE operations. Comparison operations, by default, are case-sensitive and do not ignore trailing blanks.

Specify global setting for entire database

The IP can call dam_setOption() from IP CONNECT to apply settings for all operations:
• To make all string operations case-insensitive the IP can call: dam_setOption(DAM_CONN_OPTION, dam_hdbc, DAM_CONN_OPTION_CASE_IN_STRINGS, DAM_CIS_IGNORE_ALL).
• To make all string operations ignore trailing blanks, the IP can call: dam_setOption(DAM_CONN_OPTION, dam_hdbc, DAM_CONN_OPTION_BLANKS_IN_STRINGS, DAM_BIS_IGNORE_TRAILING).

Specify settings for specific columns

To apply settings to only specific columns, the IP can set the required option value in the OA_SUPPORT field of the column schema definition. A Static schema IP can set the value in OA_SUPPORT field in the OA_COLUMNS. A Dynamic schema IP would pass the option value in the operator_support argument when calling dam_add_damobj_column() and dam_set_damobj_column(operator_support).
• To make string operations of the column to be case-insensitive, OA_SUPPORT set to "0x2000".
• To make string operations of the column to ignore trailing blanks, OA_SUPPORT set to "0x4000".