Text vs Varchar in Connector Xpress in JDBC endpoints

Products

• CA Identity Manager
• CA Identity Governance
• CA Identity Portal
• CA Risk Analytics
• CA Secure Cloud SaaS - Arcot A-OK (WebFort)
• CLOUDMINDER ADVANCED AUTHENTICATION
• CA Secure Cloud SaaS - Advanced Authentication
• CA Secure Cloud SaaS - Identity Management
• CA Secure Cloud SaaS - Single Sign On

Issue/Introduction

When building a custom JDBC endpoint it is important to ensure that your database data types match the data types that Connector Xpress uses. When mapping data to the String type in Connector Xpress the data type that should be chosen on the database is a varchar. Other data types such as text will cause errors like the one below.

The following error occurred while trying to view the details of an endpoint account from a JDBC endpoint where the account name in the database is of the data type text and it is mapped to an attribute with the data type String in Connector Xpress.

:ETA_E_0020<RAC>, User Account ‘[ENDPOINT ACCOUNT NAME]’ on ‘[ENDPOINT NAME]’ read failed: Connector Server Read failed: code 80 (OTHER-LdapNamingException): failed on search operation: [ACCOUNT DN]: JCS@[HOST NAME]: JDBC: Bad SQL Grammar: The data types text and nvarchar are incompatible in the equal to operator.

(/daps:/[HOST NAME].[PORT NUMBER])

 

<Please see attached file for image>

 

 

Environment

Release:
Component: IDMGR

Resolution

The underlying datatype of a String field in Connector Xpress is nvarchar. Therefore, in order to map an SQL database field to a Connector Xpress String field it should be type varchar.

A common error is setting the database field to the data type text. Text is a deprecated data type as of SQL Server 2005. Since Text is deprecated it is a best practice to avoid using it in all new development, not only with Identity Manager.