Getting error on HTTP callout request "uncommitted work pending"

Question

I've got a fairly long code piece here that aims to extract all the information I can from multiple reports using report metadata. I have a file where I have the names of reports in each line, so I can loop through each report by their name.

StaticResource sr = [select body from StaticResource Where Name = 'test'];
List<String> reportNames = sr.body.toString().replaceAll('\r\n','\n').replaceAll('\r','\n').split('\n');
Report[] reportsInfo = [SELECT Name, FolderName FROM Report WHERE Name = :reportNames];

for(Report reportRecord: reportsInfo) {
    
    // get report folder api name
    String fname = (String) reportRecord.get('FolderName');
    List <Folder> folderAPI = [SELECT DEVELOPERNAME FROM FOLDER where Name = :fname];
    
    // Get report ID
    String reportId = (String)reportRecord.get('Id');
    
    // Use report ID to get report Metadata
    Reports.ReportMetadata metadata = Reports.ReportManager.describeReport(reportId).getReportMetadata();

    // Run a report
    Reports.reportResults results = Reports.ReportManager.runReport(reportId, true);

    // Get the report extended metadata
    Reports.ReportExtendedMetadata rmd = results.getReportExtendedMetadata();   

    // begin collecting and formatting data
    string header = 'Report Info \n\n';
    string finalstr = header ;
    
    // report name, type, folder
    string recordString1 = 'Report Name: '+metadata.getName() +'\n'+ 'Report API Name: '+metadata.getDeveloperName() +'\n'
                      + 'Report Type Name: ' + metadata.getReportType().getLabel() + '\n'
                      +'Report Type API Name: '+ metadata.getReportType().getType() + '\n'+ 'Report Folder: ' 
                      + fname +'\n' + 'Report Folder API Name: ' + folderAPI.get(0).get('DEVELOPERNAME') + '\n\n';


    // report filters
    string recordString11 = 'Report Filters: \n';
    for(Reports.ReportFilter rf : metadata.getreportFilters()){
        recordString11 += 'Filter Name: '+rf.getcolumn() + ',  Filter Value: '+rf.getValue() + '\n';
    }
    
    // report cross filters
    string recordString12 = 'Report Cross Filters: \n';
    for(Reports.CrossFilter rf : metadata.getCrossFilters()){
        recordString12 += 'Filter Object: '+rf.getRelatedEntityJoinField() + ',  Filter Value: '+rf.getRelatedEntity() + '\n';
    }
    
    // report date, scope
    string recordString2 =  recordString11 +'\n\n' + recordString12 +'\n\n' + 
        'Report Start Date: '+ metadata.getStandardDateFilter().getStartDate()+'\n' + 
        'Report End Date: ' + metadata.getStandardDateFilter().getEndDate() + '\n'
        + 'Report Scope: ' + metadata.getScope() + '\n\n';

    
    // report groupings info
    Map<String,Reports.GroupingColumn> groupings = rmd.getGroupingColumnInfo();
    string recordString3 = 'Groups: \n';
    
    // used to give numbered list in output
    Integer count_a = 1;
    
    for (String key : groupings.keySet()) {
        recordString3 += count_a + ': ' + groupings.get(key).getLabel() + '\n';
        /*+ ' , Type: ' + groupings.get(key).getDataType() */
        //This line gives the datatype of the groupings
        count_a += 1;
    }
    
    // report column info
    Map<String,Reports.DetailColumn> colMap = rmd.getDetailColumnInfo();
    
    // used to give numbered list in output
    Integer count_b = 1;
    string recordString4 = '\nColumns: \n';
    for(String key : colMap.KeySet()){
        recordString4 += count_b + ': ' + 'Column Label =  ' + colMap.get(key).getLabel() + ' ,  Column API Name = ' + Key + '\n';
        count_b += 1;
    }


    // report fields info
    
    // set new http callout request 
    Http http = new Http();
    HttpRequest req = new HttpRequest();
    
    // use rest api with appropriate parameters to get info about report's fields and its parent object 
    req.setEndpoint(URL.getSalesforceBaseUrl().toExternalForm() + '/services/data/v51.0/analytics/reports/' + reportId + '/describe');
    req.setHeader('Authorization', 'Bearer ' + UserInfo.getSessionId());
    req.setMethod('GET');
    
    HttpResponse res = http.send(req);
    Map<String, Object> resBody = (Map<String, Object>) JSON.deserializeUntyped(res.getBody());
    Map<String, Object> reportExtendedMetadata = (Map<String, Object>) resBody.get('reportExtendedMetadata');
    Map<String, Object> detailColumnInfo = (Map<String, Object>) reportExtendedMetadata.get('detailColumnInfo');
    
    String recordString5 = '\nField and its Parent Object: \n';
    for (String key : detailColumnInfo.keySet()) {
        Map<String, Object> tmp = (Map<String, Object>) detailColumnInfo.get(key);
        recordString5 += (String) tmp.get('label') + ' -> ' + (String) tmp.get('entityColumnName') + ' -> ' + ((String) tmp.get('entityColumnName'))?.substringBefore('.') + '\n'; 
        // E.g, Account Name -> Account.Name -> Account
    }
    
    
    //putting it all together
    finalstr = finalstr +recordString1 + recordString2 + recordString3 + recordString4  + recordString5 + '\n\n';
    
    
    // create a csv file to send the data to, and then download the file into your org
    ContentVersion file = new ContentVersion(
        title = 'asset.csv',
        versionData = Blob.valueOf(finalstr),
        pathOnClient = '/accounts.csv'
    );
    
    insert file;

}

The problem I'm having right now is near the end with this code:

// set new http callout request 
    Http http = new Http();
    HttpRequest req = new HttpRequest();
    
    // use rest api with appropriate parameters to get info about report's fields and its parent object 
    req.setEndpoint(URL.getSalesforceBaseUrl().toExternalForm() + '/services/data/v51.0/analytics/reports/' + reportId + '/describe');
    req.setHeader('Authorization', 'Bearer ' + UserInfo.getSessionId());
    req.setMethod('GET');
    
    HttpResponse res = http.send(req);
    Map<String, Object> resBody = (Map<String, Object>) JSON.deserializeUntyped(res.getBody());
    Map<String, Object> reportExtendedMetadata = (Map<String, Object>) resBody.get('reportExtendedMetadata');
    Map<String, Object> detailColumnInfo = (Map<String, Object>) reportExtendedMetadata.get('detailColumnInfo');
    
    String recordString5 = '\nField and its Parent Object: \n';
    for (String key : detailColumnInfo.keySet()) {
        Map<String, Object> tmp = (Map<String, Object>) detailColumnInfo.get(key);
        recordString5 += (String) tmp.get('label') + ' -> ' + (String) tmp.get('entityColumnName') + ' -> ' + ((String) tmp.get('entityColumnName'))?.substringBefore('.') + '\n'; 
        // E.g, Account Name -> Account.Name -> Account
    }

On this very first line it gives me an System.CalloutException: You have uncommitted work pending. Please commit or rollback before calling out error. This part of the code is responsible for finding the information about the report fields and their parent object. I used this same method before when I was building this program to run on one report at a time. Now I'm trying to do it for multiple reports at a time through a loop and getting this error. How can I fix this? Or is there another way I can get the info I want without using my method? Any help is appreciated.

Thanks.

Answer 1

The issue here, assuming your provided code is correct, is the DML that you're performing at (what I presume is) the end of your loop.

You're making your first callout just fine, then performing DML, and then trying to make the next callout. Making a callout after performing any DML is not allowed, so you get the error on your second iteration of the loop.

Fixing this should be as simple as removing that DML from your loop. DML inside of a loop is at least as bad as having a query in a loop, and has the potential to be much worse. You should never have DML inside of a loop unless you can articulate why it needs to be in a loop.

Just create a list to hold the ContentVersion records you generate, add to the list inside of your loop, then DML outside of the loop.

List<ContentVersion> contentVersionsToInsert = new List<ContentVersion>();

for(...){
    // callout code

    // DML inside of a loop is bad
    // gather things in a list, and perform the dml outside the
    //   loop instead
    //insert file;
    contentVersionsToInsert.add(file);
}

insert contentVersionsToInsert;