Specifying data types
- Last Updated: May 12, 2026
- 3 minute read
- OpenAccess SDK
- Version 8.1
- Documentation
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.
Data type name to number mapping
| SQL Type Name | Datatype | 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".