Bind variable with dotted name not working in dynamic SOQL

As mentioned in this answer, it’s possible to use bind variables in dynamic SOQL queries. This seems like a great way to avoid SOQL-injection, and to build a query string once but use it multiple times with different values in the variables.

I’m working on a method that returns a list of objects. It accepts a class with multiple properties as its only parameter. The properties of this class are used to filter the results to just the required records. It will eventually be driven by a Visualforce component, which will be configured by querystring parameters in the URL.

I just ran into an issue where the query fails when I use dotted bind variables. If I copy the constructed dynamic SOQL into a static query, it works fine. This may be a bug, because in doing tests I found that :foo.bar would actually try to use local variable bar.

Here’s one of my queries, which uses related custom objects. I’ve stripped it down to just the part that’s failing (wrapped for legibility):

result = Database.query('SELECT Name, Another_Object__r.Name,
     Another_Object__r.Custom_Field__c
     FROM Custom_Object__c 
     WHERE Another_Object__r.Custom_Field__c = :filter.deviceType');

The error reported is System.QueryException: Variable does not exist: filter.deviceType. As mentioned above, the query works fine as static SOQL, just not dynamic:

result = [SELECT Name, Another_Object__r.Name, Another_Object__r.Custom_Field__c
FROM Custom_Object__c WHERE Another_Object__r.Custom_Field__c = :filter.deviceType];

It also works in this case:

String deviceType = filter.deviceType;
Database.query('SELECT Name, Another_Object__r.Name, Another_Object__r.Custom_Field__c
FROM Custom_Object__c WHERE Another_Object__r.Custom_Field__c = :deviceType');

I haven’t read about this being a known limitation. I don’t know if I should be using some other syntax/notation in the query — the dotted notation appears correct.

Time to report a bug? Thoughts on workarounds other than creating local variables to stand in for each member of the class?

Answer

You found the answer, dynamic SOQL is looking for your variable declaration within the scope of your method. I don’t know why it doesn’t respect public properties of the class, but that is the case and your solution of setting it to a method-scoped variable should do the trick.

Attribution
Source : Link , Question Author : tomlogic , Answer Author : greenstork

Leave a Comment