You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Lawrence Gerstley <la...@gmail.com> on 2009/08/11 02:43:28 UTC
Oracle CONTAINS clause?
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
Re: Oracle CONTAINS clause?
Posted by Andrus Adamchik <an...@objectstyle.org>.
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
>