Oh my god. It's full of code!

Salesforce SOQL Query to JSON

So this is another cool little tool I whipped up. Sometimes I’ll be working on an external app that requires data from Salesforce. I just want a little bit of data without having to deal with complexities of the API. Maybe I don’t have access to a server side language, just trying to work with javascript or something. Whatever the case I wanted a tool where I could simple construct a url that contained an SOQL query, and the resulting content would be JSON that contained my data. The following is that tool.

For those who just want the code, download it from the projects page. It consists of a visualforce page, an apex controller, and a sample javascript file. Simple host the visualforce page and apex controller in a salesforce site (make the publically available basically). Then just call to the visualforce page, and pass the query you want, and if you want, the name of a callback function (used for cross domain data fetching, using JSONP).

Really it’s pretty simple. Use javascript to create a request with the requested query string, send it to the visualforce page, the apex controller attached to that page takes the query in the url, runs it and encodes the results in JSON. Return the JSON (wrapped in a callback function if desired, which it will be if you are doing anything cross domain) and then do whatever with the results.

Download the package here: QueryToJson.zip

Or try the example here Example

Also I’ll post the code here incase the file hosting goes down.

Controller

public class queryToJSON {
    
    public String returnString;
    /** invoked on an Ajax request */    
    public void getJson() 
    {
        
        //There should be a parameter called QueryString in the URL.
        Map<string,string> params = ApexPages.currentPage().getParameters();
        String queryString = EncodingUtil.urlDecode(params.get('queryString'), 'UTF-8');
        returnString = '{ "queryString": "'+ queryString +'", ';
        
        if(params.get('queryString').length() > 1)
        {    
            try
            {             
                //Dynamic SOQL Query based off URL queryString param. use String.escapeSingleQuotes to help
                //prevent SOQL injection.                 
                List<sObject> records = Database.query('select ' + queryString + ' limit 2000');
    
                returnString = returnString + ' "returnStringSet": [';
                        
                if (!records.isEmpty()) {
         
                    //This is a mapping of the column names returned by the query
                    Map<String, Schema.SObjectField> columns = records.getSObjectType().getDescribe().fields.getMap();
                    
                    
                    for (sObject c : records) 
                    {
                        //Now for every column in the query, I need to construct a new JSON "element"
                        //I can do that statically by typing something like this
                        //cjson.putOpt('"id"', new JSONObject.value(c.Id));

                        returnString = returnString + '{';
                        for(String columnName : columns.keySet()) 
                        {   
                             try 
                             {
                                  
                                 //Try to get the value of the column name. If it isn't there, then it tosses
                                 //an error, no big deal.
                                 
                                 string cellVal = String.valueOf( c.get(columnName));
                                returnString = returnString + '"'+columnName+'": "'+cellVal.replace('"','\'')+'",'; 
                             } 
                             catch(System.Exception ex) 
                             {
                                 //returnString = returnString + '"'+columnName+'": "'+ex.getMessage()+'",';
                             }
                             
                             
                        }
                        returnString = returnString.substring(0, returnString.length() - 1);
                        returnString = returnString + '},';  
                                                   
                    }  
                    returnString = returnString.substring(0, returnString.length() - 1); 
                }
                else
                {
                    returnString = returnString + '"No Records Found For Query: select ' +params.get('queryString')+ ' LIMIT 500 "';
                }
            }
            catch(Exception ex)
            {
                 returnString = returnString + '"ERROR": "'+ex.getMessage()+'"';
            }
        }
        returnString = returnString + ']}';
        
        //If this was invoked via JSONP (requires  a callback function, set that up now)
        string callBack = params.get('callBack');
        if(callBack != null)
        { 
            returnString = callBack + '(' + returnString + ');';
        }
         
    }

    public String getResult() 
    {
        
        return returnString; 
       
    }       
}

Page

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

Example

<html>
<head>
    <title>Query to Json test</title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.2/jquery.min.js"></script>
    <script>
    var url = "http://fpitesters.testbed.cs2.force.com/webServices/querytojson";
    
    

    function loadData()
    {
        jQuery('#results').html('<h3>Loading...</h3>');
        var query = jQuery('#queryString').val();
        jQuery.getJSON(url+'?queryString='+query+'&callback=?',function(data)
        {
            var html = '<table>';
            for(var key in data.returnStringSet)
            {
                html += '<tr><td>'+key+'</td><td>'+data.returnStringSet[key].name+'</td></tr>';
            }
            html += '</table>';
            jQuery('#results').html(html);
        });
    
    }
    </script>
</head>
    <body>

        <input type="text" id="queryString" name="queryString" value="name from contact where firstname = 'dan'" size="50"><br>
        <input type="button" name="loadButton" onClick="loadData()" value="Load Query Data">
        <br>
        <h3>Query Result Data</h3>        
           <div id="results">
        
        </div>
    </body>
</html>

Anyway, I hope this helps someone out there. I thought it was pretty cool.

5 responses

  1. David

    Thanks for the post. I am hoping you can help me understand if your example will work as a solution to my problem:

    I have an application where I am looking to query SalesForce from an external website (cross-domain), but I can only use JavaScript and I cannot create a visualforce page. Is this possible?

    Currently, I am attempting to solve this problem by passing the query by URL to a php page that has been set up as an application with remote access in salesforce using an oauth call. Very convoluted but the flow looks something like this….
    main.com/mainpage.html –> newdomain.com/callsf.php?query=somestuff –> https://login.salesforce.com/services/oauth2/authorize?response_type=code&client_id=appid&redirect_uri=newdomain.com/oauthcallback.php

    It has been working but I am hoping to avoid using this php page as a proxy.

    Any help is greatly appreciated!

    August 16, 2011 at 6:33 pm

  2. Pingback: Ask Kenji: Cross domain ajax requests? « I Write Crappy Code

  3. Unquestionably believe that which you said. Your favorite reason seemed to be on
    the web the easiest thing to be aware of. I say to you, I definitely get
    irked while people consider worries that they just do not
    know about. You managed to hit the nail upon the top and also
    defined out the whole thing without having side-effects , people
    could take a signal. Will probably be back to get more. Thanks

    January 15, 2013 at 12:57 am

  4. If some one wants to be updated with latest technologies after that he must be pay a visit this web page and be up to date everyday.

    October 6, 2013 at 11:04 pm

  5. Howdy! This is my first visit to your blog! We are a collection of volunteers and starting a new project in a community in the same niche.

    Your blog provided us valuable information to work on. You have done a marvellous job!

    October 1, 2014 at 12:51 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 605 other followers