Archibus AFM Type vs. Database Data Types
Archibus schema supports the following data types. These have different physical data types under Microsoft SQL Server and Oracle. ODBC also supplies type information in a different format. Internal to Archibus, the program stores these data items in memory objects of yet another type.
The type mappings are:
| AFM Type | Oracle | SQL Server | Size Matters | Decimals Matters |
|---|---|---|---|---|
| SQL_CHAR | "VARCHAR2(%d)" | "CHAR(%d)" | TRUE | FALSE |
|
SQL_NUMERIC |
"NUMBER(%d,%d)" |
"NUMERIC(%d,%d)" |
TRUE |
TRUE |
|
SQL_DECIMAL |
"NUMBER(%d,%d)" |
"NUMERIC(%d,%d)" |
TRUE |
TRUE |
|
SQL_INTEGER |
"NUMBER(*,0)" |
"INTEGER" |
FALSE |
FALSE |
|
SQL_SMALLINT |
"NUMBER(*,0)" |
"SMALLINT" |
FALSE |
FALSE |
|
SQL_FLOAT |
"FLOAT" |
"FLOAT(53)" |
FALSE |
FALSE |
|
SQL_REAL |
"REAL" |
"REAL" |
FALSE |
FALSE |
|
SQL_DOUBLE |
"NUMBER(%d,%d)" |
"FLOAT(53)" |
FALSE |
FALSE |
|
SQL_DATE |
"DATE" |
"DATETIME" |
FALSE |
FALSE |
|
SQL_TIME |
"DATE" |
"DATETIME" |
FALSE |
FALSE |
|
SQL_TIMESTAMP |
"DATE" |
"DATETIME" |
FALSE |
FALSE |
|
SQL_VARCHAR |
"VARCHAR2(%d)" |
"VARCHAR(%d)" |
TRUE |
FALSE |
|
SQL_LONGVARCHAR(-1) |
"VARCHAR2(%d)" |
"VARCHAR(%d)" |
TRUE |
FALSE |
Note the following about these data types:
- SQL_LONGVARCHAR(-1) is not used.
- Date ISO Format -- You must create all databases so that they interpret dates supplied to SQL statements (e.g., CREATE TABLE) and from Basic in ISO (YYYY-MM-DD) format. Under Oracle, use the NLS-DATE-FORMAT feature and take care to specify ISO format dates (YYYY-MM-DD).
- Time Fields -- Oracle and SQL Server have a single field type, DATE, which dedicates a portion of the field to the date value and a portion to the time. For Oracle projects, Archibus uses the DATE data type for both date and time fields. For DATE fields, Archibus fills in a placeholder value for the time portion; for TIME fields, Archibus fills in a placeholder value for the date portion.
- Max Double Size -- Due to the Archibus internal representation of doubles as an IEEE 8 byte quantity, 8 bytes is the maximum double size.
- Specifying Varchars -- Oracle requires a size. Always specify a size so that the schema can run under all databases.
- Max Varchar Size -- Oracle VARCHAR2 fields have a maximum size of 4000 characters. Microsoft SQL Server does not have a maximum size for any individual field, but you must make certain that the total number of bytes is less than 8060 for any record. Under Oracle, if you specify a size over 2000 or 4000, the Update Project Wizard will use 2000 or 4000 for a maximum size, depending on your server.
- Long Varchar Fields -- The Database Update Wizard will map fields with an Archibus schema type LONG VARCHAR to the Oracle LONG data type.
Oracle LONG Fields
The Archibus default schema uses only VARCHAR2 fields and avoids the Oracle LONG fields. This mapping works around several limitations of the LONG data type under Oracle.
- Oracle allows only one LONG field per table.
- The OraDynaset object provided by Oracle Objects for OLE does not fetch values from LONG fields into joined cursors. As a result, LONG values will not appear in the grid or in reports that have a table in the standard role in the table-group, since the standard table is joined to the table in the assigned role.
- Oracle does not implement LONG fields consistently within the SQL language. Many SQL statements will not work with fields of type LONG. For instance, you cannot issue an INSERT statement that selects a LONG value from table wr and inserts that LONG value into hwr. If you do use a LONG field, Archibus will read it into the ODBC Type SQL_VARCHAR and the AFX type of CString. Please refer to your Oracle SQL Language Reference Manual for more restrictions on the use of the LONG datatype in Oracle.
Specifying Field Widths
For international versions, Archibus uses Unicode UTF-8 encoding so that the database can record strings from multiple languages in the same database.
For historical reasons, Archibus field widths (A/FM Fields, Size values) are in BYTEs rather than the number of Unicode CHARs. (The Oracle setting for this is the NLS_LENGTH_SEMANTICS with which the field was created.) Current databases keep this length, as the difference affects few sites.
Sites who want to use the Unicode CHAR field width measurement can change this setting by using the Schema Change Wizard. The Smart Client, the Web Central Java routines, and the Web Central JavaScript forms all work with this setting. Using the Unicode CHAR field width measurement avoids any potential truncation of string values that users enter, since the size measurement on forms and in the databases are then identical.