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.
I've just tried some apex to execute the Database.query() dynamically. The logic is to get the soql query from somewhere and put it to the Database.query() in some method that gets the set of id's at the same time (i hope i understand the question right). So i've created a list method that gets a soql as a string where the set name not specifically defined (just a XXX placeholder) and the actual set name. It works perfectly. I could store soql strings in the database in some fields, it does not matter. Yes, i can not bind to the objects field directly, but i can read this field first, put it to the string and then bind it. But for this simple example i will store sets and soqls directly in the class. I can create the final Database.query() parameter dynamically as i wish.
public class MyTestClass{
public List<sObject> myList {get; set;}
public Set<Id> set1 = new Set<id>{'001E000000fpkEJ'};
public Set<Id> set2 = new Set<id>{'006E0000005Ry3S'};
public final String soql1 = 'Select Name From Account Where Id IN :XXX';
public final String soql2 = 'Select Name From Opportunity Where Id IN :XXX';
public MyTestClass(){
myList = new List<Account>();
}
public List<sObject> objects(String setName, String soqlString){
return Database.query(soqlString.replace('XXX',setName));
}
public PageReference soqlExecuter1(){
myList.clear();
myList = objects('set1', soql1);
return null;
}
public PageReference soqlExecuter2(){
myList.clear();
myList = objects('set2', soql2);
return null;
}
}
I can generate a list of different unknown objects types just with one method with unknown soql string and unknown set name.
I hope this helps.
With the following simple page i can read unknown objects:
<apex:page controller="MyTestClass">
<apex:form>
<apex:pageBlock>
<apex:pageBlockButtons>
<apex:commandButton action="{!soqlExecuter1}" reRender="myTable" value="Execute1"/>
<apex:commandButton action="{!soqlExecuter2}" reRender="myTable" value="Execute2"/>
</apex:pageBlockButtons>
<apex:pageBlockTable value="{!myList}" var="a" id="myTable">
<apex:column value="{!a['name']}" />
</apex:pageBlockTable>
</apex:pageBlock>
</apex:form>
</apex:page>
Just results of the code above:
And yes, this is all based on the official and credible documentation
, like:
However, unlike inline SOQL, dynamic SOQL can’t use bind variable
fields in the query string. (source)
BUT
You can instead resolve the variable field into a string and use the
string in your dynamic SOQL query (source)
Well, since this is just a string i will use another official and credible source to modify my string, like String instance methods.
Best Answer
This is the brute-force code my colleague ended up writing; not only multiple named bind fields but the fields needed to be of the right type: