Unicode support

SAP ASE contains formal support for the Unicode character set.

The datatypes provided are unichar, univarchar, and unitext. They comprise 2-byte characters expressed in Unicode. SAP ASE provides conversion functions between Unicode data and all other datatypes, consistent with current handling of char and varchar datatypes. By supporting these datatypes, Component Integration Services is able to present a view of all enterprise character data expressed in Unicode. Character data from mainframes and all other foreign or legacy systems is converted to Unicode when columns of type unichar or univarchar are used to defined columns in proxy tables.

The Component Integration Services features below are affected by these new datatypes:

create table – may contain columns described using the new Unicode datatypes. If the table to be created is a proxy table, Component Integration Services forwards the entire command, including the Unicode datatype names (unichar, univarchar, and unitext) to the remote server where the new table is to be created. If the remote server cannot handle the datatypes, it raises an error.
create exiting table – when comparing SAP ASE column types and lengths with the metadata obtained from a remote server, Unicode datatypes in the proxy table are allowed under the following circumstances:
- The remote server datatype for a column is unichar, unitext, or univarchar with equal length (expressed in characters, not bytes).
- The remote server datatype for a given column is char or varchar. In this case, Component Integration Services performs conversions to Unicode on data fetched from the remote server, and conversions from Unicode to the default SAP ASE character set (UTF8) on data transmitted as part of DML commands (select, insert, delete, update).
- The remote server datatype for a Unicode column is binary or varbinary. The length of the remote server column must be twice the length of the Unicode column. Component Integration Services performs conversions as required when transmitting data to or from the remote server.
  
  No other datatype mapping for Unicode datatypes is allowed when mapping a proxy table to a remote table. Other types result in a type mismatch error. You can convert data from legacy systems into Unicode simply by creating a proxy table that maps a Unicode column to an existing char or varchar column.
  
  Note: Unicode can only be mapped to unitext columns using the create existing table command.
create proxy_table – by using create proxy_table, an SAP ASE user does not have to specify the column list associated with the proxy table. Instead, the column list is derived from column metadata imported from the remote server on which the actual table resides. Unicode columns from the remote server are mapped to Unicode columns in the proxy table only when the remote column is datatype unichar, unitext, or univarchar.
alter table – allows column types to be modified. With SAP ASE version 12.5 and later, a column’s type can be modified to and from Unicode datatypes. If the command operates on a proxy table, the command is reconstructed and forwarded to the remote server that owns the actual table. If the remote server (or DirectConnect) cannot process the command, an error is expected, and the SAP ASE command is aborted.

If trace flag 11221 is on, alter table does not get forwarded to a remote server; adding, deleting, or modifying columns is done locally on the proxy table only.

Using the alter table command, unitext can be changed to char, varchar, nchar, nvarchar, unichar, univarchar, binary, and varbinary. Any of these datatypes can be changed to unitext.
select, insert, update, and delete statements – Unicode datatypes impact the processing of select statements in two ways when proxy tables are involved. The first involves the construction of SQL statements and parameters that are passed to remote servers; the second involves the conversion of data to Unicode when Component Integration Services fetches non-Unicode data.

A DML command involving a proxy table is handled using either TDS language requests or TDS cursor requests when interacting with the remote server. If a select statement contains predicates in the where clause that involve Unicode columns and constants, the Unicode constants musts be handled in one of two ways, depending on whether language or cursor commands are used to process the statement:
1. TDS language – generate clear-text values that can be included in the language text buffer. This involves converting a constant Unicode value to clear text values that can be transmitted as part of a language request.
2. TDS cursor – generate Unicode parameters for CT-Library cursor requests. Parameter values may be Unicode data, requiring Component Integration Services to use parameter types of CS_UNICHAR_TYPE.
Component Integration Services handles an insert command involving a proxy table using either TDS language requests or TDS dynamic requests.

If the insert command can be processed in quickpass mode, then TDS language requests are used. If the command cannot be handled in quickpass mode, the insert is processed using TDS Dynamic requests.

In language requests, the issues are the same as with select — Unicode values must be converted to clear-text form so they can be transmitted with the rest of the SQL statement. In dynamic requests, Unicode data (along with all other data values) is transmitted as parameters to the dynamic command. The receiving server is expected to process parameters of type CS_UNICHAR_TYPE.

The issues with update and delete commands are the same as for select and insert. Unicode values must be converted either to clear-text characters for transmission with the rest of the SQL statement, or they must be converted into parameters of type CS_UNICHAR_TYPE.