REST Service: xe:jdbcQueryJsonService Error

Question

In continuing my experimentation with the relational data controls in the Xpages Extension library I am not attempting to access my Oracle data using a REST service. While I can now connect using the same connection to pull in data for a view panel, when I attempt to use it as a rest service it fails.

REST Service:

<xe:restService id="restService1" pathInfo="myRest">
   <xe:this.service>
      <xe:jdbcQueryJsonService connectionName="cos11p"
            contentType="application/json"
            sqlQuery="SELECT * FROM COM.FAMILY">
      </xe:jdbcQueryJsonService>
    </xe:this.service>
</xe:restService>

cos11p.jdbc file

<jdbc>
<driver>oracle.jdbc.OracleDriver</driver>
<url>jdbc:oracle:thin:@//sodb2p.malt-o-meal.com:1521/cos11p</url>
<user>USERNAME</user>
<password>password</password>
</jdbc> 

When I access the URL to test the REST Service I get:

{
    "code":500,
    "text":"Internal Error",
    "message":"Error while reading the relational data",
    "type":"text",
    "data":"com.ibm.xsp.FacesExceptionEx: Error while reading the relational data\r\n\tat com.ibm.xsp.extlib.relational.jdbc.services.content.JsonJdbcQueryContent.writeJdbcQuery(JsonJdbcQueryContent.java:267)\r\n\tat com.ibm.xsp.extlib.relational.jdbc.rest.query.RestJdbcQueryJsonService.renderServiceJSONGet(RestJdbcQueryJsonService.java:228)\r\n\tat com.ibm.xsp.extlib.relational.jdbc.rest.query.RestJdbcQueryJsonService.renderService(RestJdbcQueryJsonService.java:68)\r\n\tat com.ibm.domino.services.HttpServiceEngine.processRequest(HttpServiceEngine.java:170)\r\n\tat com.ibm.xsp.extlib.component.rest.UIBaseRestService._processAjaxRequest(UIBaseRestService.java:254)\r\n\tat com.ibm.xsp.extlib.component.rest.UIBaseRestService.processAjaxRequest(UIBaseRestService.java:231)\r\n\tat com.ibm.xsp.util.AjaxUtilEx.renderAjaxPartialLifecycle(AjaxUtilEx.java:206)\r\n\tat com.ibm.xsp.webapp.FacesServletEx.renderAjaxPartial(FacesServletEx.java:249)\r\n\tat com.ibm.xsp.webapp.FacesServletEx.serviceAjaxPartialView(FacesServletEx.java:200)\r\n\tat com.ibm.xsp.webapp.FacesServletEx.serviceAjaxPartialViewSync(FacesServletEx.java:169)\r\n\tat com.ibm.xsp.webapp.FacesServletEx.serviceView(FacesServletEx.java:155)\r\n\tat com.ibm.xsp.webapp.FacesServlet.service(FacesServlet.java:160)\r\n\tat com.ibm.xsp.webapp.FacesServletEx.service(FacesServletEx.java:138)\r\n\tat com.ibm.xsp.webapp.DesignerFacesServlet.service(DesignerFacesServlet.java:103)\r\n\tat com.ibm.designer.runtime.domino.adapter.ComponentModule.invokeServlet(ComponentModule.java:576)\r\n\tat com.ibm.domino.xsp.module.nsf.NSFComponentModule.invokeServlet(NSFComponentModule.java:1335)\r\n\tat com.ibm.designer.runtime.domino.adapter.ComponentModule$AdapterInvoker.invokeServlet(ComponentModule.java:853)\r\n\tat com.ibm.designer.runtime.domino.adapter.ComponentModule$ServletInvoker.doService(ComponentModule.java:796)\r\n\tat com.ibm.designer.runtime.domino.adapter.ComponentModule.doService(ComponentModule.java:565)\r\n\tat com.ibm.domino.xsp.module.nsf.NSFComponentModule.doService(NSFComponentModule.java:1319)\r\n\tat com.ibm.domino.xsp.module.nsf.NSFService.doServiceInternal(NSFService.java:662)\r\n\tat com.ibm.domino.xsp.module.nsf.NSFService.doService(NSFService.java:482)\r\n\tat com.ibm.designer.runtime.domino.adapter.LCDEnvironment.doService(LCDEnvironment.java:350)\r\n\tat com.ibm.designer.runtime.domino.adapter.LCDEnvironment.service(LCDEnvironment.java:306)\r\n\tat com.ibm.domino.xsp.bridge.http.engine.XspCmdManager.service(XspCmdManager.java:272)\r\nCaused by: com.ibm.domino.services.ServiceException: \r\n\tat com.ibm.xsp.extlib.relational.jdbc.services.content.JsonJdbcQueryContent.writeJdbcQuery(JsonJdbcQueryContent.java:262)\r\n\t... 24 more\r\nCaused by: com.ibm.domino.services.ServiceException: \r\n\tat com.ibm.xsp.extlib.relational.jdbc.services.content.JsonJdbcQueryContent.writeJdbcQuery(JsonJdbcQueryContent.java:257)\r\n\t... 24 more\r\nCaused by: com.ibm.domino.services.ServiceException: \r\n\tat com.ibm.xsp.extlib.relational.jdbc.services.content.JsonJdbcQueryContent.writeJdbcQuery(JsonJdbcQueryContent.java:245)\r\n\t... 24 more\r\nCaused by: java.lang.NullPointerException\r\n\tat com.ibm.xsp.extlib.relational.jdbc.services.content.JsonJdbcQueryContent.writeJdbcQuery(JsonJdbcQueryContent.java:241)\r\n\t... 24 more\r\n"
}

I've seen examples of this being used to access a derby db but nothing with Oracle. Will it work with Oracle and do I need to configure the service differently to make it work.

Thanks!

Answer 1

I realise this is 2 years ago but I'm just posting in case others come across the same problem.

My best guess is that your result set might contain a null value somewhere?

Even though this is perfectly reasonable to have null values, the jdbcQueryJsonService has a bug in it where it will throw an Error 500 with NullPointerException (as you have here) when it finds a null value.

It occurs when trying to write the value to the json output there is no Null check.

I have submitted a Pull Request (#72) back in May 2016 which fixes the problem. Hopefully it is included in the next release of the Extension Library.

Until then you will have to create special resultsets that coalesce nulls into empty strings or some other non-null default value! Fun fun fun! :)