SQL Gotchas

While skimming my old zip drives I came across this interesting note that I had written years back related to SQL statements and prepared statements using iBatis framework. It was something to do with datatype of columns and implicit joins using prepared statement.

Problem: Issue of a static sql statement returns expected results, same statement issued as a prepared statment with parameters fails.

Static statement:

SELECT
nvl(sum(BATCHREL), 0) as CNT
FROM
userapplications a,
users b,
customers c
WHERE
a.USERRID = b.RID and
b.CUSTOMERKEY = c.RID AND
lower(c.ID) = '900100ev' AND
lower(b.ID) = 'admin1'

Prepared statment template:

SELECT
nvl(sum(BATCHREL), 0) as CNT
FROM
userapplications a,
users b,
customers c
WHERE
a.USERRID = b.RID and
b.CUSTOMERKEY = c.RID AND
lower(c.ID) = ? AND
lower(b.ID) = ?

Parameter values: '900100ev','admin1'



What I saw happening:

The c.id metadata was CHAR(20) and did not match the parameter passed in using prepared stmt, unless I padded it with spaces (to meet the length), however when I chose a literal string it worked w/out issues. I had to modify the prepared statement as follows (and it works)

SELECT
nvl(sum(BATCHREL), 0) as CNT
FROM
userapplications a,
users b,
customers c
WHERE
a.USERRID = b.RID and
b.CUSTOMERKEY = c.RID AND
RTRIM(lower(c.ID)) = #companyid# AND
lower(b.ID) = #operatorid#

Parameter values: '900100ev','admin1'