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
> lawger..mail.com
> http://www.psmiconsulting.net
>
This archive was generated by hypermail 2.0.0 : Tue Aug 11 2009 - 02:59:04 EDT