I signed up for the pre-release trial  of Winter '09 today and am starting to explore the new features. One new or rather a fully supported feature is Dynamic SOQL (it was previously in pilot).  Dynamic SOQL lets you build and execute a query at runtime.  This feature is especially important if you are an ISV (Independent Software Vendor) or someone writing tools that has to work in multiple orgs where it is impossible to know until runtime what the query looks like.  As an example, a general purpose reporting tool would have to let the user select the objects and other criteria at runtime and build a query based on the selected criteria.  It is also useful if you want your code to be flexible and independent of the underlying data model.

Sample Code

Let us look at some code using a simple but somewhat contrived example.  We want to retrieve all Account or Contact records in a particular zip code. 

The VisualForce page

We build a VisualForce page which uses a custom controller called SOQLController.  The page lets a user select either Account or Contact object and type in a zipcode.  We use this information to build a query string, execute it and return back a list of SObjects. We then display the result set on our page.

<apex:page controller="SOQLController" >
<apex:pageBlock title="Test dynamic SOQL">
  <apex:form >
    <apex:outputText value="Select Object to Query on   "></apex:outputText>
    <apex:selectList value="{!objectName}" size="1" >
        <apex:selectOption itemValue="Account" itemLabel="Account" />
        <apex:selectOption itemValue="Contact" itemLabel="Contact" />
    </apex:selectList>
    <apex:outputText value=" Enter Zip Code    " ></apex:outputText>
    <apex:inputText value="{!zipCode}" />
    <apex:commandButton value="Query" action="{!query}" ></apex:commandButton>    
  </apex:form>
</apex:pageBlock>
<apex:pageBlock title="Retrived Ids">
    <apex:dataList value="{!queryResult}" var="r">
        <apex:outputText value ="{!r.Id}" />
    </apex:dataList>
</apex:pageBlock>
</apex:page>

The page looks as shown in Figure 1 below.

The Controller code and the query method

When the user hits the Query button, the query method in the controller is invoked. The code for the controller is shown below.  Since Account and Contact use different field names for storing the zip code we have to set that appropriately. We build the query as a string by concatenating the different pieces.  We then call the query method on the Database singleton which returns a list of SObjects.

public class SOQLController { 
    
    public String objectName { get {return objectName ;} set{ objectName = value ;} }
    public String zipCode { get { return zipCode ;} set { zipCode=value ; } }
    public List queryResult { get {return queryResult; }  set { queryResult = value ; }
    	
    public PageReference query() {
        String zipFieldName ;       
        if( objectName == 'Account' ){
                zipFieldName = 'BillingPostalCode' ;
        }
        else if( objectName == 'Contact' ){
                zipFieldName = 'MailingPostalCode' ;
        }        
        // create the query string 
        String qryString = 'SELECT Id FROM ' + objectName + ' WHERE ' + objectName + '.' + 
        		    zipFieldName + '=' + '\'' + zipCode + '\'' ; 
        // execute the query
        queryResult = Database.query(qryString) ;       
        return null;
    } 
}

The result of running this with some sample data is shown in Figure 2 where we just display the Ids of the retrieved Contact records.

Some Observations

• This example hardcodes the values of objects which a user can select - in this case to either Account or Contact and the field used as a filter - in this case the zipcode.  More typically such criteria would be built dynamically as well.  A general purpose reporting tool for example, would not know what custom objects an org may contain and the exact criteria which the user may want to use as filter.   
• Use dynamic SOQL only if you require extra flexibility or if you can't build a query ahead of time. Dynamic SOQL requires more coding and error checking on your part.
• The query method on Database expects the entire query as a string. Since no compile time checking can be done, if the query is malformed or incorrect a runtime error will be thrown.  Your code should be able to handle it.
• Letting end users control the form of your SOQL query can potentially make the code vulnerable to SOQL injection attack.  SOQL injection attack involves a malicious user making your program execute database method you didn't intend to be executed.  For more details and how to protect yourself aganist it refer to the Apex documentation.

Figure 1

Figure 2

This is just one of the new features in Winter '09 - I will be sharing my experience with other features as I explore them myself. 

Nick Simha