Using Variables In Dynamic SOQL - Uncovering

Monday, September 10, 2012

In Earlier post "Using Variables In Dynamic SOQL",  I wrote about how to use variables in a Dynamic SOQL. Well after playing with Dynamic SOQL, I found few interesting thing like:

·         You CAN directly use primitive data types in SOQL.

So what does this means?

This means you can directly use any Integer, String , Date, Datetime,Double, Id variable along with Collection of this variable within the query.

For Example: 

a) You are allowed to do this

//initialize the Datetime with current time

DateTime now = System.now();

//query accounts by merging the variable name inside the query string

List<Account> accountList = Database.query('SELECT Id FROM Account WHERE CreatedDate =:now');

b)You can also include LISTs(Collections) in your queries

List<String> accNameList = new List<String>{'Acc1','Acc2'}

//query accounts by merging the variable name inside the query string

List<Account> accountList = Database.query('SELECT Id FROM Account WHERE Name IN:accNameList');

·         You CANNOT use complex types in a Dynamic SOQL directly.

This means that you cannot use any Sobject, Apex Class or Any other user defined data type inside the Dynamic Query. In short you cannot use a dot (".") operator to specify a field value in a Dynamic query.

For Example :

                a)  Using an Sobject inside the query String will not work

           //initialize a Account with a Name value for demo

Account acc = new Account(Name='MyAccount');

//query accounts by merging the variable name inside the query string

//This will not work

List<Account> accountList = Database.query('SELECT Id FROM Account WHERE Name =:acc.Name');

//But You can always make the above work by writing the code as.

           //initialize a Account with a Name value for demo

      Account acc = new Account(Name='MyAccount');

      String accountName = acc.Name

      //query accounts by merging the variable name inside the query string

      List<Account> accountList = Database.query('SELECT Id FROM Account WHERE                       
      Name =: accountName  ');

Hope this helps!.

Using Variables In Dynamic SOQL

Friday, August 17, 2012

Well how many times you have tried to use a list variable in a Dynamic SOQL?
We generally use Database.query() ,  which takes a query in string format and returns a List of sObject . The problem comes when want to use "IN" statement or want to use "date/date time" in a Dynamic query . I really tried a lot of combination, formatting the data,escapes etc.But finally was surprised to see if you just include the variable inside the query String, it just works !!!

Wasn't clear enough??

See the below example  which shows how to query all the accounts that are created today:

 
//initialize the Datetime with current time
DateTime now = System.now(); 
//query accounts by merging the variable name inside the query string 
List<Account> accountList = Database.query('SELECT Id FROM Account WHERE CreatedDate =:now'); 

Please Note :  that the variable "now" is included in the string itself.

Datetime field in Dynamic SOQL

Thursday, May 24, 2012

[You can see this blog for a more elegant solution ]

This was unknown to me and in past months I tried hard to use Datetime field in Dynamic SOQL query, well after doing lots of permutations and combinations I finally figured it out! which I though was not possible.

Well I guess this is worth sharing so here it is.

You can compare any Datetime field in Dynamic SOQL where query! Just keep in mind the format should be correct. You can use the format method to format datetime field like this where System.Now() is my Datetime field.

System.Now().format('yyyy-MM-dd\'T\'HH:mm:ss\'Z\''));

OR

DateTime dt = System.Now();//initialize datetime with current datetime

String formatedDt = dt.format('yyyy-MM-dd\'T\'HH:mm:ss\'Z\'');//format the datetime to make it Dynamic Soql ready

So a sample query to extract all the Accounts where the CreatedDate is less than Today will be

DateTime dt = System.Now();//initialize datetime with current datetime

String formatedDt = dt.format('yyyy-MM-dd\'T\'HH:mm:ss\'Z\'');//format the datetime to make it Dynamic Soql ready

Database.query('Select Id FROM Account WHERE CreatedDate<'+formatedDt);