JADE to SQL Server Type Mapping

Product Information > JADE Synchronized Database Service (SDS) Administration Guide > Chapter 2 - Relational Population Service (RPS) Support > JADE to SQL Server Type Mapping

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 2000	SQL Server 2005	SQL Server 2008 or Later
Binary (unbounded)	IMAGE*	IMAGE	IMAGE
		VARBINARY(max)*	Same as SQL Server 2005
Binary (length)	BINARY(length)	Same as SQL Server 2000	Same as SQL Server 2000
	VARBINARY(length)*
	IMAGE
Boolean	BIT*	Same as SQL Server 2000	Same as SQL Server 2000
	CHAR(1) or NCHAR(1)
Byte	TINYINT*	Same as SQL Server 2000	Same as SQL Server 2000
Character	CHAR(1) or NCHAR(1)*	Same as SQL Server 2000	Same as SQL Server 2000
	TINYINT
Date	DATETIME*	Same as SQL Server 2000	DATE*
Decimal	DECIMAL(prec, digits)*	Same as SQL Server 2000	Same as SQL Server 2000
	MONEY
Integer	INTEGER*	Same as SQL Server 2000	Same as SQL Server 2000
Integer64	BIGINT*	Same as SQL Server 2000	Same as SQL Server 2000
JadeBytes	IMAGE*	Same as SQL Server 2000	Same as SQL Server 2000
OID	CHAR(16) or NCHAR(16)*	Same as SQL Server 2000	Same as SQL Server 2000
	INTEGER/INTEGER
	INTEGER/BIGINTEGER
Real	FLOAT*	Same as SQL Server 2000	Same as SQL Server 2000
String (unbounded)	TEXT or NTEXT*	TEXT or NTEXT	Same as SQL Server 2005
		VARCHAR(max)* or NVARCHAR(max)	Same as SQL Server 2005
String (length)	VARCHAR(length) or NVARCHAR(length)*	Same as SQL Server 2000	Same as SQL Server 2000
	CHAR(length) or NCHAR(length)
	TEXT or NTEXT
StringUtf8 (unbounded)	NTEXT*	NTEXT	Same as SQL Server 2005
		NVARCHAR(max)*	Same as SQL Server 2005
StringUtf8 (length)	NVARCHAR(length)*	Same as SQL Server 2000	Same as SQL Server 2000
	NCHAR(length)
	NTEXT
Time	DATETIME*	Same as SQL Server 2000	TIME*
	INTEGER		INTEGER
TimeStamp	DATETIME*	Same as SQL Server 2000	DATETIME2*
	INTEGER		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 JADE 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 "65534.4294967295" (<classId>.<instId>), by default
- An INTEGER/INTEGER data type that contains the _clsno (class number) and _instId (of type int)
- An INTEGER/BIGINTEGER 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'.