5

I'm trying to use sAjaxSource with server side processing from Apex class which will provide JSON as source. First I was trying simple example, which won't work and won't output any error (not in Salesforce and not in JS console), I'm just getting empty JQuery DataTable.

Visualforce page:

<apex:page controller="vfDBTestController">

    <apex:includeScript value="https://ajax.googleapis.com/ajax/libs/jquery/2.1.0/jquery.min.js" />
    <apex:stylesheet value="https://ajax.googleapis.com/ajax/libs/jqueryui/1.10.4/themes/smoothness/jquery-ui.css"/>
    <apex:includeScript value="https://ajax.googleapis.com/ajax/libs/jqueryui/1.10.4/jquery-ui.min.js" />

    <apex:includeScript value="{!URLFOR($Resource.DataTables_1_9_4, 'DataTables-1.9.4/media/js/jquery.dataTables.js')}"/>
    <apex:stylesheet value="{!URLFOR($Resource.DataTables_1_9_4, 'DataTables-1.9.4/media/css/jquery.dataTables_themeroller.css')}" />  

    <script type="text/javascript">
        var j$ = jQuery.noConflict();
        j$(document).ready( function () 
        {
            j$('#tblid').dataTable(
            {
                "bProcessing"   : true,
                "sAjaxSource"   : {!JSONDataSource}
            } );
        } );
    </script>

    <apex:form >

        <table cellpadding="0" cellspacing="0" border="0" class="display" id="tblid">
            <thead>
                <th>cell1</th>
                <th>cell2</th>
            </thead>
            <tbody>

            </tbody>
        </table>

    </apex:form>

</apex:page> 

Apex:

public with sharing class vfDBTestController 
{     
    public vfDBTestController(){}

    public String getJSONDataSource()
    {
        List<Account> lstAaData = [SELECT Id, Name FROM Account];

        // Create a JSONGenerator object.
        // Pass true to the constructor for pretty print formatting.
        JSONGenerator gen = JSON.createGenerator(true);

        // Write data to the JSON string.
        gen.writeStartObject();
        gen.writeFieldName('aaData');
        gen.writeStartArray();

        for (Account acc : lstAaData)
        {
            gen.writeStartObject();
            gen.writeStringField('cell1', acc.Name);
            gen.writeStringField('cell2', acc.Id);
            gen.writeEndObject();
        }
        gen.writeEndArray();
        gen.writeEndObject();

        String JSONString = gen.getAsString();
        system.debug('JSONString: ' + JSONString);
        return JSONString;
    }

}

JSON output from system debug:

{"aaData":[{"cell1" : "GenePoint","cell2" : "001b000000MTqJJAA1"},{"cell1" : "United Oil & Gas, UK","cell2" : "001b000000MTqJKAA1"},{"cell1" : "United Oil & Gas, Singapore","cell2" : "001b000000MTqJLAA1"}]}
2

I have found an answer with help from here. I will give a small example, which shows how to use this technique related to JQuery DataTable.

Note: this is only small example that shows how one can communicate with JQuery DataTable and Apex, to get a good grasp on the matter. Hope it will help more people who trying this.

Ajax Response VF page:

<apex:page controller="AjaxRespController"  action="{!retrieveData}"
    contentType="application/x-JavaScript; charset=utf-8" showHeader="false" standardStylesheets="false" sidebar="false">
{!JSONString}
</apex:page>

AjaxRespController:

public class AjaxRespController 
{
    public String JSONString {get;set;}

    public AjaxRespController() {}

    /** invoked on an Ajax request */   
    public void retrieveData() 
    {
        // We need those parameters to work with JQuery DataTable.
        Map<String, String> params = ApexPages.currentPage().getParameters();
        for (String key : params.keySet())
        {
            system.debug('Key: ' + key + ', Value: ' + params.get(key));
        }
        List<Account> lstAaData = [SELECT Id, Name FROM Account LIMIT 10];

        // Create a JSONGenerator object.
        // Pass true to the constructor for pretty print formatting.
        JSONGenerator gen = JSON.createGenerator(true);

        // Write data to the JSON string.
        gen.writeStartObject();
        gen.writeNumberField('sEcho', Integer.valueOf(params.get('sEcho')));
        gen.writeNumberField('iTotalRecords', 14);
        gen.writeNumberField('iTotalDisplayRecords', 14);
        gen.writeFieldName('aaData');       
        gen.writeStartArray();
        for (Account acc : lstAaData)
        {       
            gen.writeStartArray();
            gen.writeString(acc.Name);
            gen.writeString(acc.Id);
            gen.writeEndArray();

        }

        gen.writeEndArray();
        gen.writeEndObject();

        JSONString = gen.getAsString();     
    }

}

VF client page:

<apex:page>
    <apex:includeScript value="https://ajax.googleapis.com/ajax/libs/jquery/2.1.0/jquery.min.js" />
    <apex:stylesheet value="https://ajax.googleapis.com/ajax/libs/jqueryui/1.10.4/themes/smoothness/jquery-ui.css"/>
    <apex:includeScript value="https://ajax.googleapis.com/ajax/libs/jqueryui/1.10.4/jquery-ui.min.js" />

    <apex:includeScript value="{!URLFOR($Resource.DataTables_1_9_4, 'DataTables-1.9.4/media/js/jquery.dataTables.js')}"/>
    <apex:stylesheet value="{!URLFOR($Resource.DataTables_1_9_4, 'DataTables-1.9.4/media/css/jquery.dataTables_themeroller.css')}" />  

    <script type="text/javascript">

        var j$ = jQuery.noConflict();
        j$(document).ready( function () 
        {           
            j$('#tblid').dataTable(
            {
                "bProcessing"   : true,
                "bServerSide"   : true,
                "sAjaxSource"   : '{!$Page.AjaxResponder}?core.apexpages.devmode.url=1'
            } );
        } );

    </script>

    <apex:form >
        <table cellpadding="0" cellspacing="0" border="0" class="display" id="tblid">
            <thead>
                <th>Name</th>
                <th>Id</th>
            </thead>
            <tbody>

            </tbody>
        </table>
    </apex:form>
</apex:page>
|improve this answer|||||
  • 1
    Glad to see you've found a solution. Fyi, I've seen comments from Salesforce about query string parameters (the "core.apexpages.devmode.url" in your case) not being part of their API and so subject to change. But I question if they can remove/change such parameters if many people are using them. (I also did notice that DataTables supports a fnServerData property that in theory would allow the JavaScript generated by an "@RemoteAction" to be called.) – Keith C Mar 13 '14 at 22:04
  • Thank you for all your help. I also read about fnServerData and was thinking about using "@RemoteAction" with it, but I still need to use a URL in the sAjaxSource which is the source given to the fnServerData, so as I see it am still back to the original problem I had. – GoldenAxe Mar 14 '14 at 10:40
  • Yeah I tried fnServerData for a few minutes too and couldn't get it to work. I might give it another go soon - seems like it ought to be possible. – Keith C Mar 14 '14 at 10:50
  • 2
    Fyi - did get the fnServerData thing to work - see force201.wordpress.com/2014/03/15/…. – Keith C Mar 15 '14 at 18:06
2

Based on DataTables AJAX source example, sAjaxSource should be a URL that returns the JSON not a literal copy of the JSON. I suggest you dig into the DataTables documentation further to figure out the right data loading mode as you experiment.

If you did want to emit JSON into the generated page, you would have to add quotes like this '{!JSONDataSource}'; view the source of the generated page to make sure it looks right. I also wouldn't expect things like "aaData" to be in your JSON.

I've used DataTables and found it to be excellent and to work well in Salesforce.

PS

I've posted some sample code that uses sAjaxSource here http://force201.wordpress.com/2014/03/15/connecting-datatables-to-json-generated-by-apex/.

|improve this answer|||||
  • Thank for your info. The quotes don't work (Uncaught SyntaxError: Unexpected token ILLEGAL), and I'm familiar with my browser dev tools, as I was mentioned I don't see any error in the javascript debug console (prior to the change you suggested). I do understand from what you said that I need to write a web service which will provide the JSON, I'll try it and get back here to update. – GoldenAxe Mar 12 '14 at 9:36
  • I should have suggested single quotes as double quotes are use in the JSON - the JSON needs to be interpreted by JavaScript as a single string. I've edited the answer to reflect this. Missed your comment about the JS console too. – Keith C Mar 12 '14 at 9:38
  • It gave me the same error as double quotes. I think your suggestion about providing the source as URL is the solution, so I'm building a web service with apex code that will provide the JSON, and I'll get back here to update. – GoldenAxe Mar 12 '14 at 9:50
  • Update: When using apex Rest Service - "sAjaxSource" : "https://eu2.salesforce.com/services/apexrest/test/test_rest_api", I have a probelm described here link, but that not helping me getting a URL with JSON output within my ORG, to use server-side processing, any more suggestions will be most helpful. – GoldenAxe Mar 12 '14 at 15:04
  • I'm unclear what your problem is exactly; consuming JSON generated in the same server by an @RestResource that your page comes from requires no special mechanism and you are automatically authenticated. Perhaps you should post a separate question detailing the specific problem including the current code and any error reported? (You can link to this question as background info.) – Keith C Mar 12 '14 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.