Dynamically display results from an unkown SOQL query

Question

I want to do some investigation of how the data looks in my Salesforce org, so I'd like to have a simple Visualforce page that takes an query string as an input and then display the results in a pageBlockTable (or whatever will work)

<apex:page controller="Cntrlr_soqlQuery">
    <apex:form >
        <apex:pageBlock title="Enter your SOQL Query">
            <apex:pageMessages ></apex:pageMessages>
            <apex:inputTextarea id="query" value="{!query}" styleClass="query" />
            <br/>
            <apex:commandButton action="{!runQuery}" value="Submit Query"/>
            <br/>
            <apex:pageBlockTable value="{!result}" var="r" rendered="{!showResult}">
                <apex:repeat value="{!fields}" var="f" rendered="{!showResult}">
                    <apex:column headerValue="{!f}">{!r[f]}</apex:column>
                </apex:repeat>
            </apex:pageBlockTable>
        </apex:pageBlock>
    </apex:form>
</apex:page>

with controller

public class Cntrlr_soqlQuery { 

    public string query {get;set;}
    public list<string> fields {get;set;}
    public list<sObject> result {get;set;}
    public boolean showResult {get;set;}

    public Cntrlr_soqlQuery(){
        showResult=false;
    }//END init

    public pageReference runQuery(){
        fields=query.toLowerCase().substringBefore('from').substringAfter('select').split(',');
        for(string f:fields){
            if(f.contains(')')  //for AggregateResults
                f=f.substringAfter(')');
            f=f.deleteWhiteSpace();
        }
        try{
            result=database.query(query);
        } catch(exception e){
            ApexPages.addMessage(new ApexPages.Message(ApexPages.severity.ERROR,'Malformed SOQL query.  Error: '+e));
        }
        showResult=true;
        return null;
    }//END runQuery

}//END class

But I get errors like "Exception: Invalid field *** for ####"

I'm guessing one reason this isn't working is because the VF page doesn't know what type the data is (string, integer, decimal, etc.), but I can't figure it out.

---

One idea I have is to create a map> to send each record to an integer and then each field to a string (of that field name and the data-type cast as a string)...but that isn't as elegant as I'm hoping for.

Any ideas?

Answer 1

I appreciate that this does not meet the title of the question, but perhaps does satisfy the underlying requirement: "I want to do some investigation of how the data looks in my Salesforce org"

Have you seen the Salesforce Developer Console?
Your Name > Developer Console

You can run queries and get a tabulated output:

Answer 2

It's not a fix for your code, but there is a project on Google Code which already does this:

soql-query-tool

It performs all operations using the ajax toolkit. Meaning there is no Apex Controller, it is all done using javascript. This is a big change from how you were looking to solve the problem, but someone else has done all the hard work :)

Answer 3

Another alternative to rolling your own: Try out the Developer Workbench: