I guess I had modified JdbcAccessImpl to add some print statements.
I have solved the problem. Here's what was happening:
I had code like this: crit.addGreaterThan("contains(clob_column,
'someval')", "0"), because I needed to get SQL like this:
contains(clob_column, 'someval') > 0. When OJB parsed the first parameter
to extract the referenced column, it decided (correctly) that it was
looking at a CLOB. So when it went to bind the second parameter ("0"), it
tried to handle it as a CLOB as well, which (of course) didn't work.
The workaround that I am using: I made a custom platform implementation
which overrides setObjectForStatement() to look for the special case where
a String containing a single digit is being treated as a CLOB and treat it
as an integer instead. This test, at least in my app, will pretty
reliably indicate this specific situation.
I'm not sure whether there's a cleaner solution short of making OJB aware
of functions and their specific return types.
As an aside, it sure would be nice to be able to add new Platform
implementations without having to put them into
org.apache.ojb.broker.platforms. A simple solution would be something
like this (in PlatformFactory):
private static String getClassnameFor(String platform)
{
String pf = "Default";
if (platform != null)
{
+ if (platform.indexOf('.') > 0)
+ {
+ return platform;
+ }
pf = platform;
}
return "org.apache.ojb.broker.platforms.Platform" +
pf.substring(0, 1).toUpperCase() + pf.substring(1) + "Impl";
}
-steve
Steve Clark
ECOS Development Group
[EMAIL PROTECTED]
(970)226-9291
Armin Waibel <[EMAIL PROTECTED]>
07/28/2006 11:35 AM
Please respond to
"OJB Users List" <ojb-user@db.apache.org>
To
OJB Users List <ojb-user@db.apache.org>
cc
Subject
Re: ORA-00932 inconsistent datatypes with CLOB
Hi Steve,
do you use a modified version of JdbcAccessImpl? The stack trace show
line 327 in method 'executeQuery' as causer but this doesn't match with
1.0.4 version of this class (SNV: 365259)
...
at
org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl.java:327)
regards,
Armin
[EMAIL PROTECTED] wrote:
> I am using OJB 1.0.4, Persistence Broker, Oracle 10g, Oracle Text.
>
> I have a query which is using the Oracle Text contains() function to
> search in a CLOB. It is throwing ORA-00932: inconsistent datatypes:
> expected - got CLOB.
> If I replace the ?'s with appropriate values and do
> Statement.executeQuery(), i.e. do the binding by hand and pass this
query
> straight to JDBC, then it works. Similarly, if I give it to sqlplus, it
> works. Can anybody tell me why it fails when produced by OJB? The one
> possibly weird thing that I can come up with is that 'SPECIES', the
value
> that's being sought in the CLOB column, is not a bind variable in the
> PreparedStatement but rather a literal (I couldn't figure out how to do
it
> otherwise with PB).
>
> Any insights?
>
> thanks,
> -steve
>
> Steve Clark
> ECOS Development Group
> [EMAIL PROTECTED]
> (970)226-9291
>
> Full stack trace:
>
> An error has occurred
>
> org.apache.ojb.broker.PersistenceBrokerSQLException: * SQLException
during
> execution of sql-statement: * sql statement was 'SELECT DISTINCT
>
A0.ACTIVITY_ID,A0.PRIMARY_WORK_TYPE_ID,A0.IS_EARLY_PLANNING,A0.IS_MITIGATION_REQUIRED,A0.ACRES_IMPACTED,A0.MILES_IMPACTED,A0.S18_FISHWAY_PRESCRIPTIONS,A0.S18_FISHWAY_MILES,A0.OTHER_RIVER_MILES,A0.FWS_RESPONSE_DATE,A0.FINAL_DECISION_DATE,A0.PERMIT_DATE,A0.PERMIT_AGENCY_ID,A0.OTHER_PERMIT_AGENCY,A0.REF_ENVIR_REVIEW,A0.REF_FOIA_CODE,A0.FEDACT_REVIEW_TYPE_ID,A0.APPLICANT_TYPE_ID,A0.PERMIT_STATUS_ID,A0.FWS_RECOMMENDATION,A1.SEQUENCE
> as ojb_col_21 FROM TAILS_DEV.FEDERAL_ACTIVITY A0 INNER JOIN
> TAILS_DEV.ACTIVITY A1 ON A0.ACTIVITY_ID=A1.ACTIVITY_ID WHERE
> A0.ACTIVITY_ID IN (SELECT DISTINCT B0.ACTIVITY_ID FROM
> TAILS_DEV.FEDERAL_ACTIVITY B0 LEFT OUTER JOIN TAILS_DEV.ACTIVITY B1 ON
> B0.ACTIVITY_ID=B1.ACTIVITY_ID LEFT OUTER JOIN TAILS_DEV.ACTIVITY_EVENT
B2
> ON B1.ACTIVITY_ID=B2.ACTIVITY_ID LEFT OUTER JOIN TAILS_DEV.EVENT B3 ON
> B2.EVENT_ID=B3.EVENT_ID LEFT OUTER JOIN TAILS_DEV.ACTIVITY B4 ON
> B0.ACTIVITY_ID=B4.ACTIVITY_ID LEFT OUTER JOIN
> TAILS_DEV.ACTIVITY_OTHER_OFFICE B5 ON B4.ACTIVITY_ID=B5.ACTIVITY_ID LEFT
> OUTER JOIN ECOS_DATA.ECOS_OFFICE B6 ON B5.OFFICE_ID=B6.OFFICE_ID WHERE (
> contains(B3.DESCRIPTION,'SPECIES') > ?) AND (B4.LEAD_OFFICE_ID = ? OR
> (B6.OFFICE_ID = ?))) ORDER BY 21' * Exception message is [ORA-00932:
> inconsistent datatypes: expected - got CLOB ] * Vendor error code [932]
*
> SQL state code [42000]
>
>
> org.apache.ojb.broker.PersistenceBrokerException:
> org.apache.ojb.broker.PersistenceBrokerSQLException:
> * SQLException during execution of sql-statement:
> * sql statement was 'SELECT DISTINCT
>
A0.ACTIVITY_ID,A0.PRIMARY_WORK_TYPE_ID,A0.IS_EARLY_PLANNING,A0.IS_MITIGATION_REQUIRED,A0.ACRES_IMPACTED,A0.MILES_IMPACTED,A0.S18_FISHWAY_PRESCRIPTIONS,A0.S18_FISHWAY_MILES,A0.OTHER_RIVER_MILES,A0.FWS_RESPONSE_DATE,A0.FINAL_DECISION_DATE,A0.PERMIT_DATE,A0.PERMIT_AGENCY_ID,A0.OTHER_PERMIT_AGENCY,A0.REF_ENVIR_REVIEW,A0.REF_FOIA_CODE,A0.FEDACT_REVIEW_TYPE_ID,A0.APPLICANT_TYPE_ID,A0.PERMIT_STATUS_ID,A0.FWS_RECOMMENDATION,A1.SEQUENCE
> as ojb_col_21 FROM TAILS_DEV.FEDERAL_ACTIVITY A0 INNER JOIN
> TAILS_DEV.ACTIVITY A1 ON A0.ACTIVITY_ID=A1.ACTIVITY_ID WHERE
> A0.ACTIVITY_ID IN (SELECT DISTINCT B0.ACTIVITY_ID FROM
> TAILS_DEV.FEDERAL_ACTIVITY B0 LEFT OUTER JOIN TAILS_DEV.ACTIVITY B1 ON
> B0.ACTIVITY_ID=B1.ACTIVITY_ID LEFT OUTER JOIN TAILS_DEV.ACTIVITY_EVENT
B2
> ON B1.ACTIVITY_ID=B2.ACTIVITY_ID LEFT OUTER JOIN TAILS_DEV.EVENT B3 ON
> B2.EVENT_ID=B3.EVENT_ID LEFT OUTER JOIN TAILS_DEV.ACTIVITY B4 ON
> B0.ACTIVITY_ID=B4.ACTIVITY_ID LEFT OUTER JOIN
> TAILS_DEV.ACTIVITY_OTHER_OFFICE B5 ON B4.ACTIVITY_ID=B5.ACTIVITY_ID LEFT
> OUTER JOIN ECOS_DATA.ECOS_OFFICE B6 ON B5.OFFICE_ID=B6.OFFICE_ID WHERE (
> my_contains(B3.DESCRIPTION,'E') > ?) AND (B4.LEAD_OFFICE_ID = ? OR
> (B6.OFFICE_ID = ?))) ORDER BY 21'
> * Exception message is [ORA-00932: inconsistent datatypes: expected -
got
> CLOB
> ]
> * Vendor error code [932]
> * SQL state code [42000]
> at
>
org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:272)
> at
>
org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:284)
> at
>
org.apache.ojb.broker.core.PersistenceBrokerImpl.getCollectionByQuery(PersistenceBrokerImpl.java:1475)
> at
>
org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(DelegatingPersistenceBroker.java:383)
> at
>
org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(DelegatingPersistenceBroker.java:383)
> at gov.doi.tat.odmg.Select.byCriteria(Select.java:314)
> at gov.doi.tat.odmg.Select.byCriteria(Select.java:265)
> at
gov.doi.tat.odmg.Select.byCriteriaWithSubQuery(Select.java:242)
> at
>
gov.doi.tails.dao.odmg.AbstractSearchDaoImpl.search(AbstractSearchDaoImpl.java:306)
> at
> gov.doi.tails.biz.AbstractSearchMgr.search(AbstractSearchMgr.java:129)
> at
>
gov.doi.tails.actions.AbstractSearchAction.doSearch(AbstractSearchAction.java:224)
> at
>
gov.doi.tails.actions.SearchFedActsAction.getResults(SearchFedActsAction.java:31)
> at
>
gov.doi.tails.actions.AbstractSearchAction.doExec(AbstractSearchAction.java:55)
> at
gov.doi.tails.actions.TailsAction.execute(TailsAction.java:104)
> at
>
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
> at
>
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
> at
> org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)
> at
> org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
> at
> gov.doi.tat.servlet.TatActionServlet.doPost(TatActionServlet.java:148)
> at
> gov.doi.tails.util.TailsController.doPost(TailsController.java:159)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> at
>
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
> at
>
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
> at
>
org.displaytag.filter.ResponseOverrideFilter.doFilter(ResponseOverrideFilter.java:125)
> at
>
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:213)
> at
>
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
> at
>
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
> at
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
> at
>
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
> at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> at
>
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
> at
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
> at
>
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:551)
> at
>
gov.doi.ecos.catalina.valves.EcosAuthenticator.invoke(EcosAuthenticator.java:163)
> at
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
> at
>
gov.doi.ecos.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:581)
> at
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
> at
>
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
> at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> at
>
org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2422)
> at
>
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
> at
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
> at
>
org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
> at
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
> at
>
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:163)
> at
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
> at
>
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:577)
> at
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
> at
>
org.apache.catalina.authenticator.EcosSingleSignOn.invoke(EcosSingleSignOn.java:193)
> at
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
> at
>
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
> at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> at
>
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
> at
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
> at
>
org.apache.catalina.valves.RequestDumperValve.invoke(RequestDumperValve.java:221)
> at
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
> at
>
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
> at
> org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> at
> org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:199)
> at
> org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:324)
> at
> org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:395)
> at
> org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:673)
> at
>
org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:615)
> at
> org.apache.jk.common.SocketConnection.runIt(ChannelSocket.java:786)
> at
>
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
> at java.lang.Thread.run(Thread.java:534)
> Caused by: org.apache.ojb.broker.PersistenceBrokerSQLException:
> * SQLException during execution of sql-statement:
> * sql statement was 'SELECT DISTINCT
>
A0.ACTIVITY_ID,A0.PRIMARY_WORK_TYPE_ID,A0.IS_EARLY_PLANNING,A0.IS_MITIGATION_REQUIRED,A0.ACRES_IMPACTED,A0.MILES_IMPACTED,A0.S18_FISHWAY_PRESCRIPTIONS,A0.S18_FISHWAY_MILES,A0.OTHER_RIVER_MILES,A0.FWS_RESPONSE_DATE,A0.FINAL_DECISION_DATE,A0.PERMIT_DATE,A0.PERMIT_AGENCY_ID,A0.OTHER_PERMIT_AGENCY,A0.REF_ENVIR_REVIEW,A0.REF_FOIA_CODE,A0.FEDACT_REVIEW_TYPE_ID,A0.APPLICANT_TYPE_ID,A0.PERMIT_STATUS_ID,A0.FWS_RECOMMENDATION,A1.SEQUENCE
> as ojb_col_21 FROM TAILS_DEV.FEDERAL_ACTIVITY A0 INNER JOIN
> TAILS_DEV.ACTIVITY A1 ON A0.ACTIVITY_ID=A1.ACTIVITY_ID WHERE
> A0.ACTIVITY_ID IN (SELECT DISTINCT B0.ACTIVITY_ID FROM
> TAILS_DEV.FEDERAL_ACTIVITY B0 LEFT OUTER JOIN TAILS_DEV.ACTIVITY B1 ON
> B0.ACTIVITY_ID=B1.ACTIVITY_ID LEFT OUTER JOIN TAILS_DEV.ACTIVITY_EVENT
B2
> ON B1.ACTIVITY_ID=B2.ACTIVITY_ID LEFT OUTER JOIN TAILS_DEV.EVENT B3 ON
> B2.EVENT_ID=B3.EVENT_ID LEFT OUTER JOIN TAILS_DEV.ACTIVITY B4 ON
> B0.ACTIVITY_ID=B4.ACTIVITY_ID LEFT OUTER JOIN
> TAILS_DEV.ACTIVITY_OTHER_OFFICE B5 ON B4.ACTIVITY_ID=B5.ACTIVITY_ID LEFT
> OUTER JOIN ECOS_DATA.ECOS_OFFICE B6 ON B5.OFFICE_ID=B6.OFFICE_ID WHERE (
> my_contains(B3.DESCRIPTION,'E') > ?) AND (B4.LEAD_OFFICE_ID = ? OR
> (B6.OFFICE_ID = ?))) ORDER BY 21'
> * Exception message is [ORA-00932: inconsistent datatypes: expected -
got
> CLOB
> ]
> * Vendor error code [932]
> * SQL state code [42000]
> at
>
org.apache.ojb.broker.util.ExceptionHelper.generateException(ExceptionHelper.java:256)
> at
>
org.apache.ojb.broker.util.ExceptionHelper.generateException(ExceptionHelper.java:90)
> at
>
org.apache.ojb.broker.util.ExceptionHelper.generateException(ExceptionHelper.java:71)
> at
>
org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl.java:327)
> at
>
org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(RsQueryObject.java:72)
> at
> org.apache.ojb.broker.accesslayer.RsIterator.(RsIterator.java:187)
> at
>
org.apache.ojb.broker.core.RsIteratorFactoryImpl.createRsIterator(RsIteratorFactoryImpl.java:58)
> at
>
org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(PersistenceBrokerImpl.java:2061)
> at
>
org.apache.ojb.broker.core.PersistenceBrokerImpl.getIteratorFromQuery(PersistenceBrokerImpl.java:1619)
> at
>
org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:120)
> at
>
org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:264)
> ... 66 more
> Caused by: java.sql.SQLException: ORA-00932: inconsistent datatypes:
> expected - got CLOB
>
> at
>
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:158)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
> at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:626)
> at
>
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:182)
> at
>
oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:774)
> at
>
oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:977)
> at
>
oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:817)
> at
>
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1094)
> at
>
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3015)
> at
>
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3056)
> at
>
org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl.java:294)
> ... 73 more
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
