Salesforce.com limit?

[bgroh]

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

[bgroh]

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

[bgroh]

(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

[shawkins]

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.

[bgroh]

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.

[bgroh]

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

[shawkins]

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

[bgroh]

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

[shawkins]

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

[shawkins]

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

Thanks for catching this,

Steve

[bgroh]

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.

[bgroh]

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