Component Integration Services includes changes to the connect to...diconnect command. When a create existing table command is received, it is interpreted as a request to import metadata from the remote or external location of the object for updating system catalogs. Importing this metadata is performed by means of three RPCs sent to the remote server with which the object has been associated:
• sp_tables – verifies that the remote object actually exists.
• sp_columns – obtains column attributes of the remote object for comparison with those defined in create existing table.
• sp_statistics – obtains index information to update the local system table, sysindexes.
See also create existing table in the Reference Manual: Procedures
Server Class ASEnterprise
This table describes the allowable datatypes that can be used when mapping remote SAP ASE columns to local proxy table columns:
Table 15. SAP ASE datatype conversions for create existing table Remote SAP
ASE Datatype
Allowable SAP ASE Datatypes
binary(n) image, binary(n), and varbinary(n); if not image, the length must match
bit bit, decimal, float, int, money, numeric, real,
smallint, smallmoney, and tinyint
char(n) text, nchar( n), nvarchar( n),char( n), varchar(n),
unichar, univarchar; if not text, the length must match
datetime datetime, smalldatetime, char, and varchar
decimal(p, s) bit, decimal, float, int, money, numeric, real, smallint,
smallmoney, and tinyint
float bit, decimal, float, int, money, numeric, real, smallint,
smallmoney, and tinyint
image image
int bit, decimal, float, int, money, numeric, real, smallint,
Remote SAP ASE Datatype
Allowable SAP ASE Datatypes
money bit, decimal, float, int, money, numeric, real, smallint,
smallmoney, and tinyint
nchar(n) text, nchar( n),nvarchar( n), char( n), varchar( n); if not
text, the length must match
numeric(p, s) bit, decimal, float, int, money, numeric, real, smallint,
smallmoney, and tinyint
nvarchar(n) text, nchar( n), nvarchar( n), char( n), varchar(n), uni-
char, univarchar; if not text, the length must match
real bit, decimal, float, int, money, numeric, real, smallint,
smallmoney, and tinyint
smalldate- time
datetime, smalldatetime, char, and varchar
smallint bit, decimal, float, int, money, numeric, real, smallint,
smallmoney, and tinyint
smallmoney bit, decimal, float, int, money, numeric, real, smallint,
smallmoney, and tinyint
text text, unitext
timestamp timestamp
tinyint bit, decimal, float, int, money, numeric, real, smallint,
smallmoney, and tinyint
unichar char, varchar, unichar, univarchar, text, date-
time, and smalldatetime
univarchar char, varchar, unichar, univarchar, text, date-
time, and smalldatetime
unitext unitext
varbinary(n) image, binary(n), and varbinary(n); if not image, the length must match
varchar(n) text, nchar( n), nvarchar( n), char( n), varchar(n)
Remote SAP ASE Datatype
Allowable SAP ASE Datatypes date
time
bigint Implicit: binary, varbinary, bit, tinyint, smallint, int,
decimal, numeric, float, real, money, smallmoney Explicit: char, varchar, unichar, univarchar
unsigned tinyint
Implicit: binary, varbinary, bit, tinyint, smallint, un-
signed smallint, int, unsigned int, bigint, unsigned
bigint, decimal, numeric, float, real money, smallmo-
ney
Explicit: char, varchar, unichar, univarchar
Unsupported: text, image, date, time, datetime, smallda-
tetime unsigned
smallint
Implicit: binary, varbinary, bit, tinyint, smallint, un-
signed smallint, int, unsigned int, bigint, unsigned
bigint, decimal, numeric, float, real money, smallmo-
ney
Explicit: char, varchar, unichar, univarchar
Unsupported: text, image, date, time, datetime, smallda-
tetime unsigned
int
Implicit: binary, varbinary, bit, tinyint, smallint, un-
signed smallint, int, unsigned int, bigint, unsigned
bigint, decimal, numeric, float, real money, smallmo-
ney
Explicit: char, varchar, unichar, univarchar
Unsupported: text, image, date, time, datetime, smallda-
Remote SAP ASE Datatype
Allowable SAP ASE Datatypes unsigned
bigint
Implicit: binary, varbinary, bit, tinyint, smallint, un-
signed smallint, int, unsigned int, bigint, unsigned
bigint, decimal, numeric, float, real money, smallmo-
ney
Explicit: char, varchar, unichar, univarchar
Unsupported: text, image, date, time, datetime, smallda-
tetime
Note: Component Integration Services only supports unitext with SAP ASE version 15.0 and higher.
Server Class ASAnywhere
This table describes the allowable datatypes that can be used when mapping remote SAP ASE columns to local proxy table columns:
Table 16. SQL Anywhere datatype conversions for create existing table Remote SQL Any-
where Datatype
Allowable SQL Anywhere Datatypes
binary(n) image, binary(n), and varbinary( n); if not image, the length must match
bit bit, decimal, float, int, money, numeric, real,
smallint, smallmoney, and tinyint
char(n) text, nchar(n), nvarchar(n), char(n), varchar(n), uni-
char, univarchar; if not text, the length must match
datetime datetime and smalldatetime
decimal(p, s) bit, decimal, float, int, money, numeric, real,
smallint, smallmoney, and tinyint
float bit, decimal, float, int, money, numeric, real,
smallint, smallmoney, and tinyint
image image
int bit, decimal, float, int, money, numeric, real,
Remote SQL Any- where Datatype
Allowable SQL Anywhere Datatypes
money bit, decimal, float, int, money, numeric, real,
smallint, smallmoney, and tinyint
numeric(p, s) bit, decimal, float, int, money, numeric, real,
smallint, smallmoney, and tinyint
real bit, decimal, float, int, money, numeric, real,
smallint, smallmoney, and tinyint
smalldatetime datetime and smalldatetime
smallint bit, decimal, float, int, money, numeric, real,
smallint, smallmoney, and tinyint
smallmoney bit, decimal, float, int, money, numeric, real,
smallint, smallmoney, and tinyint
text text
timestamp timestamp
tinyint bit, decimal, float, int, money, numeric, real,
smallint, smallmoney, and tinyint
varbinary(n) image, binary(n), and varbinary(n), unichar,
unitext, univarchar; if not image, the length must match
varchar(n) text, nchar(n), nvarchar(n), char(n),varchar(n),
unichar, univarchar; if not text, the length must match
date time
bigint Implicit: binary, varbinary, bit, tinyint, smallint,
unsigned smallint, int, unsigned int, bigint,
unsigned bigint, decimal, numeric, float, real
money, smallmoney
Explicit: char, varchar, unichar, univarchar
Unsupported: text, image, date, time, datetime, small-
Remote SQL Any- where Datatype
Allowable SQL Anywhere Datatypes unsigned ti-
nyint
Implicit: binary, varbinary, bit, tinyint, smallint,
unsigned smallint, int, unsigned int, bigint,
unsigned bigint, decimal, numeric, float, real
money, smallmoney
Explicit: char, varchar, unichar, univarchar
Unsupported: text, image, date, time, datetime, small-
datetime unsigned small-
int
Implicit: binary, varbinary, bit, tinyint, smallint,
unsigned smallint, int, unsigned int, bigint,
unsigned bigint, decimal, numeric, float, real
money, smallmoney
Explicit: char, varchar, unichar, univarchar
Unsupported: text, image, date, time, datetime, small-
datetime
unsigned int Implicit: binary, varbinary, bit, tinyint, smallint,
unsigned smallint, int, unsigned int, bigint,
unsigned bigint, decimal, numeric, float, real
money, smallmoney
Explicit: char, varchar, unichar, univarchar
Unsupported: text, image, date, time, datetime, small-
datetime
unsigned bigint Implicit: binary, varbinary, bit, tinyint, smallint,
unsigned smallint, int, unsigned int, bigint,
unsigned bigint, decimal, numeric, float, real
money, smallmoney
Explicit: char, varchar, unichar, univarchar
Unsupported: text, image, date, time, datetime, small-
datetime
nchar(n) text, nchar(n), nvarchar(n), char(n), varchar(n),
unichar, univarchar; if not text, the length must match
nvarchar(n) text, nchar(n), nvarchar(n), char(n), varchar(n),
Server Class ASIQ
• text and image datatypes are supported by ASIQ version 12.6 and requires a license. • Behavior is the same as for server class ASAnywhere.
Server Class direct_connect
• The RPC sp_columns queries the datatypes of the columns in the existing table. • Local column datatypes do not need to be identical to remote column datatypes, but they
must be convertible as shown in the table below. If not, a column type error is raised and the command is aborted.
Table 17. DirectConnect datatype conversions for create existing table DirectCon-
nect Datatype
Allowable SAP ASE Datatypes
binary(n) image, binary(n), varbinary(n); if the length does not match, the command is aborted
binary(16) timestamp
bit bit, decimal, float, int, money, numeric, real, small-
int, smallmoney, and tinyint
char(n) text, nchar(n), nvarchar(n), char(n) and varchar(n),
unichar, univarchar; if the length does not match, the command is aborted
datetime datetime, smalldatetime
decimal(p, s) bit, decimal, float, int, money, numeric, real, small-
int, smallmoney, and tinyint
float bit, decimal, float, int, money, numeric, real, small-
int, smallmoney, and tinyint
image image
int bit, decimal, float, int, money, numeric, real, small-
int, smallmoney, and tinyint
money bit, decimal, float, int, money, numeric, real, small-
DirectCon- nect Datatype
Allowable SAP ASE Datatypes
nchar(n) text, nchar(n), nvarchar(n), char(n) and varchar(n),
unichar, univarchar; if the length does not match, the command is aborted
numeric(p, s)
bit, decimal, float, int, money, numeric, real, small-
int, smallmoney, and tinyint
nvarchar(n) text, nchar(n), nvarchar(n), char(n) and varchar(n),
unichar, univarchar; if the length does not match, the command is aborted
real bit, decimal, float, int, money, numeric, real, small-
int, smallmoney, and tinyint
smalldate- time
datetime, smalldatetime
smallint bit, decimal, float, int, money, numeric, real, small-
int, smallmoney, and tinyint
smallmoney bit, decimal, float, int, money, numeric, real, small-
int, smallmoney, and tinyint
text text
timestamp timestamp, binary(8), varbinary(8)
unichar text, nchar(n), nvarchar(n), char(n), varchar(n), uni-
char, univarchar; if not text, the length must match
univarchar text, nchar(n), nvarchar(n), char(n), varchar(n), uni-
char, univarchar; if not text, the length must match
date time
bigint UDB and DC/Microsoft support bigint.
• Datatype information is passed in the CS_DATAFMT structure associated with the parameter. The following fields of the structure contain datatype information:
• datatype – the CS_Library datatype representing the SAP ASE datatype. For example, CS_INT_TYPE.
• usertype – the native DBMS datatype. sp_columns passes this datatype back to Component Integration Services during a create existing table command as part of its
result set (see sp_columns in the Reference Manual). SAP ASE returns this datatype in the usertype field of parameters to assist the DirectConnect in datatype conversions.