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

Columns catalog table OA_COLUMNS

The OA_COLUMNS table defines the column name, type, length and description of all the columns from the tables in the database that are being managed by the associated IP. This table provides information for the SQLColumns and SQLSpecialColumns ODBC calls.
Column names can have a maximum length of 128 characters, must begin with a letter of the alphabet or underscore(_), and may contain alphanumeric characters and the following characters: _ @ # ^ $ & ~ %. Alphanumeric characters include a-z, A-Z and all Unicode characters that are not considered as punctuation, control or space characters. If identifier names contain spaces or any other special characters, client applications must use quoted identifiers to query these tables. If identifiers are quoted, then these restrictions do not apply.; however, not all applications quote the identifiers.
Use of the backslash ( \ ) character is not supported as it conflicts with the ODBC use of it as an escape character in SQLTables and other catalog functions.
 
Table 23: Definition of OA_COLUMNS table 
Column name
Type
Len
Description
TABLE_QUALIFIER
WVARCHAR
128
The name of the database in which the table is created (SCHEMA is normally used).
TABLE_OWNER
WVARCHAR
128
The owner of the table. It is normally set to system or user.
SYSTEM—The table is managed by the OpenAccess SDK SQL engine.
OAUSER—the table is managed by the IP.
TABLE_NAME
WVARCHAR
128
The name of the table.
COLUMN_NAME
WVARCHAR
128
The name of the column of the specified table.
DATA_TYPE
SMALLINT
Data type of this column. See Table 24 for the number corresponding to the supported types.
TYPE_NAME
WVARCHAR
254
Identifies the XO column type; used to display to end user. The OpenAccess SDK SQL engine uses information from the data_type column. See Table 24.
OA_LENGTH
INTEGER
 
Length in bytes of data transferred to client in its default format.
BIGINT – 20
BIT – 1
CHAR and VARCHAR—maximum length of the string.
NUMERIC—precision + 2.
INTEGER=4, SMALLINT=2, DOUBLE=8, REAL=4, FLOAT=8.
DATE=6, TIME=6, TIMESTAMP=16.
LONGVARBINARY—maximum length of the column.
WCHAR, WVARCHAR, WLONGVARCHAR – maximum length of the string * 2
TINYINT – 1
OA_PRECISION
 
INTEGER
 
 
Maximum number of digits used by the data in the column.
NUMERIC—Number of digits.
CHAR and VARCHAR—set to same as OA_LENGTH.
INTEGER=10, SMALLINT=5, DOUBLE=15, REAL=7, FLOAT=15.
DATE=10, TIME=8.
OA_PRECISION
 
INTEGER
 
TIMESTAMP=19 if the timestamp format is yyyy-mm-dd hh:mm:ss and there is no fractional seconds. If the timestamp uses fractional seconds, it is (19+1+number of fractional seconds). For example if the timestamp uses thousands of a second, the precision is 23 (number of characters in yyyy-mm-dd hh:mm:ss.fff). Maximum precision is 29 (billionth of a second format).
LONGVARBINARY – maximum length of column.
WCHAR, WVARCHAR, WLONGVARCHAR – maximum length of the string
BIGINT – 20
BIT –1
TINYINT – 3
Note: For LONGVARCHAR, WLONGVARCHAR, and LONGVARBINARY, this number can be set to the maximum value of an INTEGER (2147483647) to indicate to the client application that it can contain any amount of data.
OA_RADIX
INTEGER
 
Reserved for future use. Set to null.
OA_SCALE
INTEGER
 
Total number of significant digits to the right of the decimal point.
NUMERIC—Number of digits to the right of the decimal point.
TIMESTAMP—Number of digits to the right of the decimal point in the "yyyy-mm-dd hh:mm:ss.ffffff" format. 0 if no fractional seconds. If the timestamp uses in yyyy-mm-dd hh:mm:ss.fff, the scale is 3.
Other types—Not applicable. Set to null.
OA_NULLABLE
SMALLINT
 
Whether the column can be null:
XO_NO_NULLS (0)—No nulls allowed.
XO_NULLABLE (1)—Can be null.
XO_NULLABLE_UNKNOWN (2)—Nullable unknown.
OA_SCOPE
SMALLINT
 
Scope of the SQL_BEST_ROWID column. If the OA_COLUMNTYPE is set to SQL_BEST_ROWID, set this field to:
SQL_SCOPE_CURROW (0)—Rowid is valid only while positioned on that row.
SQL_SCOPE_TRANSACTION (1)—Rowid is valid for the duration of transaction.
SQL_SCOPE_SESSION (2)—Rowid is valid for the duration of session.
For all other columns, set to NULL.
OA_USERDATA
WVARCHAR
254
Any proprietary data about the column that the IP wants to access. The IP calls dam_describeColDetail to get this information.
OA_SUPPORT
WVARCHAR
128
A place for the IP to maintain which operators it supports for the conditions on this column (such as =, >, LIKE), when this column is considered for a restriction or a search condition. The OpenAccess SDK SQL engine only passes conditions with these operators for the IP to evaluate.
This setting is used only if GETSUPPORT returns false for the specified condition. The value in this field is specified as a hex number with the 0x convention. It is bitwise OR of the following bit masks:
IP_COL_SUPPORT_EQUAL - 0x01
IP_COL_SUPPORT_SMALLER - 0x02
IP_COL_SUPPORT_GREATER - 0x04
IP_COL_SUPPORT_LIKE - 0x08
IP_COL_SUPPORT_ISNULL - 0x10
IP_COL_SUPPORT_NOT - 0x20
IP_COL_SUPPORT_BETWEEN - 0x40
PSEUDO_COLUMN
SMALLINT
 
Indicates whether the column is a pseudo column like Oracle ROWID:
SQL_PC_UNKNOWN (0)—unknown.
SQL_PC_NOT_PSEUDO (1)—this is the normal setting to indicate a regular column.
SQL_PC_PSEUDO (2)—if a column is a pseudo column.
OA_COLUMNTYPE
SMALLINT
 
Defines the type of column:
NULL—for regular columns.
SQL_BEST_ROWID (1)—it is a rowid column.
SQL_ROWVER(2)—if a column is a row version column.
REMARKS
WVARCHAR
254
The description of the column.