Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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?

share|improve this question
    
I've raised the idea Dynamic SOQL binding expression support to match static SOQL as I couldn't find an existing one. –  Daniel Ballinger Mar 6 '13 at 3:01

2 Answers 2

up vote 10 down vote accepted

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.

share|improve this answer
    
In this case, it's a parameter passed to the function (filter in my example above), which is within the scope of the method. –  tomlogic Feb 13 '13 at 0:06
5  
As the author of the initial answer: +1 to this answer. It doesn't evaluate expressions, it only binds to variables that are in scope. (if it did evaluate I would have abused it to build an eval method a while back). –  ca_peterson Feb 13 '13 at 0:33
2  
@ca_peterson: OK, I think I'm starting to grasp it. filter is a local variable, but filter.deviceType is actually an expression that requires evaluation. I'm still picking up on the nuances of Apex classes used to emulate the C structures I'm more familiar with. –  tomlogic Feb 13 '13 at 0:55
    
Cool. I updated my other answer to point to this question for any future readers. You might also want to mark @greenstork's answer as accepted if it got you working. –  ca_peterson Feb 13 '13 at 3:23

Well some time back I wrote a blog about Using Variables In Dynamic SOQL, including the limitation that you cannot use dot notation in the referenced variables.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.