After 24.x upgrade PREP_PROCESS_VAR does not work with filters

Products

• CA Automic Workload Automation - Automation Engine
• CA Automic One Automation

Issue/Introduction

In 21.0, running a PREP_PROCESS_VAR with a filter would return rows.  In 24.X, these no longer return rows.  For example, when running a SQLI variable like this:

select eh_otype, eh_name from eh where eh_otype = 'JOBP'

where there are three results all starting with JOBP. 

Using a PREP_PROCESS_VAR in a script like:

:SET &HND#=PREP_PROCESS_VAR(VARA_SEC_SQLI_JOB_STATUS_CHECK,'JOBP')
:PROCESS &HND#
:   SET &LINE# = GET_PROCESS_LINE(&HND#)
:   P &LINE#
:END_PROCESS

No lines are returned.  When running something without a filter like:

:SET &HND#=PREP_PROCESS_VAR(VARA_SEC_SQLI_JOB_STATUS_CHECK)
:PROCESS &HND#
:   SET &LINE# = GET_PROCESS_LINE(&HND#)
:   P &LINE#
:END_PROCESS

Three lines are returned that all have the first column being JOBP.  The database is on Microsoft SQL Server and uses a traditional DSN for the ODBC sqldriverconnect setting

Environment

Automation Engine: 24.X
Database: MSSQL

Cause

This issue is caused by an implicit conversion that is made with ODBC connections on Windows with UTF8 database collation.

Resolution

Use a DSN-less connection string in ucsrv.ini file.  

Automic is moving towards using DSN-less sqldriverconnect strings like the following:

SQLDRIVERCONNECT=ODBCVAR=NNNNNNRN,Driver={ODBC Driver 17 for SQL Server};Server=tcp:[ADD.IP.ADDR.HERE],1433;Database=[database name];Uid=[user name];PWD=[password_goes_here];MARS_CONNECTION=Yes;TrustServerCertificate=YES;AutoTranslate=NO;

instead of the traditional DSN sqldriverconnect like this:

sqlDriverConnect=ODBCVAR=NNNNNNRN,DSN=automic;UID=automic;PWD=???;Mars_Connection=Yes;TrustServerCertificate=YES;AutoTranslate=NO

Using a DSN-less connection string should resolve this issue.