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
