cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: Oracle CONTAINS clause?
Date Tue, 11 Aug 2009 06:58:24 GMT
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
> lawgers@gmail.com
> http://www.psmiconsulting.net
>


Mime
View raw message