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
PXForcePXForce 

System.QueryException : Unexpected token '('

Hi, 

 

I am having trouble getting this query working in a batchable class. 

 

In my start method when I use the following query : 

 

 

String query = 'Select a.Id, a.Name, a.OwnerId ,(Select Id, OwnerId From Contacts) from Account a Where a.Type IN: '+ accTypesToInclude ;
 return Database.getQueryLocator(query);

 

 

I am getting the error :

 

System.QueryException: unexpected token: '(' 

 

Any ideas whats wrong

 

Thanks

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox

@rahulsharma,

 

As I stated previously, this dynamic string binding only occurs in a Database method, such as Database.getQueryLocator() or Database.query(). I shall provide a demonstration from my logs to prove that the system does work as I have described.

 

First, I shall provide a sample Dynamic Apex Code, such as I described in my previous response.

 

Here is the Execute Anonymous code that I used:

 

 

Set<String> accNames = new Set<String>{ 'test','test me' };
String query = 'select id,name,(select id,ownerid from contacts) from account where name in :accNames';
System.debug(query);
System.debug(Database.query(query));

Having executed this code, the result returns three records, two named 'test', and one named 'test me'. This is despite the fact that used only dynamic colon binding and did not use any fancy tricks to achieve these results.

 

Here is the output from my example code:

 

 

20.0 APEX_CODE,FINEST;APEX_PROFILING,INFO;CALLOUT,INFO;DB,INFO;VALIDATION,INFO;WORKFLOW,INFO
Execute Anonymous: Set<String> accNames = new Set<String>{ 'test','test me' };
Execute Anonymous: String query = 'select id,name,(select id,ownerid from contacts) from account where name in :accNames';
Execute Anonymous: System.debug(query);
Execute Anonymous: System.debug(Database.query(query));
01:25:58.816 (12241000)|EXECUTION_STARTED
01:25:58.816 (12269000)|CODE_UNIT_STARTED|[EXTERNAL]|execute_anonymous_apex
01:25:58.817 (12848000)|STATEMENT_EXECUTE|[1]|Anonymous
01:25:58.817 (12939000)|STATEMENT_EXECUTE|[1]|DeclareVar: SET<String> accNames
01:25:58.817 (12986000)|VARIABLE_SCOPE_BEGIN|[1]|accNames|Set|true
01:25:58.817 (13066000)|HEAP_ALLOCATE|[1]|Bytes:23
01:25:58.817 (13153000)|STATEMENT_EXECUTE|[2]|DeclareVar: String query
01:25:58.817 (13197000)|VARIABLE_SCOPE_BEGIN|[2]|query|String|false
01:25:58.817 (13280000)|VARIABLE_ASSIGNMENT|[2]|query|"select id,name,(select id,ownerid from contacts) from account where name in :accNames"
01:25:58.817 (13351000)|STATEMENT_EXECUTE|[3]|System.debug(String)
01:25:58.817 (13404000)|METHOD_ENTRY|[3]|System.debug(ANY)
01:25:58.817 (13463000)|USER_DEBUG|[3]|DEBUG|select id,name,(select id,ownerid from contacts) from account where name in :accNames
01:25:58.817 (13511000)|METHOD_EXIT|[3]|System.debug(ANY)
01:25:58.817 (13568000)|STATEMENT_EXECUTE|[4]|System.debug(LIST<SObject>)
01:25:58.817 (13613000)|METHOD_ENTRY|[4]|System.debug(ANY)
01:25:58.818 (13668000)|METHOD_ENTRY|[4]|Database.query(String)
01:25:58.827 (22750000)|SOQL_EXECUTE_BEGIN|[4]|Aggregations:1|select id,name,(select id,ownerid from contacts) from account where name in :accNames
01:25:58.855 (51218000)|SOQL_EXECUTE_END|[4]|Rows:3
01:25:58.855 (51375000)|HEAP_ALLOCATE|[4]|Bytes:16
01:25:58.855 (51418000)|HEAP_ALLOCATE|[4]|Bytes:115
01:25:58.855 (51452000)|METHOD_EXIT|[4]|Database.query(String)
01:25:58.856 (51732000)|USER_DEBUG|[4]|DEBUG|(Account:{Name=test, Id=0015000000YJiBtAAL}, Account:{Name=Test Me, Id=0015000000YD47fAAD}, Account:{Name=test, Id=0015000000Un0IZAAZ})
01:25:58.856 (51770000)|METHOD_EXIT|[4]|System.debug(ANY)

As you can see, the first red line in my demonstration proves that the string has been unaltered, using only a dynamic apex binding variable as I outlined previously. No escaped quotes, parenthesis, mathematical operators or functions were harmed in the making of this string.

 

Slightly later, you can determine that the string is passed into the Database.query method, and it still looks the same as the variable. The magenta line immediate thereafter shows that the system returned three records, even though "test" and "test me" were never explicitly put into the string. I did not have to count commas, use any special quotes, or parenthesis, and the system was still able to single out those three records to my precise specification.

 

Dynamic Apex is meant to be as easy and secure as possible, and given that the most likely attack vector for SOQL injection is incorrect character escapes while using Dynamic Apex, it makes sense that parameter binding working on these functions. This design is in place to reduce the risks of users typing in a "search term" of ') or name <> ', which if care is not exercised, might well crash your script or expose data they shouldn't see.

 

Please try this for yourself so you can understand how parameter binding can be used to create dynamic queries quickly and easily.

 

All Answers

mikefmikef

PXForce:

 

you can't add the set of ids to the string query and have apex cast it for you into the proper format.

 

you need to get you string to look like this.

 

String query = 'Select a.Id, a.Name, a.OwnerId ,(Select Id, OwnerId From Contacts) from Account a Where a.Type IN (\'sobjectId1\', \'sobjectId2\'...,\'sobjectIdn\')';

 in order to do that you have to loop through your set of Ids and put them into a string.

 
PXForcePXForce

accTypesToInclude is already a list of ids...anyways i got that working ...but when I use  NOT IN: instead of IN: it gives me System.Exception unknown token ':'

any ideas why?

 
mikefmikef

yes don't use the ':' in the string, you don't need it.
sfdcfoxsfdcfox

Actually, you can use that syntax, but the variable has to be in the string. Do this:

 

 

String query = 'Select a.Id, a.Name, a.OwnerId ,(Select Id, OwnerId From Contacts) from Account a Where a.Type IN :accTypesToInclude';

Dynamic Apex supports variable binding directly. Just be sure that the variable is in the scope of the dynamic query function (in this case, getQueryLocator).

 
rahulsharmarahulsharma

Hello  PXForce,

1)I just have doubt that u have to use ID instead of type while filtering query,if you are filtering it as type(string).

2)your Id must be in string in that query,

Try this:

String query = 'Select a.Id, a.Name, a.OwnerId ,(Select Id, OwnerId From Contacts) from Account a Where a.Type IN \''+ accTypesToInclude+'\'' ;

 

Hope this helps.
sfdcfoxsfdcfox

If you don't use variable binding, then you must use parenthesis, just as in SQL.
rahulsharmarahulsharma

Hello,

i just noticed that,

if u debug this :

 

Id accTypesToInclude ='00190000004Ww8A';
String query = 'Select a.Id, a.Name, a.OwnerId ,(Select Id, OwnerId From Contacts) from Account a Where a.Type IN :accTypesToInclude';
system.debug(query);

you'll get :

DEBUG|Select a.Id, a.Name, a.OwnerId ,(Select Id, OwnerId From Contacts) from Account a Where a.Type IN :accTypesToInclude

 

and if u debug this string:

Id accTypesToInclude ='00190000004Ww8A';
String query = 'Select a.Id, a.Name, a.OwnerId ,(Select Id, OwnerId From Contacts) from Account a Where a.Type IN \''+ accTypesToInclude+'\'' ;
system.debug(query);

you'll get :

Select a.Id, a.Name, a.OwnerId ,(Select Id, OwnerId From Contacts) from Account a Where a.Type IN '00190000004Ww8AAAS'

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

I took static Id only for Explanation
sfdcfoxsfdcfox

@rahulsharma,

 

As I stated previously, this dynamic string binding only occurs in a Database method, such as Database.getQueryLocator() or Database.query(). I shall provide a demonstration from my logs to prove that the system does work as I have described.

 

First, I shall provide a sample Dynamic Apex Code, such as I described in my previous response.

 

Here is the Execute Anonymous code that I used:

 

 

Set<String> accNames = new Set<String>{ 'test','test me' };
String query = 'select id,name,(select id,ownerid from contacts) from account where name in :accNames';
System.debug(query);
System.debug(Database.query(query));

Having executed this code, the result returns three records, two named 'test', and one named 'test me'. This is despite the fact that used only dynamic colon binding and did not use any fancy tricks to achieve these results.

 

Here is the output from my example code:

 

 

20.0 APEX_CODE,FINEST;APEX_PROFILING,INFO;CALLOUT,INFO;DB,INFO;VALIDATION,INFO;WORKFLOW,INFO
Execute Anonymous: Set<String> accNames = new Set<String>{ 'test','test me' };
Execute Anonymous: String query = 'select id,name,(select id,ownerid from contacts) from account where name in :accNames';
Execute Anonymous: System.debug(query);
Execute Anonymous: System.debug(Database.query(query));
01:25:58.816 (12241000)|EXECUTION_STARTED
01:25:58.816 (12269000)|CODE_UNIT_STARTED|[EXTERNAL]|execute_anonymous_apex
01:25:58.817 (12848000)|STATEMENT_EXECUTE|[1]|Anonymous
01:25:58.817 (12939000)|STATEMENT_EXECUTE|[1]|DeclareVar: SET<String> accNames
01:25:58.817 (12986000)|VARIABLE_SCOPE_BEGIN|[1]|accNames|Set|true
01:25:58.817 (13066000)|HEAP_ALLOCATE|[1]|Bytes:23
01:25:58.817 (13153000)|STATEMENT_EXECUTE|[2]|DeclareVar: String query
01:25:58.817 (13197000)|VARIABLE_SCOPE_BEGIN|[2]|query|String|false
01:25:58.817 (13280000)|VARIABLE_ASSIGNMENT|[2]|query|"select id,name,(select id,ownerid from contacts) from account where name in :accNames"
01:25:58.817 (13351000)|STATEMENT_EXECUTE|[3]|System.debug(String)
01:25:58.817 (13404000)|METHOD_ENTRY|[3]|System.debug(ANY)
01:25:58.817 (13463000)|USER_DEBUG|[3]|DEBUG|select id,name,(select id,ownerid from contacts) from account where name in :accNames
01:25:58.817 (13511000)|METHOD_EXIT|[3]|System.debug(ANY)
01:25:58.817 (13568000)|STATEMENT_EXECUTE|[4]|System.debug(LIST<SObject>)
01:25:58.817 (13613000)|METHOD_ENTRY|[4]|System.debug(ANY)
01:25:58.818 (13668000)|METHOD_ENTRY|[4]|Database.query(String)
01:25:58.827 (22750000)|SOQL_EXECUTE_BEGIN|[4]|Aggregations:1|select id,name,(select id,ownerid from contacts) from account where name in :accNames
01:25:58.855 (51218000)|SOQL_EXECUTE_END|[4]|Rows:3
01:25:58.855 (51375000)|HEAP_ALLOCATE|[4]|Bytes:16
01:25:58.855 (51418000)|HEAP_ALLOCATE|[4]|Bytes:115
01:25:58.855 (51452000)|METHOD_EXIT|[4]|Database.query(String)
01:25:58.856 (51732000)|USER_DEBUG|[4]|DEBUG|(Account:{Name=test, Id=0015000000YJiBtAAL}, Account:{Name=Test Me, Id=0015000000YD47fAAD}, Account:{Name=test, Id=0015000000Un0IZAAZ})
01:25:58.856 (51770000)|METHOD_EXIT|[4]|System.debug(ANY)

As you can see, the first red line in my demonstration proves that the string has been unaltered, using only a dynamic apex binding variable as I outlined previously. No escaped quotes, parenthesis, mathematical operators or functions were harmed in the making of this string.

 

Slightly later, you can determine that the string is passed into the Database.query method, and it still looks the same as the variable. The magenta line immediate thereafter shows that the system returned three records, even though "test" and "test me" were never explicitly put into the string. I did not have to count commas, use any special quotes, or parenthesis, and the system was still able to single out those three records to my precise specification.

 

Dynamic Apex is meant to be as easy and secure as possible, and given that the most likely attack vector for SOQL injection is incorrect character escapes while using Dynamic Apex, it makes sense that parameter binding working on these functions. This design is in place to reduce the risks of users typing in a "search term" of ') or name <> ', which if care is not exercised, might well crash your script or expose data they shouldn't see.

 

Please try this for yourself so you can understand how parameter binding can be used to create dynamic queries quickly and easily.

 

This was selected as the best answer
rahulsharmarahulsharma

Thank you very much sdfcfox for making things very clear and simple..
PanchoPancho

@sfdcfox

 

I ran into this same issue.

Where do i get the Database.query class?

Is there a Jar file or WSDL that it comes from?

I cant seem to see it under any JAVA import statements.

Any help is greatly appreciated.

 

Thanks
sfdcfoxsfdcfox

It's part of the system library for Apex Code. There is no JAR or WSDL, because we're talking about Apex Code, not Java.
A D PA D P
Hi @sfdcfox,
            i'm getting this error "Calculation error on quote Q-05208: System.QueryException: invalid ID field: null" please guide me why im getting this error.
Thanks & Regards