ARCHIBUS AfmType vs Database Data Types

ARCHIBUS schema supports the following data types. These have different physical data types under Sybase, 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:

AfmType	Sybase	Oracle	SQLServer	Size Matters	Decimals Matters

SQL_CHAR	"CHAR(%d)"	"VARCHAR2(%d)"	"CHAR(%d)"	TRUE	FALSE

SQL_NUMERIC	"NUMERIC(%d,%d)"	"NUMBER(%d,%d)"	"NUMERIC(%d,%d)"	TRUE	TRUE
SQL_DECIMAL	"NUMERIC(%d,%d)"	"NUMBER(%d,%d)"	"NUMERIC(%d,%d)"	TRUE	TRUE
SQL_INTEGER	"INTEGER"	"NUMBER(*,0)"	"INTEGER"	FALSE	FALSE
SQL_SMALLINT	"SMALLINT"	"NUMBER(*,0)"	"SMALLINT"	FALSE	FALSE
SQL_FLOAT	"FLOAT"	"FLOAT"	"FLOAT(53)"	FALSE	FALSE
SQL_REAL	"REAL"	"REAL"	"REAL"	FALSE	FALSE
SQL_DOUBLE	"DOUBLE"	"NUMBER(%d,%d)"	"FLOAT(53)"	FALSE	FALSE
SQL_DATE	"DATE"	"DATE"	"DATETIME"	FALSE	FALSE
SQL_TIME	"TIME"	"DATE"	"DATETIME"	FALSE	FALSE
SQL_TIMESTAMP	"DATE"	"DATE"	"DATETIME"	FALSE	FALSE
SQL_VARCHAR	"VARCHAR(%d)"	"VARCHAR2(%d)"	"VARCHAR(%d)"	TRUE	FALSE
SQL_LONGVARCHAR(-1)	"VARCHAR(%d)"	"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). Under Sybase, this is the default date format.

Time Fields -- Sybase contains separate DATE and TIME field types. 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 -- Sybase does not require a size; Oracle does. 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; Sybase VARCHARs have a length of 32K bytes. 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 and keeping the BYTE values allow users with V.17 and V.18 Windows client/server and client/server Overlay products to use the newer databases.

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.