SQL query returns different result in different gateway version due to char data type

Products

• CA API Gateway

Issue/Introduction

Version: v11-CR01


Issue:
One of SQL query works in v10.1-CR02 and fails in API v11-CR01

The API runs the following SQL statement vs. an Oracle JDBC connection.
 SELECT position_id,REVIEW_DATE from <tablename> where position_id=${positionIdAsString}
 


The position_id field is a CHAR(10) type and it is the primary key of the SQL table. 
The value of variable positionIdAsString is a context variable of type String and the value is P0xxxx
It queries the Oracle database to retrieve the position effective date, using the employee position ID.

Here are the results of the tests done.

In v10.1 - the SQL query returns 1 row.
<L7j:jdbcQueryResult xmlns:L7j="http://ns.l7tech.com/2012/08/jdbc-query-result">
<L7j:row>
<L7j:col name="pos_stat_date" type="java.lang.String">20160319</L7j:col>
<L7j:col name="position_id" type="java.lang.String">P0xxxx</L7j:col>
</L7j:row>
</L7j:jdbcQueryResult>

In v11.0 - the SQL query returns 0 row when we use the variable ${positionIdAsString}
Result: <L7j:jdbcQueryResult xmlns:L7j="http://ns.l7tech.com/2012/08/jdbc-query-result"/>

In v11.0 - when we replace ${positionIdAsString} by a hardcoded value (P0xxxx), the SQL query returns 1 row.
<L7j:jdbcQueryResult xmlns:L7j="http://ns.l7tech.com/2012/08/jdbc-query-result">
<L7j:row>
<L7j:col name="pos_stat_date" type="java.lang.String">20160319</L7j:col>
<L7j:col name="position_id" type="java.lang.String">P0xxxx</L7j:col>
</L7j:row>
</L7j:jdbcQueryResult>

Run SQL query in Oracle tables using context variables in the WHERE condition and they work fine.

Environment

Release : 11.0

Cause

This is due to the char data type. 

If "P0xxxx" is not fix length, they should use varchar2 data type.

Keep in mind  that a CHAR(n) will ALWAYS be N bytes long, it will be blank padded upon insert to ensure this. A varchar2(n) on the other hand will be 1 to N bytes long, it will NOT be blank padded. Using a CHAR on a varying width field can be a pain due to the search semantics of CHAR.

Different jdbc driver may have different way to handle the trailing space. (gateway 11 should use differernt jdbc driver)

So, to avoid inconsistence, you might either,

add trim() function, for example:

trim(position_id)=${xxx}

or,

just use varchar2 data type instead of char data type.

or,

ensure each position_id is the same length as the column definition. for example, regarding char(10), using "P00000xxxx" instead of "P0xxxx"

Resolution

Modify the code to trim the trailing blanks