>>>> The solution is:
>>>> WHERE UPPER(name) LIKE UPPER(CAST(? AS VARCHAR (100)))
Mario, Holger:
I just checked in refactored translator code to provide framework for
this customization of DB2 Adapter. Now
QualifierTranslator.appendLiteral() also takes an expression parameter
that allows to check for LIKE expressions, and process them
accordingly. (This doesn't address the bigger issue of code spaghetti
in qualifier translator, but well... lets work with what we have now
:-)).
I am attaching a sample QualifierTranslator implementation for DB2.
Since I don't have DB2 installed and have no way of testing it, I did
not commit it yet. Someone please confirm that it solves the problem
(of course to use it, you will need to patch DB2Adapter to use this
translator). Interestingly, I was able to test it on Oracle, since CAST
is a valid operator there. Everything seemed to work OK.
Let me know how it goes.
Andrus
P.S. Mario mentioned that the size in "AS VARCHAR(100)" needs to be
customized as well. Could you explain why and what is the criteria?
/* ====================================================================
*
* The ObjectStyle Group Software License, Version 1.0
*
* Copyright (c) 2002-2003 The ObjectStyle Group
* and individual authors of the software. All rights reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions
* are met:
*
* 1. Redistributions of source code must retain the above copyright
* notice, this list of conditions and the following disclaimer.
*
* 2. Redistributions in binary form must reproduce the above copyright
* notice, this list of conditions and the following disclaimer in
* the documentation and/or other materials provided with the
* distribution.
*
* 3. The end-user documentation included with the redistribution, if
* any, must include the following acknowlegement:
* "This product includes software developed by the
* ObjectStyle Group (http://objectstyle.org/)."
* Alternately, this acknowlegement may appear in the software itself,
* if and wherever such third-party acknowlegements normally appear.
*
* 4. The names "ObjectStyle Group" and "Cayenne"
* must not be used to endorse or promote products derived
* from this software without prior written permission. For written
* permission, please contact andru..bjectstyle.org.
*
* 5. Products derived from this software may not be called "ObjectStyle"
* nor may "ObjectStyle" appear in their names without prior written
* permission of the ObjectStyle Group.
*
* THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
* WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
* OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
* DISCLAIMED. IN NO EVENT SHALL THE OBJECTSTYLE GROUP OR
* ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
* SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
* LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
* USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
* OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
* OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
* SUCH DAMAGE.
* ====================================================================
*
* This software consists of voluntary contributions made by many
* individuals on behalf of the ObjectStyle Group. For more
* information on the ObjectStyle Group, please see
* <http://objectstyle.org/>.
*
*/
package org.objectstyle.cayenne.dba.db2;
import java.sql.Types;
import org.objectstyle.cayenne.CayenneRuntimeException;
import org.objectstyle.cayenne.access.trans.QueryAssembler;
import org.objectstyle.cayenne.access.trans.TrimmingQualifierTranslator;
import org.objectstyle.cayenne.dba.TypesMapping;
import org.objectstyle.cayenne.exp.Expression;
import org.objectstyle.cayenne.map.DbAttribute;
/**
*..uthor Andrei Adamchik
*/
public class DB2QualifierTranslator extends TrimmingQualifierTranslator {
public DB2QualifierTranslator() {
super();
}
public DB2QualifierTranslator(
QueryAssembler queryAssembler,
String trimFunction) {
super(queryAssembler, trimFunction);
}
protected void appendLiteralDirect(
StringBuffer buf,
Object val,
DbAttribute attr,
Expression parentExpression) {
boolean castNeeded = false;
if (parentExpression != null) {
int type = parentExpression.getType();
castNeeded =
attr != null
&& (type == Expression.LIKE
|| type == Expression.LIKE_IGNORE_CASE
|| type == Expression.NOT_LIKE
|| type == Expression.NOT_LIKE_IGNORE_CASE);
}
if (castNeeded) {
buf.append("CAST (");
}
super.appendLiteralDirect(buf, val, attr, parentExpression);
if (castNeeded) {
int jdbcType = attr.getType();
// triming a value first, and then comparing it to CHAR may produce unpredictable results.
if (jdbcType == Types.CHAR) {
jdbcType = Types.VARCHAR;
}
buf.append(" AS ");
String[] types =
getQueryAssembler().getAdapter().externalTypesForJdbcType(
jdbcType);
if (types == null || types.length == 0) {
throw new CayenneRuntimeException(
"Can't find database type for JDBC type '"
+ TypesMapping.getSqlNameByType(jdbcType));
}
buf.append(types[0]);
if (attr.getMaxLength() > 0
&& TypesMapping.supportsLength(jdbcType)) {
buf.append("(").append(attr.getMaxLength()).append(")");
}
buf.append(")");
}
}
}
This archive was generated by hypermail 2.0.0 : Tue Jun 24 2003 - 22:44:53 EDT