3

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?

  • 1
    Where in Cntrlr_soqlQuery is the invalid field exception occurring? Is it the database.query(query); call? Also, have you tried the Developer Console for running one off SOQL queries and checking the results? – Daniel Ballinger Jun 21 '13 at 2:19
  • Take a look at Dynamic Visualforce Components – Daniel Hoechst Jun 21 '13 at 2:46
  • The controller isn't throwing an exception. The visualforce page is throwing an error trying to display the results – Scott Pelak Jun 21 '13 at 14:13
  • @DanielHoechst I came across Dynamic Components before, and I'll take a closer look to see what it can do. – Scott Pelak Jun 21 '13 at 14:16
4

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:

enter image description here

  • 1
    This is exactly what I'm trying to do. Heh, I was working too hard! – Scott Pelak Jun 21 '13 at 14:20
  • In a way its a shame - Visualforce Dynamic Bindings and Dynamic Visualforce Components are a nice topic :-) – Stephen Willcock Jun 21 '13 at 14:38
  • 1
    Sometimes we get so deep in the technical details of what is trying to be done, we forget to ask why is it trying to be done? – Daniel Hoechst Jun 21 '13 at 19:42
  • Ohh Stephen, you and your Dynamic Visualforce Components :) jesting aside, I really would like to read yours ideas about Dynamic Visualforce Components. Do you have a blog? – Daniel Blackhall Jun 22 '13 at 2:09
  • Ah yes Daniel (Blackhall) our previous discussion of DVCs! I do have a blog, but haven't touched the topic of DVCs yet. If we wanted to build a solution to the original question then this would be a pretty good use case for DVCs, so I do like the idea of using it as a example for a blog post... – Stephen Willcock Jun 22 '13 at 6:59
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 :)

0

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

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.