7

I'm building a system where a user will be providing me with a query through a UI. This query will then be used in an Async Apex Job, by passing it as a param to Database.getQueryLocator in the start method.

Initially, I was using Database.query or Database.countQuery for validating a query and catching possible exceptions. But in actuality, there could be millions of records in which case these functions will throw an error saying I've exceeded governor limits.

Does Salesforce provide a way of compiling a query just to check whether it is valid? A quick Google search turns up nothing.

  • 1
    No, you can't check. Put the query in try catch block! – Krishna Bidwai Feb 14 '17 at 7:27
  • I don't want to execute it, I just want to validate it... – coldspeed Feb 14 '17 at 7:28
  • no you can't do any validation without actual query. – Krishna Bidwai Feb 14 '17 at 7:29
  • you can do that but thats not going to be easy. First you need to parse the query and find the object and all fields name then using describe you can validate all API name and related field and there API name. Then you can check for special character Select, Find, WHEREetc and check there spellings are correct and at end you can check the number provided in limits are correct. – Tushar Sharma Feb 14 '17 at 7:42
  • 3
    I suggest you put security at the top of your list of concerns here. Executing a query in Apex code like this will bypass access rights defined in profiles essentially allowing access to every object and field in the org. The only way to have a secure result would be for you to parse and check the query against describe data before you execute it. – Keith C Feb 14 '17 at 10:23
13

You might be able to accomplish this by executing the query given with an additional LIMIT 0 at the end. You'll still use one SOQL query, but I don't believe this will count against the total number of rows retrieved. Something like:

string userQuery = 'select ID from Contact where Name like \'myContact%\'';
try
{
    Database.Query(userQuery + ' LIMIT 0');
}
catch(QueryException ex)
{
    //Error handling
}

This will not work for aggregate queries that do not have a group by function, but I believe limit always comes at the end of queries, so it ought to work with any others that don't already set a limit. If your user queries can/will have limit set, you could add some parsing to check if the limit is there, and if so validate it and then run the query with it set to 0.

| improve this answer | |
  • Nice find, though. I would have thought that LIMIT 1 was the lowest you could go. – sfdcfox Feb 21 '17 at 22:04
  • @sfdcfox I tested it in the Dev Console before posting this answer, it seems to be fine with 0. – IllusiveBrian Feb 21 '17 at 22:13
  • 1
    Yeah, that's why I gave you the +1. I learned something new today. It's a really creative solution. – sfdcfox Feb 21 '17 at 22:23
  • Ah, super, thanks for sharing! Never knew/tried limit 0 in SOQL. Its also mentioned in documentation- If you specify a limit of 0, no records are returned for that object. – Raul Feb 21 '17 at 22:52
  • 1
    @manza Right, this would still count against your 100 transactional limit on SOQL queries, just not the 50000 total rows limit. – IllusiveBrian Nov 19 '18 at 2:30

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.