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!

Hi Shean, I don't know the <xe:jdbcQueryJsonService> but today I was playing with another type (viewJsonService) of rest service. I got an error 500 until I specified columns to pull. Just adding 'defaultColumns="true"' fixed the error, and I was able to see my JSON data via the pathInfo. If you have that property, then try to add something so it knows what columns to pull. I know you specified in your SQL, but perhaps the control needs more than that. — Steve Zavocki
That control does not have a column parameter and doesn't allow me to enter "defaultColumns=true" in the source. — Shean McManus

Cameron Gregor Cameron Gregor · Accepted Answer · 2016-09-12T13:27:36

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! :)

REST Service: xe:jdbcQueryJsonService Error

1 Answers