10

I'm trying to re-request an Asset from the database:

asset = Database.query('SELECT ID, Price, Asset_MRR__c FROM Asset WHERE id =: ' + asset.id + ' limit 1');

But I get an error:

Only variable references are allowed in dynamic SOQL/SOSL

What am I doing wrong?

16

If you use : in your query, Apex expects a variable. In case you are querying with the string, you should not use the :, but escape single quotes(\'). So your query should look like this:

asset = Database.query('SELECT ID, Price, Asset_MRR__c FROM Asset WHERE id =\'' + asset.id + '\' limit 1');

You could also use :, but then just put asset.id in your query string:

asset = Database.query('SELECT ID, Price, Asset_MRR__c FROM Asset WHERE id =:asset.id limit 1');

EDIT

Just checked the second example in developer console with this code:

String testName = 'test';
List<Account> accList = Database.query('SELECT Id, Name FROM Account WHERE Name = :testName');
System.debug('accList: ' + accList);

The output was:

09:09:32:100 USER_DEBUG [3]|DEBUG|accList: (Account:{Id=0018E000005L8D3QAK, Name=test, RecordTypeId=012w0000000QSfUAAW}, Account:{Id=0018E000007eRInQAM, Name=test, RecordTypeId=012w0000000QMrxAAG})

Also, using LIMIT 1 when querying with WHERE Id = ... is not necessary, ids are unique anyway, so you will get 0 or 1 results anyway.

  • Your 2nd query is wrong , if SOQL is between ' ' , then it will be considered as a String, So there won't be any actual 15 digit asset id available in the query. – SfdcBat May 12 '16 at 7:06
  • 1
    @SfdcBat no, you are wrong. Just checked it in dev console and it works – Novarg May 12 '16 at 7:10
  • I apologies, I thought there is no way to get the Id in such a query – SfdcBat May 12 '16 at 7:14
5

The dynamic SOQL documentation explains that simple bind variables (introduced with a :) can be used, that is a reference to a variable (but not a dotted expression or method call). So the pattern to use here is this:

Id assetId = asset.Id;
asset = Database.query('SELECT ID, Price, Asset_MRR__c FROM Asset WHERE id =:assetId limit 1');

where you assign the more complex expression to a variable and bind the simple variable into the dynamic SOQL.

Advantages of this approach over string concatenation are:

  • Cleanest way to avoid SOQL injection risk
  • Makes the code easier to read (and write) because it avoids extra quotes and extra string concatenation

(In other database environments binding values also allows caching of query plans to work better because the query text itself does not vary; I have not seen any mention of that for SOQL though.)

1

Try to use this query:

'SELECT ID, Price, Asset_MRR__c FROM Asset WHERE id = \' '+ asset.id + '\' limit 1'

Also, not nessessary code limit when you try to get record by id

  • 2
    Not a good habit to get into: if instead of asset.id you were using a string value that came from the UI you would be leaving your application open to SOQL injection risk. – Keith C May 12 '16 at 8:12
1

try this out .

String query='Select ' + fieldName + ' FROM ' + objectName + ' WHERE Id IN';

query += ':ids';

sobj= Database.query(query); 

or

'Select ' + fieldName + ' FROM ' + objectName + ' WHERE Id = \'' + com.Account_Lookup__c + '\''+' Limit 1'
  • Hi Shyamesh, welcome to SFSE! After looking at the other answers, this one isn't substantially different than Ruslan Kurchenko's. It's also missing some elements of a good answer. To me, a good answer will explain several things: - What is the problem? - Why is it a problem? - How can we fix the problem? - Why does the fix work? I like to keep in mind that other people will be finding this question, and trying to apply it to their own, similar (but different) problems. A good answer will help other people as well, even if it's only let them know that their problem is not solved by this answer. – Derek F Jul 3 '18 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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