Jade to SQL Server Type Mapping

The following table defines the available mappings of Jade types to SQL Server Data types.

Jade Type	SQL Server
Binary (unbounded)	IMAGE
	VARBINARY(max)*
Binary (length)	BINARY(length)
	VARBINARY(length)*
	IMAGE
Boolean	BIT*
	CHAR(1) or NCHAR(1)
Byte	TINYINT*
Character	CHAR(1) or NCHAR(1)*
	TINYINT
Date	DATE*
Decimal	DECIMAL(prec, digits)*
	MONEY
Integer	INTEGER*
Integer64	BIGINT*
JadeBytes	IMAGE*
OID	CHAR(28) or NCHAR(28)*
	INTEGER/INTEGER
	INTEGER/BIGINT
Real	FLOAT*
String (unbounded)	TEXT or NTEXT
	VARCHAR(max)* or NVARCHAR(max)
String (length)	VARCHAR(length) or NVARCHAR(length)*
	CHAR(length) or NCHAR(length)
	TEXT or NTEXT
StringUtf8 (unbounded)	NTEXT
	NVARCHAR(max)*
StringUtf8 (length)	NVARCHAR(length)*
	NCHAR(length)
	NTEXT
Time	TIME*
	INTEGER
TimeStamp	DATETIME2*
	INTEGER

In this table:

• Default property values are indicated by an asterisk (*).

• Column-mapping methods that return a:

  • String primitive type have a default mapping type of unbounded string.

  • Binary primitive type have a default mapping type of unbounded binary.

  • Binary (length) primitive type and that are mapped to BINARY[length], trailing zeros are inserted for length columns. If they are mapped to the default VARBINARY[length], only the valid data is output.

  • Decimal primitive type have a default mapping type of Decimal[38,19].

  These column-mapping method return types accommodate the largest possible values that could be returned.

  To change the mapping for columns to a more appropriate size for the data, use the Relational Population Service wizard. For details, see "Maintaining RPS Column Mappings", in Chapter 15 of the Development Environment User's Guide.)

• When mapping the String or StringUtf8 primitive type:

  • ANSI padding is set to off in the SQL Server connection.

  • If the length is less than 255 characters, the map reflects the actual string length; for example, VARCHAR(30) or NVARCHAR(30).

  • If the length is in the range 255 through 8000, the default mapping is to VARCHAR(max), NVARCHAR(max), TEXT, or NTEXT.

  • Unbounded strings or strings with a length greater than 8,000 map only to VARCHAR(max), NVARCHAR(max), TEXT, or NTEXT.

  • In SQL Server, the limit for a row in any table is 8060. You can create tables with a total column length greater than 8060; however an error is raised if the actual data being put into a row exceeds 8060.

    When mapping longer strings to VARCHAR or NVARCHAR, take care not to exceed this limit.

• When mapping a Decimal primitive type to an SQL Server MONEY data type, the Jade decimal attribute must have no more than 14 integer digits and no more than 3 fractional digits.

• The NVARCHAR, NCHAR, and NTEXT SQL Server data types are used in Unicode Jade systems. StringUtf8 always maps to NVARCHAR, NCHAR, or NTEXT in ANSI or Unicode Jade systems.

• Jade OID types are stored in one of:

  • A fixed-string format "0065534.00000000004294967295" (<classId>.<instId>), by default

    This is the recommended oid mapping option, as any oid can be represented in this way.

  • An Int/Int data type that contains the _clsno (class number) and _instId (of type int)

  • An Int/BigInt data type that contains the _clsno (type int) and _instid (type bigint)

• Mapping a Jade Boolean primitive type to a character maps to values 'T' and 'F'.