Built-in Datatypes

Built-in types are provided out of the box in the platform.

Built-in Datatypes

Built-in types include:

  • Numeric Types:

    • ByteInteger: 8 bytes signed. Range [-128 to 127]

    • ShortInteger: 16 bytes signed. Range [-32,768 to -32,767]

    • Integer: 32 bytes signed. Range [-232 to 232-1]

    • LongInteger: 64 bytes signed. Range [-264 to 264-1]

    • Decimal: Number (precision and scale).

      • Precision range [1 to 38].

      • Scale range [-84 to 127].

        Only decimal numbers containing up to 14 digits can be entered on authoring forms. This constraint does not apply to data published using the REST or SQL APIs.

  • Text Types:

    • String: Length smaller than 4,000 characters. Note that strings translate in the database to VARCHAR2 (Oracle), VARCHAR (PostgreSQL) or NVARCHAR (SQL Server).

      To increase the maximum string length from 4,000 to 32,767 bytes, platform administrators can add com.semarchy.mdm.supportExtendedMaxStringSize to xDM system properties.

      Once set to true, the com.semarchy.mdm.supportExtendedMaxStringSize property cannot be reverted to false.

      Oracle database administrators should additionally set the MAX_STRING_SIZE parameter to EXTENDED in the Oracle database settings.

      Once set to EXTENDED, the MAX_STRING_SIZE parameter cannot be set back to STANDARD.
    • LongText: No size limit. Translated in the database to a CLOB (Oracle), TEXT (PostgreSQL), or NVARCHAR(MAX) (SQL Server).

  • Date Types:

    • Date: Date with no time or timezone. It translates to a DATE with PostgreSQL and SQL Server, and to a Timestamp with Oracle. For Oracle, the time portion is automatically truncated by the certification process for date attributes.

    • Timestamp: Date and Time (to fractional digits of a Second). This translates to Timestamp (Oracle and PostgreSQL) and Datetime2 (SQL Server). See the note below for more information about dates, timestamps and time zones.

  • Binary Types:

    • Binary: Store any type of binary content (image, document, movie, etc.) with no size limit. Translated in the database to a BLOB (Oracle), BYTEA (PostgreSQL) or VARBINARY (SQL Server)

  • Misc. Types:

    • UUID: 16 bytes Global Unique ID. This translates to RAW (Oracle), UUID (PostgreSQL) and UNIQUEIDENTIFIER (SQL Server)

    • Boolean: With Oracle, a boolean translates to a CHAR(1) containing either 1 (true) or 0 (false). PostgreSQL and SQL Server respectively use BOOLEAN and BIT.

Dates, Timestamps Values and Time Zones Conversion

Date values do not store the time, and are not subject to time zones conversions.
Timestamp values contain the date and time, and are therefore subject to time zones conversions:

  • Timestamps are stored in the repository and data locations in the time zone of the application server.

  • Timestamps and dates used in applications:

    • Users view or author timestamps in the timezone defined in their user profile. The values are automatically converted from/to the application server timezone when read/written to the database.

    • Users view and author timestamps and date in the format defined in their user profile by default, or in the format defined by the application designer in the form or collection.

    • Import or export is an exception. Users import or export timestamps in the application server timezone, independently from their profile timezone. This allows exporting timestamps in one timezone and reimporting them in a different timezone without altering the values.

  • Timestamps and dates in integration:

    • The REST API uses the ECMAScript standard format (YYYY-MM-DDTHH:mm:ss.sssZ) for timestamps. This format includes an explicit timezone part and conversion is performed automatically.

    • The REST API uses the date without a time-zone format in the ISO-8601 calendar system (YYYY-MM-DD) for dates. Example: 2007-12-03.

    • Integration specialists using SQL to consume timestamp data from or publish timestamp data to the MDM hub should be aware that conversion may happen depending on their driver configuration. It is recommended to have the integration component located in the same timezone as the application server to avoid such conversion.