Java Development Tools

We're using ADF 11g against an AS/400 Database.

When using a filter table, or using any advanced search with starts with , .... we are getting an SQL error SQL0418 -- "A statement contains a use of parameter marker that is not valid ..."

The generated SQL looks something like:

SELECT Customer.CGVMSTCST,
Customer.PARENTCUSTOMER, ...
FROM CSTTBLMP Customer,
NADADRMP Address
WHERE Customer.CGVADRLOC = Address.NADADRLOC
AND ( ( (Customer.CGVADRSTE LIKE ( ? || '%') ) ) )


Running this query in test tools confirms that this is not acceptable SQL. However, this is

SELECT Customer.CGVMSTCST,
Customer.PARENTCUSTOMER, ...
FROM CSTTBLMP Customer,
NADADRMP Address
WHERE Customer.CGVADRLOC = Address.NADADRLOC
AND ( ( (Customer.CGVADRSTE LIKE ( cast(? as varchar(255)) || '%' ) ) ) );

DB2 needs the parameter marker to be typed.

We've created CustomerViewImpll.java (view for above examples) and overridden buildWhereClause() to replace
?
with
cast(? as varchar(255)).


In debug, we verify that the buffer passed to buildWhereClause (1st param) is being changed,
but when the query runs, it is not reflecting the changed value.


Is there anything else we need to change?

----------------------------------------------------------------------------------------------------------------------------

CustomerViewImpl.java:
...

@Override
protected boolean buildWhereClause(java.lang.StringBuffer sqlBuffer, int noBindVars) {
// call ViewObjectImpl's buildWhereClause() to allow the framework to do its processing
boolean hasWhereClause = super.buildWhereClause(sqlBuffer, noBindVars);

if (hasWhereClause) { // framework added a WHERE clause
sqlBuffer = replace(sqlBuffer, "?", "cast(? as varchar(255))");
}
else { // framework did not add a WHERE clause, so we need to add it ourselves
hasWhereClause = true; // ensure that is set to notify the framework
}

return hasWhereClause; // return true/false to indicate whether a WHERE clause was added
}

public StringBuffer replace (StringBuffer strBuffer, String searchString, String newString) {
String str = strBuffer.toString();
// Need to use Regex functionality, or the '?' replacement parm is construed as a regex instead of a literal
String newStr = str.replaceFirst(Pattern.quote(searchString), Matcher.quoteReplacement(newString));
return new StringBuffer(newStr);
}

...