XPages REST Service with JDBC data

Question

I am trying to get the REST Service Data Access control to work with some JDBC data. Eventually, this will all go in a Dojo Datagrid. But somehow, I can't make it work and I can't figure out what is wrong.

Here is the code I have:

<?xml version="1.0" encoding="UTF-8"?>
<xp:view xmlns:xp="http://www.ibm.com/xsp/core"
    xmlns:xe="http://www.ibm.com/xsp/coreex">

    <xe:restService id="restService1" pathInfo="results">
        <xe:this.service>
            <xe:jdbcQueryJsonService contentType="application/json"
                connectionName="oracle">
                <xe:this.sqlQuery><![CDATA[select postal_code, province cifadmin.postal_code_search_mv where postal_code='J6Y1R4'"]]></xe:this.sqlQuery>
            </xe:jdbcQueryJsonService>
        </xe:this.service>
    </xe:restService>

</xp:view>

All I get is an error 500 when I access the page with the /results, and I don't have access to the log.nsf, so no clue to what is going on. Database table and columns names are valid.

Can anyone shed a bit of light on this?

As usual, thansk for your help.

Did /results return the whole error message as JSON? If so please post it here. — Knut Herrmann
Do you have access to the log files in "C:\<server-install>\Domino\data\IBM_TEHCNICAL_SUPPORT"? If so look for a log file prefixed with "xpages_exc" for more details on the problem, and post them here — Brian Gleeson - IBM

Ben Dubuc Ben Dubuc · Accepted Answer · 2014-12-15T17:58:15

That was a stupid, worked too many hours in a day error: the FROM was missing from my SQL query!!!

One thing that you may want to check too is that when you assign field names to the dojo datagrid columns, these names ARE case sensitive.

Working now... all that remains is formatting and some basic DojoDataGrid properties to set

XPages REST Service with JDBC data

1 Answers