28

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?

Improve this question
1

2 Answers 2

Reset to default
20

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.

Improve this answer
4
  • 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
    Commented Feb 13, 2013 at 0:06
  • 6
    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
    Commented Feb 13, 2013 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
    Commented Feb 13, 2013 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
    Commented Feb 13, 2013 at 3:23
4

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .