Looks like Cayenne attempts to read CLOB as a byte[] instead of a
String, which is odd, and is likely due to Oracle driver incorrectly
reporting the result type. One possible way around it is using the
#result directive in SQLTemplate. The downside of it is that if it is
used, it has to be applied to every column in the result, and the SQL
can get quite verbose.
http://cayenne.apache.org/doc/scripting-sqltemplate.html
Andrus
On Aug 11, 2009, at 3:43 AM, Lawrence Gerstley wrote:
Hello,
Have you any experience using a query with an Oracle "CONTAINS"
clause? I'm trying to get results back from a SQLTemplate query from
a table with CLOBs that have been indexed to improve performance
with the form of:
select * from entity where CONTAINS(entityDescription, 'blood%', 1)
> 0;
I can validate that the query is correct by using a tool like SQL
Squirrel using the same Oracle Thin driver and connection string
that Cayenne uses, and see a valid result from the query. When the
query is performed in Cayenne, however, it generates:
INFO [main] (QueryLogger.java:358) - select * from entity where
CONTAINS(entityDescription, 'blood%', 1) > 0
INFO [main] (QueryLogger.java:454) - *** error.
java.sql.SQLException: Unsupported feature
at
oracle
.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at
oracle
.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at
oracle
.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at
oracle
.jdbc
.driver
.DatabaseError
.throwUnsupportedFeatureSqlException(DatabaseError.java:499)
at oracle.jdbc.driver.ClobAccessor.getBytes(ClobAccessor.java:279)
at
oracle
.jdbc.driver.OracleResultSetImpl.getBytes(OracleResultSetImpl.java:
708)
at org.apache.cayenne.dba.oracle.OracleSQLTemplateAction
$OracleResultSetWrapper.getBytes(OracleSQLTemplateAction.java:241)
at
org
.apache
.cayenne
.access.types.ByteArrayType.materializeObject(ByteArrayType.java:135)
at
org
.apache
.cayenne
.access
.types
.ExtendedTypeDecorator.materializeObject(ExtendedTypeDecorator.java:
54)
at
org
.apache
.cayenne
.access.jdbc.JDBCResultIterator.readDataRow(JDBCResultIterator.java:
314)
at
org
.apache
.cayenne
.access.jdbc.JDBCResultIterator.nextDataRow(JDBCResultIterator.java:
145)
at
org
.apache
.cayenne
.access.jdbc.JDBCResultIterator.dataRows(JDBCResultIterator.java:115)
at
org
.apache
.cayenne
.access
.jdbc.SQLTemplateAction.processSelectResult(SQLTemplateAction.java:
231)
at
org
.apache
.cayenne
.dba
.oracle
.OracleSQLTemplateAction
.processSelectResult(OracleSQLTemplateAction.java:83)
at
org
.apache
.cayenne
.access.jdbc.SQLTemplateAction.execute(SQLTemplateAction.java:171)
at
org
.apache
.cayenne
.access.jdbc.SQLTemplateAction.performAction(SQLTemplateAction.java:
127)
at
org
.apache
.cayenne
.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:58)
...
Having validated that the query is good, and that the driver is good
(identical to Squirrel -- ojdbc14.jar), I'm stuck. Is there another
way to pass-through the query, or something that's limiting Orace
11g functions?
==============================
Lawrence Gerstley, Ph.D.
PSMI Consulting
[email protected]
http://www.psmiconsulting.net
