[Dspace-tech] Oracle and DSpace 1.5.1 and "ILIKE" (again)

[Pottinger, Hardy J.]

Hi, we are currently running DSpace 1.5.1 (release install, not source)
in production, with Oracle as our back end. A message thread from March
25, 2008 to the dspace-tech list identifies the same problem we're
having (Oracle doesn't support PostgrSQL's ILIKE operator), and posts a
solution (DB-agnostic SQL replacement for the ILIKE operator). I also
see that SVN commit 2870, also made on March 25, asserts to solve this
problem.

However, in our experience, running 1.5.1, we still see this error. A
recap: in the XMLUI, when one clicks the
"People" link under "Access Control", the following exception is thrown:

java.sql.SQLException: ORA-00920: invalid relational operator

if you look in the trace, the source of the exception is:

at org.dspace.eperson.EPerson.searchResultCount(EPerson.java:326)

So, my question is, did SVN commit 2870 get rolled back before 1.5.1 was
released?

To work around this issue, we've been using the JSPUI as an admin
interface. Which actually is going to come in handy since we're in the
process of implementing the Minho stats add-on, and that only supports a
JSPUI interface. However, it has always bugged me that a big part of the
XMLUI admin interface just dies whenever we try to use it.

Are there any other institutions running DSpace and using Oracle for a
back end that have noticed this behavior? What was your solution?

--
HARDY POTTINGER <potti...@umsystem.edu>
University of Missouri Library Systems
http://lso.umsystem.edu/~hardy/
"No matter how far down the wrong road you've gone,
turn back." --Turkish proverb

[Pottinger, Hardy J.]

Hi, I thought I'd bump this, and try a rephrase. See below for details,
but I'd really like to know if anyone is using DSpace 1.5.1, with Oracle
as the back end db, and the XMLUI as an interface? If so, I'd like to
know if you've encountered the behavior detailed below, and if so, are
you living with the behavior, or did you come up with a solution? Our
approach has been to use XMLUI as a public interface and JSPUI as our
admin interface. But, I'd love to have the XMLUI as an option for an
admin interface.

--Hardy

------------------------------------------------------------------------
> ------
> Stay on top of everything new and different, both inside and
> around Java (TM) technology - register by April 22, and save
> $200 on the JavaOne (SM) conference, June 2-5, 2009, San Francisco.
> 300 plus technical and hands-on sessions. Register today.
> Use priority code J9JMT32. http://p.sf.net/sfu/p
> _______________________________________________
> DSpace-tech mailing list
> DSpac...@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/dspace-tech

[Pottinger, Hardy J.]

Hi, I thought I'd try asking this one more time. See below for
background. After reading this message in the thread I mentioned
before...

http://www.mail-archive.com/dspac...@lists.sourceforge.net/msg03793.h
tml

...I dove into the code we're using in
production--[dspace]/dspace-api/src/main/java/org/dspace/eperson/EPerson
.java--and discovered that the ILIKE code has indeed been replaced.
There is also a larger workaround for the LIMIT and OFFSET relational
operators (mentioned in the message above as also presenting a problem
for Oracle). All good things, however, the stack trace for the error
we're receiving definitely points to the error being somewhere in the
SQL in EPerson.java.

I'm going to attach the stack trace at the end of this note. But, my
question is, is anyone else noticing this behavior? Anyone using Oracle
and XMLUI? I wish the error message from Oracle were a bit more
detailed... I'd like to know what it's complaining about.

Anyone out there have any ideas on this?

Here's the stack trace:

org.apache.cocoon.ProcessingException: Failed to process pipeline
at <map:serialize type="xml"> -
file:/dspace/webapps/xmlui/aspects/aspects.xmap:126:31
at <map:generate type="file"> -
file:/dspace/webapps/xmlui/aspects/aspects.xmap:125:55
at <map:serialize type="xml"> -
resource://aspects/ArtifactBrowser/sitemap.xmap:304:31
at <map:transform type="Navigation"> -
resource://aspects/ArtifactBrowser/sitemap.xmap:104:38
at <map:generate> -
resource://aspects/ArtifactBrowser/sitemap.xmap:92:19
at <map:serialize type="xml"> -
resource://aspects/Administrative/sitemap.xmap:813:31
at <map:transform type="ManageEPeopleMain"> -
resource://aspects/Administrative/sitemap.xmap:317:54
at <map:transform type="Navigation"> -
resource://aspects/Administrative/sitemap.xmap:266:38
at <map:transform type="SystemwideAlerts"> -
resource://aspects/Administrative/sitemap.xmap:265:44
at <map:generate> -
resource://aspects/Administrative/sitemap.xmap:264:19
at <map:serialize type="xml"> -
resource://aspects/EPerson/sitemap.xmap:331:31
at <map:transform type="Navigation"> -
resource://aspects/EPerson/sitemap.xmap:136:38
at <map:generate> -
resource://aspects/EPerson/sitemap.xmap:125:19
at <map:serialize type="xml"> -
resource://aspects/Submission/sitemap.xmap:296:38
at <map:transform type="Navigation"> -
resource://aspects/Submission/sitemap.xmap:139:45
at <map:generate> -
resource://aspects/Submission/sitemap.xmap:136:26
at <map:serialize type="xml"> -
file:/dspace/webapps/xmlui/aspects/aspects.xmap:115:34
at <map:transform type="PageNotFound"> -
file:/dspace/webapps/xmlui/aspects/aspects.xmap:114:43
at <map:generate> -
file:/dspace/webapps/xmlui/aspects/aspects.xmap:113:22
at <map:serialize type="xhtml"> -
file:/dspace/webapps/xmlui/themes/mospace/sitemap.xmap:177:34
at <map:transform type="i18n"> -
file:/dspace/webapps/xmlui/themes/mospace/sitemap.xmap:171:33
at <map:transform> -
file:/dspace/webapps/xmlui/themes/mospace/sitemap.xmap:166:40
at <map:transform type="IncludePageMeta"> -
file:/dspace/webapps/xmlui/themes/mospace/sitemap.xmap:151:45
at <map:generate type="file"> -
file:/dspace/webapps/xmlui/themes/mospace/sitemap.xmap:137:55
at <map:mount> -
file:/dspace/webapps/xmlui/themes/themes.xmap:63:45
at <map:mount> - file:/dspace/webapps/xmlui/sitemap.xmap:345:73
at
org.apache.cocoon.ProcessingException.throwLocated(ProcessingException.j
ava:144)
at
org.apache.cocoon.components.pipeline.AbstractProcessingPipeline.handleE
xception(AbstractProcessingPipeline.java:951)
at
org.apache.cocoon.components.pipeline.impl.AbstractCachingProcessingPipe
line.processXMLPipeline(AbstractCachingProcessingPipeline.java:381)
at
org.apache.cocoon.components.pipeline.AbstractProcessingPipeline.process
(AbstractProcessingPipeline.java:779)
at
org.apache.cocoon.components.source.impl.SitemapSource.toSAX(SitemapSour
ce.java:412)
at
org.apache.cocoon.components.source.SourceUtil.toSAX(SourceUtil.java:100
)
at
org.apache.cocoon.components.source.SourceUtil.parse(SourceUtil.java:320
)
at
org.apache.cocoon.generation.FileGenerator.generate(FileGenerator.java:1
15)
at
org.apache.cocoon.components.pipeline.impl.AbstractCachingProcessingPipe
line.processXMLPipeline(AbstractCachingProcessingPipeline.java:335)
at
org.apache.cocoon.components.pipeline.AbstractProcessingPipeline.process
(AbstractProcessingPipeline.java:779)
at
org.apache.cocoon.components.source.impl.SitemapSource.toSAX(SitemapSour
ce.java:412)
at
org.apache.cocoon.components.source.SourceUtil.toSAX(SourceUtil.java:100
)
at
org.apache.cocoon.components.source.SourceUtil.parse(SourceUtil.java:320
)
at
org.apache.cocoon.generation.FileGenerator.generate(FileGenerator.java:1
15)
at
org.apache.cocoon.components.pipeline.impl.AbstractCachingProcessingPipe
line.processXMLPipeline(AbstractCachingProcessingPipeline.java:335)
at
org.apache.cocoon.components.pipeline.AbstractProcessingPipeline.process
(AbstractProcessingPipeline.java:779)
at
org.apache.cocoon.components.source.impl.SitemapSource.toSAX(SitemapSour
ce.java:412)
at
org.apache.cocoon.components.source.SourceUtil.toSAX(SourceUtil.java:100
)
at
org.apache.cocoon.components.source.SourceUtil.parse(SourceUtil.java:320
)
at
org.apache.cocoon.generation.FileGenerator.generate(FileGenerator.java:1
15)
at
org.apache.cocoon.components.pipeline.AbstractProcessingPipeline.process
XMLPipeline(AbstractProcessingPipeline.java:578)
at
org.apache.cocoon.components.pipeline.impl.AbstractCachingProcessingPipe
line.processXMLPipeline(AbstractCachingProcessingPipeline.java:281)
at
org.apache.cocoon.components.pipeline.AbstractProcessingPipeline.process
(AbstractProcessingPipeline.java:779)
at
org.apache.cocoon.components.source.impl.SitemapSource.toSAX(SitemapSour
ce.java:412)
at
org.apache.cocoon.components.source.SourceUtil.toSAX(SourceUtil.java:100
)
at
org.apache.cocoon.components.source.SourceUtil.parse(SourceUtil.java:320
)
at
org.apache.cocoon.generation.FileGenerator.generate(FileGenerator.java:1
15)
at
org.apache.cocoon.components.pipeline.AbstractProcessingPipeline.process
XMLPipeline(AbstractProcessingPipeline.java:578)
at
org.apache.cocoon.components.pipeline.impl.AbstractCachingProcessingPipe
line.processXMLPipeline(AbstractCachingProcessingPipeline.java:281)
at
org.apache.cocoon.components.pipeline.AbstractProcessingPipeline.process
(AbstractProcessingPipeline.java:779)
at
org.apache.cocoon.components.source.impl.SitemapSource.toSAX(SitemapSour
ce.java:412)
at
org.apache.cocoon.components.source.SourceUtil.toSAX(SourceUtil.java:100
)
at
org.apache.cocoon.components.source.SourceUtil.parse(SourceUtil.java:320
)
at
org.apache.cocoon.generation.FileGenerator.generate(FileGenerator.java:1
15)
at
org.apache.cocoon.components.pipeline.AbstractProcessingPipeline.process
XMLPipeline(AbstractProcessingPipeline.java:578)
at
org.apache.cocoon.components.pipeline.impl.AbstractCachingProcessingPipe
line.processXMLPipeline(AbstractCachingProcessingPipeline.java:281)
at
org.apache.cocoon.components.pipeline.AbstractProcessingPipeline.process
(AbstractProcessingPipeline.java:779)
at
org.apache.cocoon.components.source.impl.SitemapSource.toSAX(SitemapSour
ce.java:412)
at
org.apache.cocoon.components.source.SourceUtil.toSAX(SourceUtil.java:100
)
at
org.apache.cocoon.components.source.SourceUtil.parse(SourceUtil.java:320
)
at
org.apache.cocoon.generation.FileGenerator.generate(FileGenerator.java:1
15)
at
org.apache.cocoon.components.pipeline.AbstractProcessingPipeline.process
XMLPipeline(AbstractProcessingPipeline.java:578)
at
org.apache.cocoon.components.pipeline.impl.AbstractCachingProcessingPipe
line.processXMLPipeline(AbstractCachingProcessingPipeline.java:281)
at
org.apache.cocoon.components.pipeline.AbstractProcessingPipeline.process
(AbstractProcessingPipeline.java:480)
at
org.apache.cocoon.components.treeprocessor.sitemap.SerializeNode.invoke(
SerializeNode.java:120)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.i
nvoke(PreparableMatchNode.java:130)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(P
ipelineNode.java:142)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(
PipelinesNode.java:92)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process
(ConcreteTreeProcessor.java:234)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process
(ConcreteTreeProcessor.java:176)
at
org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreePro
cessor.java:252)
at
org.apache.cocoon.components.treeprocessor.sitemap.MountNode.invoke(Moun
tNode.java:117)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:46)
at
org.apache.cocoon.components.treeprocessor.sitemap.MatchNode.invoke(Matc
hNode.java:107)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(P
ipelineNode.java:142)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(
PipelinesNode.java:92)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process
(ConcreteTreeProcessor.java:234)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process
(ConcreteTreeProcessor.java:176)
at
org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreePro
cessor.java:252)
at
org.apache.cocoon.components.treeprocessor.sitemap.MountNode.invoke(Moun
tNode.java:117)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(P
ipelineNode.java:142)
at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.
invokeNodes(AbstractParentProcessingNode.java:68)
at
org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(
PipelinesNode.java:92)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process
(ConcreteTreeProcessor.java:234)
at
org.apache.cocoon.components.treeprocessor.ConcreteTreeProcessor.process
(ConcreteTreeProcessor.java:176)
at
org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreePro
cessor.java:252)
at org.apache.cocoon.Cocoon.process(Cocoon.java:686)
at
org.apache.cocoon.servlet.CocoonServlet.service(CocoonServlet.java:1153)
at
org.dspace.app.xmlui.cocoon.DSpaceCocoonServlet.service(DSpaceCocoonServ
let.java:215)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Applica
tionFilterChain.java:269)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilt
erChain.java:188)
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValv
e.java:213)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValv
e.java:174)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java
:127)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java
:117)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.
java:108)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:1
74)
at
org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:200)
at
org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:283)
at
org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:773)
at
org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:
703)
at
org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.
java:895)
at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool
.java:689)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.SQLException: ORA-00920: invalid relational operator

at
org.dspace.app.xmlui.cocoon.AbstractDSpaceTransformer.handleException(Ab
stractDSpaceTransformer.java:140)
at
org.dspace.app.xmlui.wing.AbstractWingTransformer.startElement(AbstractW
ingTransformer.java:279)
at
org.apache.cocoon.components.sax.XMLTeePipe.startElement(XMLTeePipe.java
:83)
at
org.apache.cocoon.xml.AbstractXMLPipe.startElement(AbstractXMLPipe.java:
94)
at
org.dspace.app.xmlui.wing.AbstractWingTransformer.startElement(AbstractW
ingTransformer.java:271)
at
org.apache.cocoon.xml.AbstractXMLPipe.startElement(AbstractXMLPipe.java:
94)
at
org.dspace.app.xmlui.wing.AbstractWingTransformer.startElement(AbstractW
ingTransformer.java:271)
at
org.apache.cocoon.components.EnvironmentChanger.startElement(Environment
Stack.java:139)
at
org.apache.cocoon.components.sax.XMLTeePipe.startElement(XMLTeePipe.java
:83)
at
org.apache.cocoon.xml.AbstractXMLPipe.startElement(AbstractXMLPipe.java:
94)
at
org.dspace.app.xmlui.wing.AbstractWingTransformer.startElement(AbstractW
ingTransformer.java:271)
at
org.apache.cocoon.components.EnvironmentChanger.startElement(Environment
Stack.java:139)
at
org.apache.cocoon.components.sax.XMLTeePipe.startElement(XMLTeePipe.java
:83)
at
org.apache.cocoon.components.sax.XMLByteStreamInterpreter.parse(XMLByteS
treamInterpreter.java:96)
at
org.apache.cocoon.components.sax.XMLByteStreamInterpreter.deserialize(XM
LByteStreamInterpreter.java:72)
at
org.apache.cocoon.components.pipeline.impl.AbstractCachingProcessingPipe
line.processXMLPipeline(AbstractCachingProcessingPipeline.java:333)
... 88 more
Caused by: java.sql.SQLException: ORA-00920: invalid relational operator

at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:11
2)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
at
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.jav
a:216)
at
oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedSt
atement.java:810)
at
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.
java:1039)
at
oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPrepared
Statement.java:850)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.
java:1134)
at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePrepare
dStatement.java:3339)
at
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedSt
atement.java:3384)
at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(Delegat
ingPreparedStatement.java:92)
at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(Delegat
ingPreparedStatement.java:92)
at
org.dspace.storage.rdbms.DatabaseManager.query(DatabaseManager.java:209)
at
org.dspace.storage.rdbms.DatabaseManager.querySingle(DatabaseManager.jav
a:279)
at
org.dspace.eperson.EPerson.searchResultCount(EPerson.java:326)
at
org.dspace.app.xmlui.aspect.administrative.eperson.ManageEPeopleMain.add
Body(ManageEPeopleMain.java:153)
at
org.dspace.app.xmlui.wing.AbstractWingTransformer.startElement(AbstractW
ingTransformer.java:256)
... 102 more