8

Here is the scenario, when I receive error message.

"Apex error occurred while executing Site URL Rewriter"

I have a link in my VF page to export records as excel. on click of this export link, I call VF page which has content type as "application/vnd.ms-excel#Test.xls"

After implementing URLRewrite, whenever I click on export, it ends up with the above error message. In the debug log it is logged as "Too many SOQL queries: 101"

Below is the URLRewrite class: global class URLRedirect_URLRewriter implements Site.UrlRewriter {

    //Variables to represent the user-friendly URLs for     
    //foo pages 

    String FOO_PAGE = '/foo/';

    //Variables to represent my custom Visualforce pages     

    String FOO_VISUALFORCE_PAGE = '/FooDetails?fooid=';    
    String LIST_FOO_VISUALFORCE_PAGE = '/Foo';

    global PageReference mapRequestUrl(PageReference myFriendlyUrl)
    {
        String url = myFriendlyUrl.getUrl();        
        if(url.startsWith(FOO_PAGE) && !url.contains('&'))
        {
            String name = url.substring(FOO_PAGE.length(), url.length());
            IPFooObject grp;
            try
            {  
                grp = [select Id,Name,Name__c from IPFooObject where Name =:name LIMIT 1];
                if (grp != null)
                {
                    return new PageReference(FOO_VISUALFORCE_PAGE + grp.id);
                }
                else
                {
                    return new PageReference(LIST_FOO_VISUALFORCE_PAGE);
                }
            }
            catch(exception ex)
            {
                return new PageReference(LIST_FOO_VISUALFORCE_PAGE);
            }
        }
        //If the URL isn't in the form of a foo page, continue with the request 

        return null;
    }

    global List<PageReference> generateUrlFor(List<PageReference> mySalesforceUrls)
    {
        //A list of pages to return after all the links  
        //have been evaluated 

        List<PageReference> myFriendlyUrls = new List<PageReference>();

        //a list of all the ids in the urls 

        List<id> grpIds = new List<id>();

        // loop through all the urls once, finding all the valid ids 
        List<String> lstString = new List<String>();
        for(PageReference mySalesforceUrl : mySalesforceUrls)
        {
            lstString.add(mySalesforceUrl.getUrl());
        }

        for(PageReference mySalesforceUrl : mySalesforceUrls)
        {
            //Get the URL of the page     
            String url = mySalesforceUrl.getUrl();        
            if(url.startsWith(FOO_VISUALFORCE_PAGE) && !url.contains('&'))
            {
                //Extract the ID from the query parameter 
                //and store in a list 
                //for querying later in bulk. 
                string id= url.substring(FOO_VISUALFORCE_PAGE.length(),url.length());
                grpIds.add(id);
            }
        }

        // Get all the group names in bulk 
        List <IPFooObject> groups = [SELECT Name FROM IPFooObject WHERE Id IN :grpIds];
        // make the new urls 

        Integer counter = 0;

        // it is important to go through all the urls again, so that the order 
        // of the urls in the list is maintained.  

        for(PageReference mySalesforceUrl : mySalesforceUrls) 
        {
           //Get the URL of the page 
           String url = mySalesforceUrl.getUrl();

           if(url.startsWith(FOO_VISUALFORCE_PAGE)  && !url.contains('&'))
           {
                myFriendlyUrls.add(new PageReference(FOO_PAGE + groups.get(counter).name));
                counter++;
           } 
           else 
           {
                //If this doesn't start like a groups page,     
                //don't do any transformations     
                myFriendlyUrls.add(mySalesforceUrl);
           }
        }
        return myFriendlyUrls;
  }

}

While debugging I noticed that, below line is getting is executed more than once. List groups = [SELECT Name FROM IPFooObject WHERE Id IN :grpIds];

Why is the line getting executed so many times? What's wrong with the code?

Here is the VFPage being called through commandLink:

<apex:page readOnly="false" controller="myController" contentType="application/vnd.ms-excel#{!IF(AND((ExportTitle <> null),(ExportTitle <> '')),ExportTitle,'Patents')}.xls" showHeader="false" standardStylesheets="false" action="{!PageLoad}" cache="true">
    <apex:stylesheet value="{!URLFOR($Resource.IVExchange_Styles, 'CSS/PageStyles.css')}" />
    <apex:form >
        <apex:outputPanel id="FooExport">
            <apex:dataTable value="{!lstFooInfo}" var="lst" id="FooListTable" styleClass="fullwidth" cellpadding="5" headerClass="userTableHeaderWidth" rowClasses="oddRow TextPriority10, evenRow TextPriority10" border="1">
                <apex:column >
                    <apex:facet name="header"><span class="userTableListHeader alignLeft">Group No.</span></apex:facet>
                    <apex:outputText value="{!lst.GroupNumber}"></apex:outputText>
                </apex:column>
                <apex:column >
                    <apex:facet name="header"><span class="userTableListHeader">Group Name</span></apex:facet>
                    <apex:outputText value="{!lst.GroupName}"></apex:outputText>
                </apex:column>
                <apex:column >
                    <apex:facet name="header"><span class="userTableListHeader">Issue Date</span></apex:facet>
                    <apex:outputText value="{0,date,yyyy'-'MM'-'dd}">
                        <apex:param value="{!lst.IssueDate}" />
                    </apex:outputText>
                </apex:column>
                <apex:column rendered="{!if((LOWER($CurrentPage.parameters.seltab) == 'licensed' && ($CurrentPage.parameters.grpid != '')),true,false)}">
                    <apex:facet name="header"><span class="userTableListHeader">Participating</span></apex:facet>
                    <apex:outputText value="{!lst.Participating}"></apex:outputText>                                                         
                </apex:column>
            </apex:dataTable>       
        </apex:outputPanel>
    </apex:form>
</apex:page>
3

First thing which popups in my mind is loop of a page which calls that code.

First thing I would do is to remove/comment/whatever lines with SOQL to be sure that the problem is not in SOQL. I think the problem is in looping request to the page.

  • request to the page is made only once. Not in loop. But there is max of 1000 to be exported. <br />function redirectJQGridurl() { self.location.href = '/SiteName/FooExport?core.apexpages.devmode.url=1&action=PopulateFooInfo&seltab='+seltab + '&returntype=Excel'; } – Selvi Moies Oct 10 '12 at 13:46
  • @SelviMoies, can you reduce this number to a 49? Just to experiment. – Andrii Muzychuk Oct 10 '12 at 14:30
  • @SelviMoies, I'm not saying that you did that. I'm saying that IT does this. – Andrii Muzychuk Oct 11 '12 at 12:42
  • thanks chiz, I got u :) As you suggested, I tried with 49 records, and it works. But fails when the number increases. – Selvi Moies Oct 11 '12 at 13:45
  • I don't know why I suggested limit to 49 :-) I noticed that I don't see where your generateUrlFor method is used. Can you please post a code where this method is used? – Andrii Muzychuk Oct 12 '12 at 5:01
3

The number of executions of your SOQL query would be directly proportional to the number of invocations of generateUrlFor() in one execution context.

I'd recommend having the map to be an instance variable that is initialized at load. Depending on how many records your IPFooObject Object has, you could just have a map that loads up the whole map at startup, rather than lazy loading, which makes sense if IPFooObject has a prohibitively high number of records.

Map <Id, IPFooObject> groups; //instance variable

 if (groups != null) //check that this is not already initialized
       groups = new Map<Id, IPFooObject>([SELECT Name FROM IPFooObject]);

Then just get from this map each time you need to use it in your various methods

  • Why does it get executed 101 times in one execution context? It is not obvious to me when looking at the Site.UrlRewriter Interface documentation. It looks like the example in the documentation has a SOQL query in its generateUrlFor() implementation. – Peter Knolle Oct 10 '12 at 12:05
  • You are right Peter. I have tried it as same as the example. But no clue of this error. – Selvi Moies Oct 10 '12 at 12:26
  • techtrekker, as you suggested I checked with map implementation. But now getting 'Too many script statements...' error. I wonder why generateUrlFor() method is invoked so many times for single url request. – Selvi Moies Oct 10 '12 at 13:03
  • where have you initiazlied the map ? hopefully not in the generateURLFor() methods - this should be either in the class constructor, or a variable, where you check if the map is not null before initiazing it. – techtrekker Oct 10 '12 at 13:19
  • Yes, I have initialized the map in the class constructor. – Selvi Moies Oct 10 '12 at 13:30
2

My aim is to handle the friendly urls. But I had been getting error while clicking on the export link. After trying different solutions, landed at this point.

Since the url invoked through this action is has no need to be validated as friendly url, I added extra if conditions in my URLRewrite class.

global PageReference mapRequestUrl(PageReference myFriendlyUrl)
{
    String url = myFriendlyUrl.getUrl();        
    if(url.startsWith(FOO_PAGE) && isNumber(url.substring(FOO_PAGE.length(),url.length())) && !url.contains('&'))
    {
        ...
    }
}

global List<PageReference> generateUrlFor(List<PageReference> mySalesforceUrls)
{
    if (mySalesforceUrls.size() > 0 && mySalesforceUrls.get(0).getUrl().startsWith(FOO_VISUALFORCE_PAGE) && !mySalesforceUrls.get(0).getUrl().contains('&'))
    {
        ...
    }
}

This solved my problem. Instead of executing loop for all the pages, it will check only for the pages required. Thank you all for your suggestions.

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.