Adapter Key Query Field is not working

Products

• CA Business Service Insight

Issue/Introduction

When configuring the adapter Key Query start value it appears to return too many records. The start value seems to be ignored.

Error displayed in the adapter.log file

From Table1 where Mytime<1317427200 order by Mytime desc) [[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Mytime'. (HR: 0x80040e14) (IDispatch error #3092) (Source = Microsoft OLE DB Provider for ODBC Drivers)][(Error #80040e14) (Source = Microsoft OLE DB Provider for ODBC Drivers) (Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Mytime'.) (SQLState = S0022) (NativeError: cf)]"/>

Environment

CA Business Service Insight 8.3.x and 9.x

Resolution

The best way to understand what is occurring is to describe how this feature performs its tasks.

The purpose of the field is to only try to access new or updated information from a source database, reducing the overhead of too many records being read into the BSI database.

The way it accomplishes this is by storing the last record read and then adding a filter to the current query.

A sample query from the adapter would look like this:

select statement : Select Eventime, ResourceName, EventTypeof, EventImpact, EventPerformance, EventReason
From Table1

After adding a Key Query Field with the field Eventime, BSI adds a where statement automatically as seen below:

select statement : Select Eventime, ResourceName, EventTypeof, EventImpact, EventPerformance, EventReason
From Table1 where EvenTime>:EvenTime order by EvenTime
primary key fields:
EvenTime

Depending on the data source, there may be reasons to re-label input field names with an "AS" statement but with BSI it is not possible to do this while using the Key Query Field.

The problem output is caused because BSI will try to create the where filter using the new field name, like below:

select statement : Select [Eventime] as Mytime, ResourceName, EventTypeof, EventImpact, EventPerformance, EventReason
From Table1 where Mytime>:Mytime order by Mytime desc
primary key fields:
Mytime

Solution:

The best way to correct this is to remove the casting of the field name. If this is not possible, then inserting your own where statement (ex. where [Eventime]>:Mytime) into the query including the Key Query field defined should allow the proper functionality.