Adaptive Server Enterprise data mapping

Mapping to Adaptive Server Enterprise consolidated data types

The following table identifies how SQL Anywhere and UltraLite remote data types are mapped to Adaptive Server Enterprise consolidated data types. For example, a column of type FLOAT on the remote database should be type REAL on the consolidated database.

Maximum column length (MCL) depends on the Adaptive Server Enterprise page size. If the page size is 2K the MCL is 1954; if the page size is 4K the MCL is 4002. For information about MCL, see the Adaptive Server Enterprise documentation.

SQL Anywhere or UltraLite data type	Adaptive Server Enterprise data type	Notes
BIGINT	NUMERIC(20)¹ or BIGINT²
BIT	BIT
BINARY(n=<MCL)	BINARY(n)
BINARY(n>MCL)	IMAGE
CHAR(n=<MCL)	VARCHAR(n)
CHAR(n>MCL)	TEXT	On download, ensure the values are not too long.
DATE	DATE³ or DATETIME⁴	For Adaptive Server Enterprise DATETIME, the year must be in the range 1753-9999. For SQL Anywhere and UltraLite, the time value must in the format 00:00:00.
DATETIME	DATETIME	The Adaptive Server Enterprise DATETIME values are accurate to 1/300 second. The last digit of the fractional second is always 0, 3, or 6. Other digits are rounded to one of these three digits, so 0 and 1 round to 0; 2, 3, and 4 round to 3; 5, 6, 7, and 8 round to 6; and 9 rounds to 10. For download, SQL Anywhere keeps the original values from Adaptive Server Enterprise, but for upload, the values may not be exactly the original values. If DATETIME is used for a primary key, conflict resolution may fail. To successfully synchronize DATETIME, you should round the fractional second to 10 milliseconds. Also, the year must be in the range 1753-9999.
DECIMAL(p<39, s)	DECIMAL(p,s)	The precision of the Adaptive Server Enterprise NUMERIC can be from 1 to 38 digits (p<39).
DECIMAL(p>=39,s)		There is no corresponding data type in Adaptive Server Enterprise.
DOUBLE	DOUBLE PRECISION
FLOAT(p)	FLOAT(p)
IMAGE	IMAGE
INTEGER	INTEGER
LONG BINARY	IMAGE
LONG NVARCHAR	UNITEXT
LONG VARBIT	TEXT
LONG VARCHAR	TEXT
MONEY	MONEY
NCHAR(c=<MCL)	UNIVARCHAR(c/2)
NCHAR(c>MCL)	UNITEXT	On download, ensure the values are not too long.
NTEXT	UNITEXT
NUMERIC(p<39,s)	NUMERIC(p,s)	The precision of the Adaptive Server Enterprise decimal can be from 1 to 38 digits (p<39).
NUMERIC(p>=39,s)
NVARCHAR(c=<MCL)	UNIVARCHAR(c/2)
NVARCHAR(c>MCL)	UNITEXT	On download, ensure the values are not too long.
REAL	REAL
SMALLDATETIME	DATETIME⁴	SQL Anywhere and UltraLite SMALLDATETIME is implemented as TIMESTAMP. The Adaptive Server Enterprise DATETIME is accurate to the minute. 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute. SQL Anywhere or UltraLite SMALLDATETIME is accurate to the microsecond. To successfully synchronize, SQL Anywhere or UltraLite SMALLDATETIME must be rounded to the minute. Also, the year must be in the range 1753-9999.
SMALLINT	SMALLINT
SMALLMONEY	SMALLMONEY
TEXT	TEXT
TIME	TIME³ or DATETIME⁴	The Adaptive Server Enterprise TIME values are accurate to 1/300 second. The last digit of the fractional second is always 0, 3, or 6. Other digits are rounded to one of these three digits, so 0 and 1 round to 0; 2, 3, and 4 round to 3; 5, 6, 7, and 8 round to 6; and 9 rounds to 10. For download, SQL Anywhere keeps the original values from Adaptive Server Enterprise, but for upload, the values may not be exactly the original values. If TIME is used for a primary key, conflict resolution may fail. To successfully synchronize TIME, you should round the fractional second to 10 milliseconds.
TIMESTAMP	DATETIME	The Adaptive Server Enterprise DATETIME values are accurate to 1/300 second. The last digit of the fractional second is always 0, 3, or 6. Other digits are rounded to one of these three digits, so, 0 and 1 round to 0; 2, 3, and 4 round to 3; 5, 6, 7, and 8 round to 6; and 9 rounds to 10. For download, SQL Anywhere keeps the original values from Adaptive Server Enterprise, but for upload, the values may not be exactly the original values. If DATETIME is used for a primary key, conflict resolution may fail. To successfully synchronize DATETIME, you should round the fractional second to 10 milliseconds. Also, the year must be in the range 1753-9999.
TIMESTAMP WITH TIME ZONE	VARCHAR(34)	There is no equivalent data type in Adaptive Server Enterprise. Therefore, a TIMESTAMP WITH TIME ZONE column should be mapped to a VARCHAR(34) column. In upload, the MobiLink server first converts the data to a string using the format YYYY-MM-DD HH:NN:SS.SSSSSS [+\|-]HH:NN and then applies it to the consolidated database. In download, it converts the data from string to TIMESTAMP WITH TIME ZONE. Ensure the data in the consolidated database follows this format or the download will fail.
TINYINT	TINYINT
UNIQUEIDENTIFIER	CHAR(36)
UNIQUEIDENTIFIERSTR	CHAR(36)	Do not use UNIQUEIDENTIFIERSTR. Use UNIQUEIDENTIFIER instead.
UNSIGNED BIGINT	NUMERIC(20)¹ or UNSIGNED BIGINT²
UNSIGNED INTEGER	UNSIGNED INT
UNSIGNED SMALLINT	UNSIGNED SMALLINT
UNSIGNED TINYINT	TINYINT
VARBINARY(n=<MCL)	VARBINARY
VARBINARY(n>MCL)	IMAGE
VARBIT(n=<MCL)	VARCHAR(n)
VARBIT(n>MCL)	TEXT
VARCHAR(n=<MCL)	VARCHAR(n)
VARCHAR(n>MCL)	TEXT
XML	TEXT

¹ Only applies to Adaptive Server Enterprise before version 15.0.

² Only applies to Adaptive Server Enterprise version 15.0 or later.

³ Only applies to Adaptive Server Enterprise version 12.5.1 or later.

⁴ Only applies to Adaptive Server Enterprise before version 12.5.1.

Mapping to SQL Anywhere or UltraLite remote data types

The following table identifies how Adaptive Server Enterprise consolidated data types are mapped to SQL Anywhere and UltraLite remote data types. For example, a column of type DOUBLE PRECISION on the consolidated database should be type DOUBLE on the remote database.

Adaptive Server Enterprise data type	SQL Anywhere or UltraLite data type	Notes
BIGINT¹	BIGINT
BINARY(n)	BINARY(n)
BIT	BIT
CHAR(n)	VARCHAR(n)	There is no equivalence between SQL Anywhere CHAR/NCHAR and Adaptive Server Enterprise CHAR/NCHAR. SQL Anywhere CHAR/NCHAR is equivalent to VARCHAR/NVARCHAR. You should not use CHAR/NCHAR in a consolidated database column that is synchronized. If you must use non-SQL Anywhere CHAR/NCHAR, run the MobiLink server with the -b option.
DATE	DATE	For SQL Anywhere and UltraLite, the time value must in the format 00:00:00.
DATETIME	DATETIME	The Adaptive Server Enterprise DATETIME values are accurate to 1/300 second. The last digit of the fractional second is always one of 0, 3, or 6. Other digit numbers are rounded to one of these three digits, so 0 and 1 round to 0; 2, 3, and 4 round to 3; 5, 6, 7, and 8 round to 6; and 9 rounds to 10. For download, SQL Anywhere keeps the original values from Adaptive Server Enterprise, but for upload, the values may not be exactly the original values. Conflict resolution may fail. To successfully synchronize DATETIME, you should round the fractional second to 10 milliseconds. Also, the year must be in the range 1753-9999.
DECIMAL(p,s)	DECIMAL(p,s)
DOUBLE PRECISION	DOUBLE
FLOAT(p)	FLOAT(p)
IMAGE	LONG BINARY
INT	INT
MONEY	MONEY
NCHAR(n)	VARCHAR(n)	The Adaptive Server Enterprise NCHAR and NVARCHAR store multibyte national character strings, they are different from SQL Anywhere NCHAR and NVARCHAR. In a multibyte environment, use SQL Anywhere or UltraLite VARCHAR.
NUMERIC(p,s)	NUMERIC(p,s)
NVARCHAR(n)	VARCHAR(n)	The Adaptive Server Enterprise NCHAR and NVARCHAR store multibyte national character strings, they are different from SQL Anywhere NCHAR and NVARCHAR. In a multibyte environment, use SQL Anywhere or UltraLite VARCHAR.
REAL	REAL
SMALLDATETIME	SMALLDATETIME	SQL Anywhere and UltraLite SMALLDATETIME is implemented as TIMESTAMP. The Adaptive Server Enterprise SMALLDATETIME is accurate to the minute. 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute. SQL Anywhere or UltraLite SMALLDATETIME is accurate to the microsecond. To successfully synchronize, SQL Anywhere or UltraLite SMALLDATETIME must be rounded to the minute. Also, the year must be in the range 1900-2078.
SMALLINT	SMALLINT
SMALLMONEY	SMALLMONEY
TEXT	LONG VARCHAR
TIME	TIME	The Adaptive Server Enterprise TIME values are accurate to 1/300 second. The last digit of the fractional second is always one of 0, 3, or 6. Other digit numbers are rounded to one of these three digits, so 0 and 1 round to 0; 2, 3, and 4 round to 3; 5, 6, 7, and 8 round to 6; and 9 rounds to 10. For download, SQL Anywhere keeps the original values from Adaptive Server Enterprise, but for upload, the values may not be exactly the original values. Conflict resolution may fail. To successfully synchronize TIME, it is recommended that you round the fractional second to 10 milliseconds.
TIMESTAMP	VARBINARY(8)	Within Adaptive Server Enterprise, TIMESTAMP is a binary counter that gets incremented with every change to a row. So, each table can only contain one TIMESTAMP column and it does not make sense to synchronize it. If it must be a in synchronization, map it to a VARBINARY(8) data type in SQL Anywhere or UltraLite. This TIMESTAMP column cannot be explicitly inserted or updated, because it is maintained by the server. Keep this in mind when you are implementing upload scripts for tables that contain such columns.
TINYINT	TINYINT
UNSIGNED BIGINT¹	UNSIGNED BIGINT
UNSIGNED INT¹	UNSIGNED INT
UNSIGNED SMALLINT¹	UNSIGNED SMALLINT
VARBINARY(n)	VARBINARY(n)
VARCHAR(n)	VARCHAR(n)
UNICHAR(n)	NVARCHAR(n)	Not available in UltraLite.
UNITEXT¹	LONG NVARCHAR	Not available in UltraLite.
UNIVARCHAR(n)	NVARCHAR(n)	Not available in UltraLite.

¹ Only applies to Adaptive Server Enterprise before version 15.0.

Discuss this page in DocCommentXchange.