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)1 or BIGINT2 |
|
|
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 |
DATE3 or DATETIME4 |
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 |
DATETIME4 |
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 |
TIME3 or DATETIME4 |
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. |
|
TINYINT |
TINYINT |
|
|
UNIQUEIDENTIFIER |
CHAR(36) |
|
|
UNIQUEIDENTIFIERSTR |
CHAR(36) |
Do not use UNIQUEIDENTIFIERSTR. Use UNIQUEIDENTIFIER instead. |
|
UNSIGNED BIGINT |
NUMERIC(20)1 or UNSIGNED BIGINT2 |
|
|
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 |
1 Only applies to Adaptive Server Enterprise before version 15.0.
2 Only applies to Adaptive Server Enterprise version 15.0 or later.
3 Only applies to Adaptive Server Enterprise version 12.5.1 or later.
4 Only applies to Adaptive Server Enterprise before version 12.5.1.
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 |
|---|---|---|
|
BIGINT1 |
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 BIGINT1 |
UNSIGNED BIGINT |
|
|
UNSIGNED INT1 |
UNSIGNED INT |
|
|
UNSIGNED SMALLINT1 |
UNSIGNED SMALLINT |
|
|
VARBINARY(n) |
VARBINARY(n) |
|
|
VARCHAR(n) |
VARCHAR(n) |
|
|
UNICHAR(n) |
NVARCHAR(n) |
Not available in UltraLite. |
|
UNITEXT1 |
LONG NVARCHAR |
Not available in UltraLite. |
|
UNIVARCHAR(n) |
NVARCHAR(n) |
Not available in UltraLite. |
1 Only applies to Adaptive Server Enterprise before version 15.0.
| Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |