Catching Limit exception rows

Question

i want to catch the limit exception, but I do not how. This is my code:

queryCount = 'SELECT count(id) result FROM Product2 ';
queryCount = queryCount + getWhereClause();    
results = database.query(queryCount);

I have more than 70k Product2 records. How I can catch the exception to avoid put this error? I want to put more friendly error message

Too many query rows: 50001 Error is in expression '{!search}' in component in page pgselectproductwholeseller: Class.CnSelectProductWholeseller.search: line 112, column 1

Answer 1

A couple things. Note right off the bat that the documentation on Exception Class and Built-In Exceptions states LimitException cannot be caught. You have to proactively prevent it.

---

If it fits your needs, you can use the @ReadOnly annotation to raise the limits of your page. Here is the relevant section of the documentation:

The @ReadOnly annotation allows you to perform unrestricted queries against the Force.com database...

Visualforce pages can call controller methods with the @ReadOnly annotation, and those methods will run with the same relaxed restrictions. To increase other Visualforce-specific limits, such as the size of a collection that can be used by an iteration component like <apex:pageBlockTable>, you can set the readonly attribute on the <apex:page> tag to true. For more information, see Working with Large Sets of Data in the Visualforce Developer's Guide.

If you don't need to perform any DML on your page, the above is by far the simplest option.

---

If you do need to perform DML, but still want to be able to know the number of Product2 records in your org on demand, I would consider writing a batch to save the data in a Hierarchy Custom Setting. It can be dead simple

public with sharing class Throwaway implements Database.Batchable<SObject>, Database.Stateful
{
    final SObjectType toCount;
    Integer count;
    public Throwaway(SObjectType toCount)
    {
        this.toCount = toCount;
        this.count = 0;
    }
    public Throwaway execute()
    {
        Database.executeBatch(this, 50000);
        return this;
    }
    
    public Database.QueryLocator start(Database.BatchableContext context)
    {
        return Database.getQueryLocator('SELECT Id FROM ' + toCount);
    }
    public void execute(Database.BatchableContext context, List<SObject> scope)
    {
        this.count += scope.size();
    }
    public void finish(Database.BatchableContext context)
    {
        // Save to a Hierarchy Custom Setting - Org Default
        // You should probably accept an additional SObjectField parameter
        // If you wanted this solution to store this data for multiple objects
    }
}

Then you can get the number of records as simply as:

Integer product2Count = ObjectCounts__c.getOrgDefaults().Product2__c;

---

It doesn't really come into play here, but you should know there is a method on the Database class called countQuery. If you only want to know the number of records a query returns, this method is preferable. It doesn't change your row limits (though it should consume marginally less heap and CPU time). But on a smaller table you could do:

Integer product2Count = Database.countQuery('SELECT count() FROM Product2');

---

As a last resort, you can find the maximum number of remaining rows using the Limits class.

Integer remainingRows = Limits.getLimitQueryRows() - Limits.getQueryRows();
query += ' LIMIT :remainingRows';
Integer queriedRows = Database.countQuery(query);

However, the above will always return remainingRows and exhaust what limit remains.

Answer 2

There is no way by which you can catch a limit exception but a code of below type can help:

queryCount = 'SELECT count(id) result FROM Product2 ';
queryCount = queryCount + getWhereClause() +' limit 50000';    
results = database.query(queryCount);

if(results.size()==50000){
/*This means that there were  50000 or more records in the database.*/
    apexpages.Message msg = new Apexpages.Message(ApexPages.Severity.Error,'Error:There were 50000 or more records to process');
    apexpages.addmessage(msg);
    }

The only problem with the above code would be it will work only up to 49999 records and will start failing thereafter.