[CONNECTORS-109] Queue status report fails under Derby

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: ManifoldCF 0.1
• Component/s: Framework crawler agent
• Assignee: Karl Wright
• Reporter: Karl Wright
• Created: 17/Sep/10 22:17
• Updated: 24/Nov/10 11:39
• Resolved: 31/Oct/10 19:10

Description

If you try to use the queue status report with Derby as the database, you get the following error:

2010-09-17 18:03:21.558:WARN::Nested in javax.servlet.ServletException: org.apac
he.acf.core.interfaces.ACFException: Database exception: Exception doing query:
Syntax error: Encountered "SUBSTRING" at line 1, column 8.:
org.apache.acf.core.interfaces.ACFException: Database exception: Exception doing
query: Syntax error: Encountered "SUBSTRING" at line 1, column 8.
at org.apache.acf.core.database.Database.executeViaThread(Database.java:
421)
at org.apache.acf.core.database.Database.executeUncachedQuery(Database.j
ava:465)
at org.apache.acf.core.database.Database$QueryCacheExecutor.create(Datab
ase.java:1072)
at org.apache.acf.core.cachemanager.CacheManager.findObjectsAndExecute(C
acheManager.java:144)
at org.apache.acf.core.database.Database.executeQuery(Database.java:167)

at org.apache.acf.core.database.DBInterfaceDerby.performQuery(DBInterfac
eDerby.java:751)
at org.apache.acf.crawler.jobs.JobManager.genQueueStatus(JobManager.java
:5981)
at org.apache.jsp.queuestatus_jsp._jspService(queuestatus_jsp.java:769)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper
.java:377)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:3
13)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511
)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:3
90)
at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.jav
a:216)
at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:1
82)
at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:7
65)
at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:418)

at org.mortbay.jetty.servlet.Dispatcher.forward(Dispatcher.java:327)
at org.mortbay.jetty.servlet.Dispatcher.forward(Dispatcher.java:126)
at org.apache.jasper.runtime.PageContextImpl.doForward(PageContextImpl.j
ava:706)
at org.apache.jasper.runtime.PageContextImpl.forward(PageContextImpl.jav
a:677)
at org.apache.jsp.execute_jsp._jspService(execute_jsp.java:1291)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper
.java:377)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:3
13)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511
)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:3
90)
at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.jav
a:216)
at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:1
82)
at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:7
65)
at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:418)

at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.
java:114)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:1
52)
at org.mortbay.jetty.Server.handle(Server.java:326)
at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:54
2)
at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnectio
n.java:938)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:755)
at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218)
at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
at org.mortbay.jetty.bio.SocketConnector$Connection.run(SocketConnector.
java:228)
at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java (502)

The reason for the error is that Derby does not recognize the SUBSTRING(...) operation, which extracts parts of a string based on a regular expression. In other places in Derby where regular expressions were required, I've been successful in using LIKE clauses instead, but this is harder because a GROUP BY is used that depends on the part of the string selected by the user.