XPages - Refresh JDBC data from client side

Question

I would like to refresh a Repeat Control triggered by client side Javascript. To make it interesting, my datasource is a JDBC query which is why I didn't just do a partial refresh. I've seen pages about using an XHR request to do this, but I don't see how to refresh the JDBC data to capture the new info. I can refresh the repeat control with the old data, not the new.

I saw Repeat control refresh error which talks about possibly requiring a timeout because the runtime isn't aware of the new data. I've run the XHR after manually changing something in the DB and waiting a minute, still had the stale info.

Can I update the variable (jdbcPendingSummary) in the RPC call, if not can I call back to the server to trigger the refresh inside the CSJS function?

<xp:this.data>
    <xe:jdbcQuery connectionName="testDB"
        sqlQuery="EXEC ptoGetPendingRequests #{sessionScope.userID}"
        var="jdbcPendingSummary" />
</xp:this.data>

<xe:jsonRpcService id="ptoRPC" serviceName="ptoRPC">
    <xe:this.methods>
        <xe:remoteMethod name="createNewRequest">
            <xe:this.script><![CDATA[
                javaBeanObject.ptoCreateRequest(#{sessionScope.userID}, startDate, endDate, comment, d1,....,d15);
// Can I update the datasource var here?
            ]]></xe:this.script>
            <xe:this.arguments>
                <xe:remoteMethodArg name="startDate" type="string"></xe:remoteMethodArg>
                ..........
                <xe:remoteMethodArg name="d15" type="number"></xe:remoteMethodArg>
            </xe:this.arguments>
        </xe:remoteMethod>
    </xe:this.methods>
</xe:jsonRpcService>

<xp:scriptBlock id="scriptBlock1">
    <xp:this.value><![CDATA[
    function createNewRequest(startDateID, endDateID, commentID, hiddenDivID, requestID) {

        ptoRPC.createNewRequest(dojo.byId(startDateID).value, dojo.byId(endDateID).value, ........).addCallback( function(response) {
            //  ????? Refreshes the Repeat Control, but has the stale data.
            setTimeout(
                function() {
                    XSP.partialRefreshGet(requestID, {onComplete: function(responseData) {    } })
// Or how about updating the datasource var here?

                }, 8000);
        });
    }
    ]]></xp:this.value>
</xp:scriptBlock>

Answer 1

While this may not be a perfect solution, adding scope="request" to the JDBC code below caused the variable to be refreshed when the AJAX call completed.

<xp:this.data>
   <xe:jdbcQuery connectionName="testDB"
       sqlQuery="EXEC ptoGetPendingRequests #{sessionScope.userID}"
       var="jdbcPendingSummary"  scope="request" />
</xp:this.data>

Answer 2

You could achieve that using refresh() method of specific data source. So if you have a panel with some JDBC Query datasource configured - then inside inner view panel you could reference and refresh data via such syntax:

getComponent('some_panel_id_containing_your_datasource').getData().get(0).refresh();

While you could have multiple datasources configured for a panel - referencing to them is starting from 0 index.

Code snippet to demonstrate that technique could be like this (could make more sense if query would use some computed parameter to present different data on refresh):

<xp:panel id="outerContainer">
    <xp:this.data>
        <xe:jdbcQuery var="jdbcQuery1"
            connectionName="derby1">
            <xe:this.sqlQuery><![CDATA[#{javascript:"select * from users";
            }]]></xe:this.sqlQuery>
        </xe:jdbcQuery>
    </xp:this.data>

    <xp:viewPanel rows="10" id="viewPanel1"
        value="#{jdbcQuery1}" indexVar="idx">
        <xp:viewColumn id="viewColumn1" columnName="id"
            displayAs="link">
            <xp:this.facets>
                <xp:viewColumnHeader xp:key="header"
                    id="viewColumnHeader1" value="ID">
                </xp:viewColumnHeader>
            </xp:this.facets>
            <xp:eventHandler event="onclick"
                submit="true" refreshMode="partial" refreshId="outerContainer">

                <xp:this.action><![CDATA[#{javascript:
                    getComponent('outerContainer').getData().get(0).refresh();
                    }]]>
                </xp:this.action>
            </xp:eventHandler>
        </xp:viewColumn>
    </xp:viewPanel>
</xp:panel>