Find your content:

Search form

You are here

Dynamic SOQL variable binding not working for multiple records

 
Share

Am building a dynamic SOQL where I am using a list of strings that represent record ids, here's an example similar to my class:

String q = '';
q += 'SELECT Id FROM' + object;
q += ' WHERE Id IN :record_ids';
List<sObject> sobjects = Database.query(q);

When record_ids has a single Id in the list it works fine, more then one and it does not bring back any records.

I have tried creating a string based IN in many forms, for example:

record_scope += '\'' + record_id + '\',';

But when I look at the logs instead of ... IN ('00000000000001','00000000000002') I get ... IN ('00000000000001,00000000000002') looks like ' are subtracted for some reason.

Any help will be appreciated.

Cheers


Attribution to: rumdumdum

Possible Suggestion/Solution #1

Create record_ids as a List of Strings or Ids, e.g.

List<Id> record_ids = new List<Id>{
  '00000000000001',
  '00000000000002'
};

If you have an existing string of Ids, you can convert it an array using String.split.


Attribution to: xn.

Possible Suggestion/Solution #2

You can directly use bind variables in dynamic soql, as incredible as it sounds.

Alternatively, the more traditional approach of quote delimiting the keyset and constructing your where clause manually. You can use the shiny Winter'13 String.Join method to iterate over a list and append with a delimiter.


Attribution to: techtrekker

Possible Suggestion/Solution #3

Elegant way to convert Set<Id> into String for Dynamic SOQL IN comparison

Have a look at the above post.You need not even split and all

Map<Id,Account> accts = new Map<Id,Account>([select Id from Account]);
Set<Id> accountIds = accts.keySet();
String q = 'select id from Contact where AccountId in :accountIds';
List<Contact> cts = Database.query(q);

Just use the variable as demonstarted in above code .


Attribution to: Mohith Shrivastava

Possible Suggestion/Solution #4

The initial approach looks good, though the code you have posted won't work as is:
* object is a reserved word
* you have no space after FROM before the object name

These I guess are only symptoms of simplifying the code for posting it here.

The code below works in my org (though these particular Ids are fake):

String objectName = 'Account';
List<String> record_ids = new List<String> {'001000000000001','001000000000002','001000000000003'};
String q = '';
q += 'SELECT Id FROM ' + objectName;
q += ' WHERE Id IN :record_ids';
List<sObject> sobjects = Database.query(q);

I'd have to assume then that it is the building of record_ids that's at fault.

You could try using this debugging code before the Database.query to verify the state of record_ids:

system.assert(record_ids.size()>1);
for(String item : record_ids)
    Id myId = (ID) item;

Attribution to: Stephen Willcock

Possible Suggestion/Solution #5

Looks like there was a problem with the record_ids list, Visualforce parameter that held all the ids was passed in as a single entry, this explains why it worked with only one entry and not with multiple.

Thanks for your help guys.


Attribution to: rumdumdum
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/5639

My Block Status

My Block Content