function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Randombard2Randombard2 

First error: Only variable references are allowed in dynamic SOQL/SOSL.

Hi All,

I am trying to start a batch process from a trigger, but cant get the where cluase to pass in the query string.
  
BuildIBCall reassign = new BuildIBCall();
reassign.email='notmyemail@idontworkhere.com';
String sql = 'select Account__c, from OB__c Where ID IN:';
String idString= '\''+String.join(IBToUpdate,'\',\'')+'\'';
Sql+= idString;        
reassign.query= Sql;
ID batchprocessid = Database.executeBatch(reassign, 30);

Now I know its passing the ID's because if I remove the : I get the error:

Any ideas?
R
First error: expecting a colon, found 'anidishere'
Best Answer chosen by Randombard2
Randombard2Randombard2
BuildIBCall reassign = new BuildIBCall();
reassign.email='notmyemail@idontworkhere.com';
String sql = 'select Account__c from OB__c Where ID ';
        IF(IBToUpdate.size()==1){
sql+= '=  \''+IBToUpdate[0]+'\'';            
        }
        else{          
Set<Id> setIds = new Set<Id>();            
setIDs.addAll(IBToUpdate);
            reassign.setIDs = SetIds;
            Sql+='IN: setIDs';
        }            
reassign.query= Sql;
ID batchprocessid = Database.executeBatch(reassign, 30);

I went with a combination of the 2 
Thanks both for all of your help.

All Answers

Pankaj_GanwaniPankaj_Ganwani
Hi,

You can simple use set in the dynamic query instead of forming comma separated string and then using it:

String sql = 'select Account__c, from OB__c Where ID IN: setIds';
Randombard2Randombard2
Hi Pankaj,

Thanks, but then I get:

First error: Variable does not exist: IBToUpdate

It clearly does in the trigger, and I can make it public (not global as its a trigger not a class) but it still fails.

R
Randombard2Randombard2
And if I use: +IBToUpdate at the end of the query string I get:

First error: unexpected token: '{'
Pankaj_GanwaniPankaj_Ganwani
Hi, 

Can you share your complete code with me please?
Randombard2Randombard2
Hi Pankaj,

I am not sure I can sanitize it enough while leaving it meaningful.

Thanks for all of the help so far.

R
Randombard2Randombard2
Things I have tried,

IBToUpdate as a list
IBToUpdate as a set
Converting IBToUpdate to a string before adding it to the end of the query- First error: Only variable references are allowed in dynamic SOQL/SOSL.
Using + IBToUpdate at the end of the query -First error: unexpected token: '{'
Putting IBToUpdate into the query - First error: Variable does not exist: IBToUpdate

Is all of this because I am trying to do this from a trigger?
R
Bhaswanthnaga vivek vutukuriBhaswanthnaga vivek vutukuri

Don't if you using array of Ids if that is situation add those ids to a set using for loop.

set<Id> ids = new set<Id>();
ids.add(IBToUpdate)

String Sql = 'select Account__c, from OB__c Where ID IN: ' +ids;

 

Or If you are using single Id

 

String Sql = 'select Account__c, from OB__c Where ID =: ' +IBToUpdate;
Randombard2Randombard2
Hi Bhaswanthnaga,

I am using an array of ID's from your first method.

R
Randombard2Randombard2
Thats when I get: First error: Only variable references are allowed in dynamic SOQL/SOSL.
Bhaswanthnaga vivek vutukuriBhaswanthnaga vivek vutukuri

Replace your code with my code, you forget to give space after IN: ' instead of you have given IN:'

once post your entire code please the I can give accurate answer
Pankaj_GanwaniPankaj_Ganwani
Hi,

Please use below mentioned code. I have converted your list to set since list cannot be referenced within dynamic soql so we must first convert this to set and then use it.
BuildIBCall reassign = new BuildIBCall(); reassign.email='notmyemail@idontworkhere.com'; Set<Id> setIds = new Set<Id>(); setIds.addAll(IBToUpdate); String sql = 'select Account__c, from OB__c Where ID IN: setIds'; reassign.query= Sql; ID batchprocessid = Database.executeBatch(reassign, 30);
​
Let us know if you find any issues.
Randombard2Randombard2
Hi Pankaj,

I am afraid:
First error: Variable does not exist: setIds

R
Randombard2Randombard2
BuildIBCall reassign = new BuildIBCall();
reassign.email='notmyemail@idontworkhere.com';
String sql = 'select Account__c from OB__c Where ID ';
        IF(IBToUpdate.size()==1){
sql+='=:'+IBToUpdate[0];            
        }
        else{
String idString= '\''+String.join(IBToUpdate,'\',\'')+'\'';
Sql+='IN: ' +idString;
        }            
reassign.query= Sql;
ID batchprocessid = Database.executeBatch(reassign, 30);

First error: Variable does not exist: a0z9E000000AkdTnA0
Pankaj_GanwaniPankaj_Ganwani
Hi,

I got this problem. You will also have to pass the set to batch class since we have declared set in trigger and soql is complied in batch apex. Please add one more parameter in batch apex of type set<Id> and then execute the batch.
Randombard2Randombard2
Sorry Pankaj,

please can you show me what you mean.

Thanks
R
Bhaswanthnaga vivek vutukuriBhaswanthnaga vivek vutukuri
sql+= '=  \''+IBToUpdate[0]+'\'';
Pankaj_GanwaniPankaj_Ganwani
reassign.email='notmyemail@idontworkhere.com'; 
Set<Id> setIds = new Set<Id>(); 
setIds.addAll(IBToUpdate); 
String sql = 'select Account__c, from OB__c Where ID IN: setIds'; 
BuildIBCall reassign = new BuildIBCall(setIds); // create a variable of type set<id> in batch class.
reassign.query= Sql; 
ID batchprocessid = Database.executeBatch(reassign, 30);

You will have to create a variable of type set<Id> in batch class and then initialize it with the set(i.e. setIds) at the time of creating an instance(reassign).
Randombard2Randombard2
Hi Pankaj,

I meant what does that look like int eh batch class please.

I assume something like

Global set setIds;

R
Pankaj_GanwaniPankaj_Ganwani
Yes
Randombard2Randombard2
BuildIBCall reassign = new BuildIBCall();
reassign.email='notmyemail@idontworkhere.com';
String sql = 'select Account__c from OB__c Where ID ';
        IF(IBToUpdate.size()==1){
sql+= '=  \''+IBToUpdate[0]+'\'';            
        }
        else{          
Set<Id> setIds = new Set<Id>();            
setIDs.addAll(IBToUpdate);
            reassign.setIDs = SetIds;
            Sql+='IN: setIDs';
        }            
reassign.query= Sql;
ID batchprocessid = Database.executeBatch(reassign, 30);

I went with a combination of the 2 
Thanks both for all of your help.
This was selected as the best answer