Sql server Job or Subvar fails with error "AwE-5028 Unknown Oracle data type retrieved from database"

Products

• CA Automic Applications Manager (AM)

Issue/Introduction

After upgrading to Applications Manager version 9.6, 9.6.1, or 9.6.2, Jobs or Substitution variables using a SQL server connection may fail with the below error:

ErrorMsg: AwE-5028 Unknown Oracle data type retrieved from database
java.sql.SQLException: Unknown Oracle Data type: -9
 at com.appworx.server.sql.DBAccess.parseResultSet(DBAccess.java:1231)
 at com.appworx.server.sql.DBAccess.A(DBAccess.java:1269)
 at com.appworx.server.sql.DBAccess.getSqlData(DBAccess.java:545)
 at com.appworx.server.sql.DBAccess.getSqlData(DBAccess.java:491)
 at com.appworx.server.sql.DBAccess.getSqlData(DBAccess.java:642)
 at com.appworx.server.data.MasterDB.getSqlData(MasterDB.java:345)
 at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
 at java.base/java.lang.reflect.Method.invoke(Method.java:580)
 at com.uc4.be.util.InvokeUtil.invoke(InvokeUtil.java:122)
 at com.appworx.util.InvokeRequest.invokeMethod(InvokeRequest.java:47)
 at com.appworx.server.data.B.A(ClientServices.java:150)
 at com.appworx.server.data.B.A(ClientServices.java:132)
 at com.appworx.server.data.D$_A.doRun(RemoteClientSocketManager.java:319)
 at com.uc4.be.threading.AbstractWorker.run(AbstractWorker.java:367)
 at java.base/java.lang.Thread.run(Thread.java:1583)

Environment

Applications Manager version 9.6 to 9.6.2

Cause

Sql server Job or subvar is querying a database column which has a character data type of NVARCHAR.

Resolution

While Applications Manager never supported NVARCHAR on paper, pre 9.6 Applications Manager used the jTDS JDBC driver to connect to MS SQL Server, which implicitly handled NVARCHAR columns by treating them as VARCHAR.

Starting from version 9.6.0, the driver was changed to the Microsoft SQL Server JDBC driver. Unlike jTDS, the Microsoft driver does not map NVARCHAR to VARCHAR, resulting in failures when reading columns defined as NVARCHAR.

A fix will be included in Applications Manager version 9.6.3 or above to read NVARCHAR and NCHAR on SQL Server database login type.

Workaround for version 9.6 to 9.6.2:

Option 1 - update select sql to use "CAST(column_name as VARCHAR(#))"
Option 2 - update the database column's character data type from NVARCHAR to VARCHAR