11 Replies Latest reply on Nov 3, 2010 6:26 PM by Bernd Groh

    Salesforce.com limit?

    Bernd Groh Newbie

      Hi,

       

      I've added a connection to Salesforce.com to my VDB, and I can view the tables and sample data of the deployed VDB in the Database Development perspective of the Designer. Yet, if I make any ordinary query it seems to fail, and all I get in return is the following message:

       

      Index: 2000, Size: 2000

       

      Does anyone know what I'm missing here?

       

      Thanks,

      Bernd

        • 1. Re: Salesforce.com limit?
          Bernd Groh Newbie

          I should add, I don't seem to have that problem with tables that have a row count of <= 2000, nor do I seem to have that problem if I explicity use limit to limit the rows to <= 2000. Otherwise, there's no result, and all I get is the given message.

           

          Is that a Teiid thing? Could it be a limit of my Salesforce.com account? Either way, is there any way around this (other than having to use LIMIT, that is)?

           

          Thanks,

          Bernd

          • 2. Re: Salesforce.com limit?
            Bernd Groh Newbie

            (other than having to use LIMIT, that is)

             

            For testing purposes, I was actually trying to use LIMIT to get the data. Yet, none of the known formats to specify the offset seemed to work. How exactly do you specify the offset when querying Salesforce?

             

            Thanks,

            Bernd

            • 3. Re: Salesforce.com limit?
              Steven Hawkins Master

              This is probably related to https://jira.jboss.org/browse/TEIID-1302.  When using older Saleforce API versions it seems like we are missinterpretting the result count after ther first batch.  If you try with 7.2 Beta 1 the issue should be resolved.

              • 4. Re: Salesforce.com limit?
                Bernd Groh Newbie

                Steven Hawkins wrote:

                 

                This is probably related to https://jira.jboss.org/browse/TEIID-1302.  When using older Saleforce API versions it seems like we are missinterpretting the result count after ther first batch.  If you try with 7.2 Beta 1 the issue should be resolved.

                 

                Yes, thanks! We've switched to 7.2 Beta 1 (from 7.2 Alpha 2) and the issue seems resolved.

                • 5. Re: Salesforce.com limit?
                  Bernd Groh Newbie

                  Bernd Groh wrote:

                   

                  Steven Hawkins wrote:

                   

                  This is probably related to https://jira.jboss.org/browse/TEIID-1302.  When using older Saleforce API versions it seems like we are missinterpretting the result count after ther first batch.  If you try with 7.2 Beta 1 the issue should be resolved.

                   

                  Yes, thanks! We've switched to 7.2 Beta 1 (from 7.2 Alpha 2) and the issue seems resolved.

                   

                  Many apologies, but I'll have to take that back. What seems to have gone is the error message, but that seems to be all that's changed. I don't actually get the full data set, I only get the first 2000 rows. So, instead of giving me an error when the result set is bigger than 2000 rows, it now simply seems to truncate the result set to 2000 rows, without telling me about it. I only realized this now, with a deeper analysis of the result set. So, what exactly do I need to do here in order to get the full result set?

                   

                  Is there a way to use pagination? So far, I've not been able to specify an offset. Is there a special format for salesforce to specify an offset? Or is there another way to retrieve the full data set, not just the first 2000 rows?

                   

                  Thanks,

                  Bernd

                  • 6. Re: Salesforce.com limit?
                    Steven Hawkins Master

                    Bernd,

                     

                    You should get the whole result.  We can look further at reproducing - what version of the Salesforce API are you using, what is the expected size of the results, and what are your connector/processor batch size settings?

                     

                    Teiid does support both row limit and offset using the mysql style limit clause: SELECT ... LIMIT [offset] rowcount.  The rowcount applies after the offset is applied.

                     

                    Putting a limit on the user query though may not make it all the way to source query if there are intervening operations that do not allow the limit to pass.

                     

                    Steve

                    • 7. Re: Salesforce.com limit?
                      Bernd Groh Newbie

                      You should get the whole result.  We can look further at reproducing - what version of the Salesforce API are you using, what is the expected size of the results, and what are your connector/processor batch size settings?

                       

                      How do you find out what Salesforce API version you're connecting to? The expected result size could be anywhere between 5000 and 30000+ rows, and the connector settings would be whatever is default. I haven't changed anything in the connector. Or are you referring to other settings?

                       

                      Teiid does support both row limit and offset using the mysql style limit clause: SELECT ... LIMIT [offset] rowcount.  The rowcount applies after the offset is applied.

                       

                      Putting a limit on the user query though may not make it all the way to source query if there are intervening operations that do not allow the limit to pass.

                       

                      Hmm, ok, I've tried that format as well, and all I get is the following:

                       

                      Remote org.teiid.api.exception.query.QueryParserException: Parsing error: Encountered "2000" at line 3, column 12.
                      Was expecting one of:
                          <EOF>
                          "," ...
                          ";" ...
                          "option" ...

                       

                      `LIMIT rowcount` works as expected. Trying to put an offset into the sql results in above error. It would simply be a workaround, though, I'd much rather get the entire result set in one go.

                       

                      Cheers,

                      Bernd

                      • 8. Re: Salesforce.com limit?
                        Steven Hawkins Master

                        Bernd,

                         

                        Sorry, I forgot the comma in my LIMIT, it should be LIMIT [offset, ] rowcount

                         

                        Since SalesForce doesn't support an offset though, it wouldn't actually change the problem that you're having.

                         

                        The SalesForce API version should be part of the connection url - https://www.salesforce.com/services/Soap/u/<version>

                         

                        As I was responding to you I looked more at the code andd see the issue in QueryExecutionImpl.  I'll log an JIRA and work it.

                         

                        Steve

                        • 9. Re: Salesforce.com limit?
                          Steven Hawkins Master

                          Checked in under https://jira.jboss.org/browse/TEIID-1302, which will be part of the 7.2 release.

                           

                          Thanks for catching this,

                          Steve

                          • 10. Re: Salesforce.com limit?
                            Bernd Groh Newbie

                            Sorry, I forgot the comma in my LIMIT, it should be LIMIT [offset, ] rowcount

                             

                            Naturally, that was the first format I had tried. And yes, had the exact same issue. It's not that important, though, as using limit would only have been a workaround for me.

                            • 11. Re: Salesforce.com limit?
                              Bernd Groh Newbie

                              Checked in under https://jira.jboss.org/browse/TEIID-1302, which will be part of the 7.2 release.

                               

                              Thanks. Definitely looking forward to the 7.2 release.

                               

                              Bernd