+ Start a Discussion
Nandhakumar MuraliNandhakumar Murali 

Need help with APEX Batch job error

I'm not a developer, i'm trying to schedule the below job but I recevie "First error: You have uncommitted work pending. Please commit or rollback before calling out". Not sure how to fix this, can some one help, below is the code of the apex calss I'm trying to schedule.

Backgroupd : Brightlocal is a SEO tool and this class is trying to bring ranking infomration into salesforce.

global class BrightLocal_DownloadSeoReports implements Database.Batchable<sObject>, Database.AllowsCallouts {

  private static LogEmail email = new LogEmail('Download of SEO Reports from Bright Local');

  global Database.QueryLocator start(Database.BatchableContext BC) {

    try {
      email.Log('Starting Bright Local SEO download');

      Date today = Date.today();
      Integer day = today.day();

      email.Log('Using Today:', '' + today);
      email.Log('Using Day:', day);

      // TODO: Assess if this should run more than once a night
      // to account for failures and more than 100 records per day @ EOM
      Database.QueryLocator locator = Database.getQueryLocator([
        SELECT Ranking_Report_Link__c
        FROM Asset__c
        WHERE Ranking_Report_Link__c != null
        AND (BrightLocal_Last_Run_Date__c = NULL OR BrightLocal_Last_Run_Date__c < :today)
        AND Day_of_Month__c <= :day
        LIMIT 100  // Limit to 100 for external HTTP requests
      ]);

      email.Log('Query used to find assets:', locator.getQuery());
      email.Send();

      return locator;
    } catch(Exception ex) {
      email.Log(ex);
      email.Send();
      throw ex;
    }
  }


  global void execute(Database.BatchableContext BC, List<sObject> scope) {
    try {
      List<Asset__c> assets = (List<Asset__c>)scope;

      email.Log('Asset Count:', assets.size());
      if (assets.size() >= 100) {
        email.Log(LogEmail.MsgType.WARNING, '100 Assets selected.  It is likely that the job will need to run again.');
      }

      for (Asset__c asset : assets) {
        DownloadReport(asset.Id, asset.Ranking_Report_Link__c);
      }
      email.Send();
    } catch(Exception ex) {
      email.Log(ex);
      email.Send();
      throw ex;
    }
  }


  global void finish(Database.BatchableContext BC) {
    System.debug('Finished Download of SEO Reports');
    email.Send();
  }


  private static void DownloadReport(ID assetId, String reportUrl) {
    email.Log('Downloading Report for Asset:', assetId + ' - ' + reportUrl);

    // Download the Report from Bright Local
    HttpRequest req = new HttpRequest();
    req.setEndpoint(reportUrl + '.csv');
    req.setMethod('GET');

    Http http = new Http();
    HTTPResponse res = http.send(req);


    // Create a Summary and Summary Line items based on the Bright Local report
    SEO_Result_Summary__c summary = new SEO_Result_Summary__c();
    summary.Asset__c  = assetId;

    List<SEO_Summary_Line_Item__c> lineItems = new List<SEO_Summary_Line_Item__c>();

    //List<String> lines = res.getBody().split('\n');
    List<List<String>> lines = parseCSV(res.getBody(), true);

    for(List<String> line : lines) {
      // if (line.indexOf('"Search Term"') == 0) continue;
      // List<String> values = line.split(',', -1);

      List<String> values = line;
      // // If the size is not 10, skip as the row has incomplete data.
      if (values.size() < 10) continue;

      for(Integer i=0; i<values.size(); i++) {
        if (values[i] != 'n/a') continue;
        values[i] = null;
      }

      // Calculate current and previous ranks
      Integer currentRank = Rankings.containsKey(values[Rank]) ? Rankings.get(values[Rank]) : 0;
      Integer previousRank = Rankings.containsKey(values[Last_Rank]) ? Rankings.get(values[Last_Rank]) : 0;

      lineItems.add(new SEO_Summary_Line_Item__c(
        Last_Rank__c = TryParse(values[Last_Rank]),
        Page__c = TryParse(values[Page]),
        Rank__c = TryParse(values[Rank]),
        Result_URL__c = values[Result_URL],
        Search_Engine__c = values[Search_Engine],
        Search_Term__c = values[Search_Term],
        Search_URL__c = values[Search_URL],
        Type__c = values[Type],
        Score__c = currentRank - previousRank
      ));
    }

    email.Log('# of Line Items :', lineItems.size());
    // Don't insert a Summary or Line items if none exist
    if (lineItems.size() == 0) return;

    insert summary;
    for(SEO_Summary_Line_Item__c lineItem : lineItems) {
      lineItem.SEO_Result_Summary__c = summary.id;
    }
    insert lineItems;

  }


  // See: https://developer.salesforce.com/page/Code_Samples
  public static List<List<String>> parseCSV(String contents, Boolean skipHeaders) {
    List<List<String>> allFields = new List<List<String>>();

    // replace instances where a double quote begins a field containing a comma
    // in this case you get a double quote followed by a doubled double quote
    // do this for beginning and end of a field
    contents = contents.replaceAll(',"""',',"DBLQT').replaceall('""",','DBLQT",');
    // now replace all remaining double quotes - we do this so that we can reconstruct
    // fields with commas inside assuming they begin and end with a double quote
    contents = contents.replaceAll('""','DBLQT');
    // we are not attempting to handle fields with a newline inside of them
    // so, split on newline to get the spreadsheet rows
    List<String> lines = new List<String>();
    try {
      lines = contents.split('\n');
    } catch (System.ListException e) {
      System.debug('Limits exceeded?' + e.getMessage());
    }
    Integer num = 0;
    for(String line : lines) {
      // check for blank CSV lines (only commas)
      if (line.replaceAll(',','').trim().length() == 0) break;

      List<String> fields = line.split(',');
      List<String> cleanFields = new List<String>();
      String compositeField;
      Boolean makeCompositeField = false;
      for(String field : fields) {
        if (field.startsWith('"') && field.endsWith('"')) {
          cleanFields.add(field.replaceAll('DBLQT','"'));
        } else if (field.startsWith('"')) {
          makeCompositeField = true;
          compositeField = field;
        } else if (field.endsWith('"')) {
          compositeField += ',' + field;
          cleanFields.add(compositeField.replaceAll('DBLQT','"'));
          makeCompositeField = false;
        } else if (makeCompositeField) {
          compositeField +=  ',' + field;
        } else {
          cleanFields.add(field.replaceAll('DBLQT','"'));
        }
      }

      allFields.add(cleanFields);
    }
    if (skipHeaders) allFields.remove(0);
    return allFields;
  }


  // Parse a Decimal and return a zero if un-parsable
  private static Decimal TryParse(String value) {
    try {
      return Decimal.valueOf(value);
    } catch(Exception ex) {
      return 0;
    }
  }


  // Bright Local report headers by index
  private static final Integer Search_Term    = 0;
  private static final Integer Search_Engine  = 1;
  private static final Integer Search_URL     = 2;
  private static final Integer Result_URL     = 3;
  private static final Integer Rank           = 4;
  private static final Integer Page           = 5;
  private static final Integer Type           = 6;
  private static final Integer Match          = 7;
  private static final Integer Directory      = 8;
  private static final Integer Last_Rank      = 9;


  // These rankings are used to calculate a final score.
  // They can be changed as needed, but should be ranged so that moving from
  // 1 -> 2 is much better than moving from 2 -> 3
  private static final Map<String, Integer> Rankings = new Map<String, Integer>
    { '1'  => 360
    , '2'  => 72
    , '3'  => 18
    , '4'  => 6
    , '5'  => 3
    , '6'  => 2
    , '7'  => 2
    , '8'  => 2
    , '9'  => 2
    , '10' => 2
    };
}
AmitdAmitd

Hi Nandha,

Problem is you can not do DML before callout in same request.

Check DownloadReport method. You are calling this method in for loop. In this method you have an Insert DML statement. If this method get called more than one time you will definitely get this error message. I would suggest you to call it once . Instead of using single Id as a parameter you should use set<Id> and then prepare a list of records you want to insert.

it should be something like :

Replace below code 

for (Asset__c asset : assets) {
        DownloadReport(asset.Id, asset.Ranking_Report_Link__c);
      }

WITH

DownloadReport(assets);

AND

Declare DownloadReport mehtod as 

DownloadReport(list<Asset__c > assetList){
//here do callout in for loop and prepare list that you want to insert and once all callouts are done you can insert your list

}

Hope this answer your question.

Nandhakumar MuraliNandhakumar Murali
Sorry if my ask to too much, I'm not a programmer, can I replace the code you just gave?
AmitdAmitd
Please replace your code with following code

global class BrightLocal_DownloadSeoReports implements Database.Batchable<sObject>, Database.AllowsCallouts {

  private static LogEmail email = new LogEmail('Download of SEO Reports from Bright Local');

  global Database.QueryLocator start(Database.BatchableContext BC) {

    try {
      email.Log('Starting Bright Local SEO download');

      Date today = Date.today();
      Integer day = today.day();

      email.Log('Using Today:', '' + today);
      email.Log('Using Day:', day);

      // TODO: Assess if this should run more than once a night
      // to account for failures and more than 100 records per day @ EOM
      Database.QueryLocator locator = Database.getQueryLocator([
        SELECT Ranking_Report_Link__c
        FROM Asset__c
        WHERE Ranking_Report_Link__c != null
        AND (BrightLocal_Last_Run_Date__c = NULL OR BrightLocal_Last_Run_Date__c < :today)
        AND Day_of_Month__c <= :day
        LIMIT 100  // Limit to 100 for external HTTP requests
      ]);

      email.Log('Query used to find assets:', locator.getQuery());
      email.Send();

      return locator;
    } catch(Exception ex) {
      email.Log(ex);
      email.Send();
      throw ex;
    }
  }


  global void execute(Database.BatchableContext BC, List<sObject> scope) {
    try {
      List<Asset__c> assets = (List<Asset__c>)scope;

      email.Log('Asset Count:', assets.size());
      if (assets.size() >= 100) {
        email.Log(LogEmail.MsgType.WARNING, '100 Assets selected.  It is likely that the job will need to run again.');
      }

      //for (Asset__c asset : assets) {
        //DownloadReport(asset.Id, asset.Ranking_Report_Link__c);
      //}
      DownloadReport(assets);
      email.Send();
    } catch(Exception ex) {
      email.Log(ex);
      email.Send();
      throw ex;
    }
  }


  global void finish(Database.BatchableContext BC) {
    System.debug('Finished Download of SEO Reports');
    email.Send();
  }


  private static void DownloadReport(list<Asset__c> assets) {
    map<integer,list<SEO_Summary_Line_Item__c>> lineItemsMap = new map<integer,list<SEO_Summary_Line_Item__c>>();
    map<integer,SEO_Result_Summary__c> summaryMap = new map<integer,SEO_Result_Summary__c>();
    integer i=0;
    for(Asset__c asset : assets){
    i++;    
    email.Log('Downloading Report for Asset:', asset.Id + ' - ' + asset.Ranking_Report_Link__c);

    // Download the Report from Bright Local
    HttpRequest req = new HttpRequest();
    req.setEndpoint(asset.Ranking_Report_Link__c + '.csv');
    req.setMethod('GET');

    Http http = new Http();
    HTTPResponse res = http.send(req);


    // Create a Summary and Summary Line items based on the Bright Local report
    SEO_Result_Summary__c summ = new SEO_Result_Summary__c(Asset__c  = asset.Id)
    
    summaryMap.put(i,summ);
    List<SEO_Summary_Line_Item__c> lineItems = new List<SEO_Summary_Line_Item__c>();

    //List<String> lines = res.getBody().split('\n');
    List<List<String>> lines = parseCSV(res.getBody(), true);

    for(List<String> line : lines) {
      // if (line.indexOf('"Search Term"') == 0) continue;
      // List<String> values = line.split(',', -1);

      List<String> values = line;
      // // If the size is not 10, skip as the row has incomplete data.
      if (values.size() < 10) continue;

      for(Integer i=0; i<values.size(); i++) {
        if (values[i] != 'n/a') continue;
        values[i] = null;
      }

      // Calculate current and previous ranks
      Integer currentRank = Rankings.containsKey(values[Rank]) ? Rankings.get(values[Rank]) : 0;
      Integer previousRank = Rankings.containsKey(values[Last_Rank]) ? Rankings.get(values[Last_Rank]) : 0;
      
      lineItems.add(new SEO_Summary_Line_Item__c(
        Last_Rank__c = TryParse(values[Last_Rank]),
        Page__c = TryParse(values[Page]),
        Rank__c = TryParse(values[Rank]),
        Result_URL__c = values[Result_URL],
        Search_Engine__c = values[Search_Engine],
        Search_Term__c = values[Search_Term],
        Search_URL__c = values[Search_URL],
        Type__c = values[Type],
        Score__c = currentRank - previousRank
      ));
    }
    if(lineItems.size()>0)
       lineItemsMap.put(i,lineItems);

    email.Log('# of Line Items :', lineItems.size());
    // Don't insert a Summary or Line items if none exist
    
    }
    if (lineItemsMap.size() > 0){
        
        insert summaryMap.values();
        
        for(Integer i : lineItemsMap.keyset()){
            for(SEO_Summary_Line_Item__c lineItem : lineItemsMap.get(i)) {
              lineItem.SEO_Result_Summary__c = summaryMap.get(i).id;
            }
        }
        
        
        insert lineItems;
    }

  }


  // See: https://developer.salesforce.com/page/Code_Samples
  public static List<List<String>> parseCSV(String contents, Boolean skipHeaders) {
    List<List<String>> allFields = new List<List<String>>();

    // replace instances where a double quote begins a field containing a comma
    // in this case you get a double quote followed by a doubled double quote
    // do this for beginning and end of a field
    contents = contents.replaceAll(',"""',',"DBLQT').replaceall('""",','DBLQT",');
    // now replace all remaining double quotes - we do this so that we can reconstruct
    // fields with commas inside assuming they begin and end with a double quote
    contents = contents.replaceAll('""','DBLQT');
    // we are not attempting to handle fields with a newline inside of them
    // so, split on newline to get the spreadsheet rows
    List<String> lines = new List<String>();
    try {
      lines = contents.split('\n');
    } catch (System.ListException e) {
      System.debug('Limits exceeded?' + e.getMessage());
    }
    Integer num = 0;
    for(String line : lines) {
      // check for blank CSV lines (only commas)
      if (line.replaceAll(',','').trim().length() == 0) break;

      List<String> fields = line.split(',');
      List<String> cleanFields = new List<String>();
      String compositeField;
      Boolean makeCompositeField = false;
      for(String field : fields) {
        if (field.startsWith('"') && field.endsWith('"')) {
          cleanFields.add(field.replaceAll('DBLQT','"'));
        } else if (field.startsWith('"')) {
          makeCompositeField = true;
          compositeField = field;
        } else if (field.endsWith('"')) {
          compositeField += ',' + field;
          cleanFields.add(compositeField.replaceAll('DBLQT','"'));
          makeCompositeField = false;
        } else if (makeCompositeField) {
          compositeField +=  ',' + field;
        } else {
          cleanFields.add(field.replaceAll('DBLQT','"'));
        }
      }

      allFields.add(cleanFields);
    }
    if (skipHeaders) allFields.remove(0);
    return allFields;
  }


  // Parse a Decimal and return a zero if un-parsable
  private static Decimal TryParse(String value) {
    try {
      return Decimal.valueOf(value);
    } catch(Exception ex) {
      return 0;
    }
  }


  // Bright Local report headers by index
  private static final Integer Search_Term    = 0;
  private static final Integer Search_Engine  = 1;
  private static final Integer Search_URL     = 2;
  private static final Integer Result_URL     = 3;
  private static final Integer Rank           = 4;
  private static final Integer Page           = 5;
  private static final Integer Type           = 6;
  private static final Integer Match          = 7;
  private static final Integer Directory      = 8;
  private static final Integer Last_Rank      = 9;


  // These rankings are used to calculate a final score.
  // They can be changed as needed, but should be ranged so that moving from
  // 1 -> 2 is much better than moving from 2 -> 3
  private static final Map<String, Integer> Rankings = new Map<String, Integer>
    { '1'  => 360
    , '2'  => 72
    , '3'  => 18
    , '4'  => 6
    , '5'  => 3
    , '6'  => 2
    , '7'  => 2
    , '8'  => 2
    , '9'  => 2
    , '10' => 2
    };
}
Nandhakumar MuraliNandhakumar Murali
Thanks for the code, there wer fer erros I wa able to fix, but there is a error in line 140 Varibale does not exists: lineItems

I was able to figure that lineitems insert lineitmes is used out of scope , but not sure where to place that.