APPX field type reference
Pick an APPX data dictionary field and see the SQL column APPX would create. Each example gives the settings that matter, a sample value, and the result in each database.
Covers all 13 file field types, with separate examples when the mapping changes. Observed marks a result from an APPX run. The remaining types come from the APPX 5.5.4 and 6.2.2b2 engine rules and driver catalogs. See how the field types were tested. Compare the earlier Demo Application tests.
Character fields
Alpha, Format, and Text all use the character mapping. Length here means the stored length. The edit mask, validation table, and display rows stay in APPX.
| APPX field and example | Oracle 19c · OCI · FMS 5 | PostgreSQL 14.1 · psqlODBC ANSI 18.00.0001 | MySQL 8.4 · MySQL Connector/ODBC 8.4.0 | MariaDB 10.11 · Connector/ODBC 3.1.22 | SQL Server 2022 · Microsoft ODBC Driver 18 | IBM Db2 11.5 CLI · native FMS 8 |
|---|---|---|---|---|---|---|
Alpha · 14-character key
Single-byte storage; part of a key.
COUNTRY NAME = CANADA
Oracle uses CHAR for keys by default. The FMS option Force VARCHAR changes the choice.
|
CHAR(14 CHAR)
5.5.4: CHAR(14)
Observed · APPX 6.2.1: Oracle, Alpha · 14-character key
|
CHARACTER VARYING(14)
Observed · APPX 6.2.1: PostgreSQL, Alpha · 14-character key
|
VARCHAR(14)
|
VARCHAR(14)
Observed · APPX 6.2.1: MariaDB, Alpha · 14-character key
|
VARCHAR(14)
Observed · APPX 6.2.1: SQL Server, Alpha · 14-character key
|
VARCHAR(14)
|
Alpha · Short non-key
3 characters, single-byte storage.
STATE CODE = NY
Oracle also defaults to fixed-length CHAR for short fields. ODBC generally prefers VARCHAR.
|
CHAR(3 CHAR)
5.5.4: CHAR(3)
Observed · APPX 6.2.1: Oracle, Alpha · Short non-key
|
CHARACTER VARYING(3)
Observed · APPX 6.2.1: PostgreSQL, Alpha · Short non-key
|
VARCHAR(3)
|
VARCHAR(3)
Observed · APPX 6.2.1: MariaDB, Alpha · Short non-key
|
VARCHAR(3)
Observed · APPX 6.2.1: SQL Server, Alpha · Short non-key
|
VARCHAR(3)
|
Alpha · Longer non-key
30 characters, single-byte storage.
CUSTOMER NAME = Northwind Traders
|
VARCHAR2(30 CHAR)
5.5.4: VARCHAR2(30)
Observed · APPX 6.2.1: Oracle, Alpha · Longer non-key
|
CHARACTER VARYING(30)
Observed · APPX 6.2.1: PostgreSQL, Alpha · Longer non-key
|
VARCHAR(30)
|
VARCHAR(30)
Observed · APPX 6.2.1: MariaDB, Alpha · Longer non-key
|
VARCHAR(30)
Observed · APPX 6.2.1: SQL Server, Alpha · Longer non-key
|
VARCHAR(30)
|
Alpha · 14-character multibyte key
APPX 6.2.2b2 Unicode storage; 56 bytes in the APPX record.
COUNTRY NAME = 日本
The APPX record size drives the ODBC length in this example. Oracle uses the character count.
|
CHAR(14 CHAR)
AL32UTF8 database. A database without Unicode in its main character set can get NCHAR(14).
Observed · APPX 6.2.1: Oracle, Alpha · 14-character multibyte key
|
CHARACTER VARYING(56)
Use a Unicode-capable database and connection for this value. The WIN1252 setup in the guide cannot store it.
Observed · APPX 6.2.1: PostgreSQL, Alpha · 14-character multibyte key
|
VARCHAR(56) CHARACTER SET ucs2
This MySQL engine path adds ucs2, which cannot store characters outside the Basic Multilingual Plane.
|
VARCHAR(56)
MariaDB Connector/ODBC and an utf8mb4 database.
Observed · APPX 6.2.1: MariaDB, Alpha · 14-character multibyte key
|
NVARCHAR(56)
56 UTF-16 code units; SQL Server reports 112 bytes.
Observed · APPX 6.2.1: SQL Server, Alpha · 14-character multibyte key
|
VARCHAR(56)
If advertised by CLI. Verify Unicode round trips separately.
|
Format · Formatted customer code
8 characters with edit mask @@######.
CUSTOMER CODE = AB001234
SQL stores the value as a string. APPX applies the edit mask.
|
VARCHAR2(8 CHAR)
5.5.4: VARCHAR2(8)
Observed · APPX 6.2.1: Oracle, Format · Formatted customer code
|
CHARACTER VARYING(8)
Observed · APPX 6.2.1: PostgreSQL, Format · Formatted customer code
|
VARCHAR(8)
|
VARCHAR(8)
Observed · APPX 6.2.1: MariaDB, Format · Formatted customer code
|
VARCHAR(8)
Observed · APPX 6.2.1: SQL Server, Format · Formatted customer code
|
VARCHAR(8)
|
Text · A short note
120 characters, single-byte storage.
ORDER NOTE = Leave at receiving
A Text field can be a VARCHAR. Setting up a multi-line editor does not make it a SQL TEXT column.
|
VARCHAR2(120 CHAR)
5.5.4: VARCHAR2(120)
Observed · APPX 6.2.1: Oracle, Text · A short note
|
CHARACTER VARYING(120)
Observed · APPX 6.2.1: PostgreSQL, Text · A short note
|
VARCHAR(120)
|
VARCHAR(120)
Observed · APPX 6.2.1: MariaDB, Text · A short note
|
VARCHAR(120)
Observed · APPX 6.2.1: SQL Server, Text · A short note
|
VARCHAR(120)
|
Text · A long note
5,000 characters, single-byte storage; non-key.
SERVICE NOTES = A multi-paragraph repair history
The PostgreSQL and MySQL drivers shown advertise 255 for their ordinary VARCHAR choice. APPX then looks for a long string type. Row and index limits still apply.
|
CLOB
Observed · APPX 6.2.1: Oracle, Text · A long note
|
TEXT
Observed · APPX 6.2.1: PostgreSQL, Text · A long note
|
MEDIUMTEXT
The driver offers LONG VARCHAR as the long string type.
|
VARCHAR(5000)
Observed · APPX 6.2.1: MariaDB, Text · A long note
|
VARCHAR(5000)
Observed · APPX 6.2.1: SQL Server, Text · A long note
|
VARCHAR(5000)
|
Numbers, tokens, and flags
For fixed decimal fields, use total digits and decimal places. For binary fields and numeric tokens, the size of the compiled value determines the SQL choice.
Dates and times
Use the APPX Store From and Store Through parts. The display format can include punctuation or names; it does not determine the SQL type.
| APPX field and example | Oracle 19c · OCI · FMS 5 | PostgreSQL 14.1 · psqlODBC ANSI 18.00.0001 | MySQL 8.4 · MySQL Connector/ODBC 8.4.0 | MariaDB 10.11 · Connector/ODBC 3.1.22 | SQL Server 2022 · Microsoft ODBC Driver 18 | IBM Db2 11.5 CLI · native FMS 8 |
|---|---|---|---|---|---|---|
Date · Calendar date
CC through DD; non-key.
ORDER DATE = 2026-11-11
|
VARCHAR2(8 CHAR)
Length semantics follow the Oracle session.
Observed · APPX 6.2.1: Oracle, Date · Calendar date
|
DATE
Observed · APPX 6.2.1: PostgreSQL, Date · Calendar date
|
DATE
|
DATE
Observed · APPX 6.2.1: MariaDB, Date · Calendar date
|
DATE
Observed · APPX 6.2.1: SQL Server, Date · Calendar date
|
DATE
|
Date · Time of day
hh through ss; non-key.
OPEN TIME = 08:15:30
|
VARCHAR2(6 CHAR)
Length semantics follow the Oracle session.
Observed · APPX 6.2.1: Oracle, Date · Time of day
|
TIME WITHOUT TIME ZONE
Observed · APPX 6.2.1: PostgreSQL, Date · Time of day
|
TIME
|
TIME
Observed · APPX 6.2.1: MariaDB, Date · Time of day
|
VARCHAR(6)
The Microsoft driver reports its own TIME type code.
Observed · APPX 6.2.1: SQL Server, Date · Time of day
|
TIME
|
Date · Year and month
CC through MM; non-key.
BILLING MONTH = 2026-11
|
VARCHAR2(6 CHAR)
Length semantics follow the Oracle session.
Observed · APPX 6.2.1: Oracle, Date · Year and month
|
CHARACTER VARYING(6)
Observed · APPX 6.2.1: PostgreSQL, Date · Year and month
|
VARCHAR(6)
|
VARCHAR(6)
Observed · APPX 6.2.1: MariaDB, Date · Year and month
|
VARCHAR(6)
Observed · APPX 6.2.1: SQL Server, Date · Year and month
|
VARCHAR(6)
|
Date · Date and time through minutes
CC through mm; non-key.
PICKUP AT = 2026-11-11 08:15
|
VARCHAR2(12 CHAR)
Length semantics follow the Oracle session.
Observed · APPX 6.2.1: Oracle, Date · Date and time through minutes
|
CHARACTER VARYING(12)
Observed · APPX 6.2.1: PostgreSQL, Date · Date and time through minutes
|
VARCHAR(12)
|
VARCHAR(12)
Observed · APPX 6.2.1: MariaDB, Date · Date and time through minutes
|
SMALLDATETIME
Observed · APPX 6.2.1: SQL Server, Date · Date and time through minutes
|
VARCHAR(12)
|
Date · Date and time through seconds
CC through ss; non-key.
POSTED AT = 2026-11-11 08:15:30
|
DATE
Observed · APPX 6.2.1: Oracle, Date · Date and time through seconds
|
TIMESTAMP WITH TIME ZONE
This psqlODBC catalog advertises timestamptz. The APPX Date field has no time-zone part.
Observed · APPX 6.2.1: PostgreSQL, Date · Date and time through seconds
|
DATETIME
|
DATETIME
Observed · APPX 6.2.1: MariaDB, Date · Date and time through seconds
|
VARCHAR(14)
The SQL Server default is selected by the exact APPX date parts; this case falls through to a string.
Observed · APPX 6.2.1: SQL Server, Date · Date and time through seconds
|
TIMESTAMP
|
Date · Date and time through hundredths
CC through th; non-key.
EVENT AT = 2026-11-11 08:15:30.25
|
VARCHAR2(16 CHAR)
Length semantics follow the Oracle session.
Observed · APPX 6.2.1: Oracle, Date · Date and time through hundredths
|
TIMESTAMP WITH TIME ZONE
This psqlODBC catalog advertises timestamptz. The APPX Date field has no time-zone part.
Observed · APPX 6.2.1: PostgreSQL, Date · Date and time through hundredths
|
VARCHAR(16)
The 8.4.0 catalog reports no fractional scale for its timestamp choices.
|
VARCHAR(16)
Connector/ODBC 3.1.22 reports no fractional scale for this choice.
Observed · APPX 6.2.1: MariaDB, Date · Date and time through hundredths
|
DATETIME
Observed · APPX 6.2.1: SQL Server, Date · Date and time through hundredths
|
TIMESTAMP
|
Date · Date and time in a key
CC through ss; part of a key.
POSTED AT = 2026-11-11 08:15:30
|
VARCHAR2(14 CHAR)
Default. The Oracle APPX_RDBMS_NDK setting can select DATE.
Observed · APPX 6.2.1: Oracle, Date · Date and time in a key
|
CHARACTER VARYING(14)
Observed · APPX 6.2.1: PostgreSQL, Date · Date and time in a key
|
VARCHAR(14)
|
VARCHAR(14)
Observed · APPX 6.2.1: MariaDB, Date · Date and time in a key
|
VARCHAR(14)
Observed · APPX 6.2.1: SQL Server, Date · Date and time in a key
|
VARCHAR(14)
|
Date · Calendar date in a key
CC through DD; part of a key.
ORDER DATE = 2026-11-11
For other partial dates or character fallbacks, allow two positions for each stored part: CC, YY, MM, DD, hh, mm, ss, and th.
|
VARCHAR2(8 CHAR)
Length semantics follow the Oracle session.
Observed · APPX 6.2.1: Oracle, Date · Calendar date in a key
|
CHARACTER VARYING(8)
Observed · APPX 6.2.1: PostgreSQL, Date · Calendar date in a key
|
VARCHAR(8)
|
VARCHAR(8)
Observed · APPX 6.2.1: MariaDB, Date · Calendar date in a key
|
VARCHAR(8)
Observed · APPX 6.2.1: SQL Server, Date · Calendar date in a key
|
VARCHAR(8)
|
Audit fields and domains
System fields use the same date and character rules. A field based on a domain stores a value with the domain’s effective definition.
Fields that do not add a column
These four dictionary choices give APPX another way to refer to fields already in the file.
| APPX field and example | Result in every database |
|---|---|
Synonym · Another name for a field
CLIENT CODE refers to CUSTOMER CODE.
CLIENT CODE = ACME001
|
No separate column Use the CUSTOMER CODE column. Observed: Oracle, PostgreSQL, MariaDB, SQL Server. |
Substring · Part of another field
POSTAL PREFIX refers to the first 3 positions of POSTAL CODE.
POSTAL PREFIX = K1A
|
No separate column Use the POSTAL CODE column. Observed: Oracle, PostgreSQL, MariaDB, SQL Server. |
Group Header · A name for a group of fields
ADDRESS groups STREET, CITY, and POSTAL CODE.
ADDRESS = 10 MAIN ST / OTTAWA / K1A 0B1
|
No separate column The stored member fields get individual columns. Observed: Oracle, PostgreSQL, MariaDB, SQL Server. |
Group Trailer · The end of a group
END ADDRESS marks the end of the ADDRESS group.
END ADDRESS
|
No separate column The trailer has no stored value. Observed: Oracle, PostgreSQL, MariaDB, SQL Server. |
Field type test app
The RST application covers all 37 examples on this page. Using APPX 6.2.1 (64-bit) on Rocky Linux 9.8, Create Files produced six tables in each database, 24 in all. Four examples share storage with other fields and did not get their own columns. The separate Floating Decimal file failed on all four. The other column types agreed with the source-based examples; the catalog results below include details such as Oracle’s CHAR length semantics. This test checked what APPX creates; it did not write or read back example values.
Oracle 19c (19.3) · OCI
Native Oracle file system (FMS 5) with Oracle Instant Client 19.28 Basic Light and an AL32UTF8 database. Oracle reported CHAR length semantics for the fallback character columns; the source-based types leave that qualifier to the session.
PostgreSQL 14.1 · psqlODBC ANSI 18.00.0001
ODBC file system (FMS 6) with a 64-bit ANSI driver, WIN1252 database and connection encoding, and Bools as Char disabled. The catalog reports CHARACTER VARYING for VARCHAR. The multibyte example checks column width only.
MariaDB 10.11.18 · Connector/ODBC 3.1.22
ODBC file system (FMS 6) with the 64-bit MariaDB driver, a utf8mb4 database and an APPX proxy lock. This driver combination produced VARCHAR for the fixed decimal, binary and token examples.
SQL Server 2022 (16.0.4305.5) · Microsoft ODBC Driver 18
ODBC file system (FMS 6) with Microsoft ODBC Driver 18.7.0001 (64-bit) and a database with a UTF-8 collation. The full date through seconds came out as VARCHAR(14); the full date through hundredths came out as DATETIME. The tables were created in the active APPX session. A fresh session on this lab stopped at the ODB registration check before reaching SQL Server.
MySQL and IBM Db2 were not installed in this lab. Their examples are still based on the engine source and driver catalogs.
How to read the mappings
Which engine and driver?
Observed types come from the database catalog after APPX created the test files. The other types follow the APPX 5.5.4 and 6.2.2b2 source and the driver catalogs. When a catalog result differs from the source-based example, both are shown. Examples use single-byte fields unless marked multibyte. Field Overrides were left blank.
Why did MariaDB get VARCHAR for a number?
Connector/ODBC 3.1.22 advertises the numeric, integer, and floating choices as auto-increment capable. These APPX engines skip those choices when building the table. With that catalog, APPX falls back to character columns. VARCHAR(2) was observed for the unsigned 2-digit field. Other drivers may return a different catalog.
Oracle character lengths
The 6.2.2b2 engine specifies character length for CHAR and VARCHAR2 in its character-field mapping. The 5.5.4 declarations do not specify BYTE or CHAR; Oracle uses the session’s length semantics. The date-to-string path uses the session’s length semantics in both engines. The examples assume the usual Oracle defaults for key fields and no Force VARCHAR override.
Existing tables and other drivers
For an existing table, inspect its columns and APPX Field Overrides. This page shows what APPX asks the server to create. It does not prescribe what you must use in a hand-built schema. The Db2 column covers the native FMS 8 path where available. Other ODBC drivers supply their own type catalog; the legacy native Sybase interface is outside this comparison.
Fields with occurrences
When a stored field occurs more than once, APPX creates a column for each occurrence using the same type rule. Work fields live in APPX and do not create database columns.
References: APPX field type definitions · APPX field-to-column overrides · ODBC driver type catalogs · MySQL Connector/ODBC 8.4.0 type catalog · Db2 CLI data types