search cancel

SQL.VARA resolution fails with Invalid column index

book

Article ID: 216017

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine

Issue/Introduction

SQL VARA resolution randomly fails with error "Invalid column index" when invoked via PREP_PROCESS_VAR(VARA_SQL_NAME) or doing a Preview as somehow the Automation Engine passes some random bind parameters (somtimes 1 bind parameter, sometimes 2) which do not correspond to the VARA SQL definition at all.

In some cases, the very same SQL VARA query previewworks fine with doing Preview via AWI.
The issue only occurs VARA SQL, it seems not to affect a VARA SQLI or VARA SQL SEC having the same query, which can be used as a workaround.

The kind of error messages that appear in the db service agent log when launching a "VARA.SQL" with a simple query doing "select * from oh;" is the following:

U02012035 Start Agent query with RunID 'NNNNNNNNNN'.
U02012041 Query a table without waiting for a database. RunID: 'NNNNNNNNNN'
U02012001 Successfully established connection to 'XXX' with user 'YYY'.
U02012075 Set bind parameter '1' of type 'object' to value 'ticinese'
U02012045 The database returned an error for RunID 'NNNNNNNNNN/1':
        Invalid column index
U02012037 Query with RunID 'NNNNNNNNNN' ended abnormally.

 

On other cases, it may return this other error and produce a WP crash when SQL VARA returns no results:

U02012033 Query could not be executed: 'The index 1 is out of range.

Environment

Release : 12.2 and 12.3

Component : AUTOMATION ENGINE

Cause

Bug

Resolution

Solution:
Update to a fix version listed below or a newer version if available.

Fix version:
Component(s): Automation.Engine 

Automation.Engine 12.2.9 - Available
Automation.Engine 12.3.5 - Available

Additional Information

Solution details:

When SQL Variables are being resolved, an error "invalid column index" can occur. This happens, when prior dynamic variables with bind parameters are being resolved.

U02012045 The database returned an error for RunID '&01': Invalid column index