Sales Force with TeiidEmbedded

[jchidiac]

Hi,

I'm using to connect to sales Force via Teiid Embedded server,

my teiid version is 8.8

when i execute this query  :

     select  distinct YEAR("Opportunity"."CloseDate") as "Opportunity_CloseDate1"

     from  "SalesForceInstanceModel".Campaign "Campaign"

     LEFT OUTER JOIN "SalesForceInstanceModel".Opportunity "Opportunity" ON "Campaign"."Id" = "Opportunity"."CampaignId"

      order by YEAR("Opportunity"."CloseDate") ASC LIMIT 0 , 32

i  got the following error  :

Sep 02, 2014 10:12:17 AM org.teiid.logging.JavaLogger log

WARNING: Connector worker process failed for atomic-request=jlO/UrauRUbj.0.3.599

org.teiid.translator.TranslatorException: com.sforce.soap.partner.InvalidSObjectFault: INVALID_TYPE:

(SELECT Opportunity.CloseDate FROM Opportunitys) FROM Campaign

                                   ^

ERROR at Row:1:Column:43

Didn't understand relationship 'Opportunitys' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

  at org.teiid.translator.salesforce.execution.QueryExecutionImpl.execute(QueryExecutionImpl.java:162)

  at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:326)

  at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298)

  at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)

  at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)

  at java.util.concurrent.FutureTask.run(Unknown Source)

  at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)

  at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274)

  at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)

  at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214)

  at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

  at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

  at java.lang.Thread.run(Unknown Source)

Caused by: javax.resource.ResourceException: com.sforce.soap.partner.InvalidSObjectFault: INVALID_TYPE:

(SELECT Opportunity.CloseDate FROM Opportunitys) FROM Campaign

                                   ^

ERROR at Row:1:Column:43

Didn't understand relationship 'Opportunitys' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

  at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.query(SalesforceConnectionImpl.java:194)

  at org.teiid.translator.salesforce.execution.QueryExecutionImpl.execute(QueryExecutionImpl.java:148)

  ... 12 more

Caused by: com.sforce.soap.partner.InvalidSObjectFault: INVALID_TYPE:

(SELECT Opportunity.CloseDate FROM Opportunitys) FROM Campaign

                                   ^

ERROR at Row:1:Column:43

Didn't understand relationship 'Opportunitys' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

  at sun.reflect.GeneratedConstructorAccessor930.newInstance(Unknown Source)

  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)

  at java.lang.reflect.Constructor.newInstance(Unknown Source)

  at com.sun.xml.ws.fault.SOAPFaultBuilder.createException(SOAPFaultBuilder.java:144)

  at com.sun.xml.ws.client.sei.StubHandler.readResponse(StubHandler.java:253)

  at com.sun.xml.ws.db.DatabindingImpl.deserializeResponse(DatabindingImpl.java:181)

  at com.sun.xml.ws.db.DatabindingImpl.deserializeResponse(DatabindingImpl.java:258)

  at com.sun.xml.ws.client.sei.SyncMethodHandler.invoke(SyncMethodHandler.java:117)

  at com.sun.xml.ws.client.sei.SyncMethodHandler.invoke(SyncMethodHandler.java:91)

  at com.sun.xml.ws.client.sei.SEIStub.invoke(SEIStub.java:154)

  at com.sun.proxy.$Proxy226.query(Unknown Source)

  at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.query(SalesforceConnectionImpl.java:187)

  ... 13 more

Sep 02, 2014 10:12:17 AM org.teiid.logging.JavaLogger log

WARNING: TEIID30020 Processing exception for request jlO/UrauRUbj.0 'TEIID30504 SalesForceInstance: com.sforce.soap.partner.InvalidSObjectFault: INVALID_TYPE:

(SELECT Opportunity.CloseDate FROM Opportunitys) FROM Campaign

                                   ^

ERROR at Row:1:Column:43

Didn't understand relationship 'Opportunitys' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.'. Originally TeiidProcessingException sun.reflect.GeneratedConstructorAccessor930.newInstance(Unknown Source). Enable more detailed logging to see the entire stacktrace.

[shawkins]

The issue here is with the pluralization strategy.  We are simply adding 's' to use the name Opportunitys, when it should be Opportunities.  Can you log an issue for this?

Was there any change in the status of: Teiid 8.8 Version : Issues faced with executing query on a Salesforce model in Teiid Embedded

[jchidiac]

Class    :: org.teiid.translator.salesforce.execution.QueryExecutionImpl.java

function :: execute() ;

Incoming query :: SELECT Opportunity.CloseDate FROM Campaign LEFT OUTER JOIN Opportunity ON Campaign.Id = Opportunity.CampaignId LIMIT 30

from :: List<tableReference> contains one item ==> [Campaign LEFT OUTER JOIN Opportunity ON Campaign.Id = Opportunity.CampaignId]

finalQuery :: SELECT (SELECT Opportunity.CloseDate FROM Opportunitys) FROM Campaign LIMIT 30

I think the problem is related to the following line :: visitor.visitNode(query);

and i think it's related to the Class JoinQueryVisitor, function :: visit(Join join)

For the other SalesForce issue i replied by giving u my instance information.

[shawkins]

> I think the problem is related to the following line :: visitor.visitNode(query);

I think the logic is essentially correct.  If I change Opportunitys to Opportunities, then the query works as expected.  Can you elaborate more on what you think is wrong?

[jchidiac]

when i change with debug mode the Opportunitys to Opportunities, the query works

but in my query, i dont have Opportunitys ,  my query is :

select   "Opportunity"."CloseDate"  as "Opportunity_CloseDate1"

     from  "SalesForceInstanceModel".Campaign "Campaign"

     LEFT OUTER JOIN "SalesForceInstanceModel".Opportunity "Opportunity" ON "Campaign"."Id" = "Opportunity"."CampaignId"

    LIMIT 0 , 30  

so my question why i have Opportunitys in the query sent to sales force ?

[jchidiac]

I think i find the solution :

Class    :: JoinQueryVisitor.java

function :: getQuery()

the correct function is : "Modif is marked as Bold"

@Override

  public String getQuery() throws TranslatorException {

  if (isParentToChildJoin()) {

  return super.getQuery();

  }

  if (!exceptions.isEmpty()) {

  throw exceptions.get(0);

  }

  StringBuilder select = new StringBuilder();

  select.append(SELECT).append(SPACE);

  addSelect(leftTableInJoin.getNameInSource(), select, true);

  select.append(OPEN);

  StringBuilder subselect = new StringBuilder();

  subselect.append(SELECT).append(SPACE);

  addSelect(rightTableInJoin.getNameInSource(), subselect, false);

  subselect.append(SPACE);

  subselect.append(FROM).append(SPACE);

  //

  String rightNameInSource = rightTableInJoin.getNameInSource();

  if (rightNameInSource.endsWith("y")) {

  rightNameInSource = rightNameInSource.substring(0, rightNameInSource.length() - 1);

  subselect.append(rightNameInSource).append("ie").append('s');

  } else {

  subselect.append(rightNameInSource).append('s');

  }

  //

  subselect.append(CLOSE).append(SPACE);

  select.append(subselect);

  select.append(FROM).append(SPACE);

  select.append(leftTableInJoin.getNameInSource()).append(SPACE);

  addCriteriaString(select);

  appendGroupByHaving(select);

  select.append(limitClause);

  return select.toString();

  }

[jchidiac]

Main Query :

select

  "Opportunity"."Amount" as "Opportunity_Amount","Opportunity"."StageName" as "Opportunity_StageName",

  "User_"."Country" as "User__Country","User_"."ManagerId" as "User__ManagerId","User_1"."CommunityNickname" as "User__CommunityNickname",

  "User_"."Name" as "User__Name","Account"."Name" as "Account_Name","Account"."BillingCountry" as "Account_BillingCountry",

  "Account"."ShippingCountry" as "Account_ShippingCountry"

from 

  "SalesForceInstanceModel".Opportunity "Opportunity"

LEFT OUTER JOIN "SalesForceInstanceModel".User_ "User_" ON "Opportunity"."OwnerId" = "User_"."Id"

LEFT OUTER JOIN "SalesForceInstanceModel".User_ "User_1" ON "User_"."ManagerId" = "User_1"."Id"

LEFT OUTER JOIN "SalesForceInstanceModel".Account "Account" ON "Opportunity"."AccountId" = "Account"."Id"

LIMIT 0 , 30

In QueryExecutionImpl , Function Execute(), the query :

SELECT User.ManagerId, Opportunity.AccountId, Opportunity.Amount, Opportunity.StageName, User.Country, User.Name

FROM Opportunity

LEFT OUTER JOIN User ON Opportunity.OwnerId = User.Id

LIMIT 30

the finalQuery variable :

   SELECT Opportunity.AccountId, Opportunity.Amount, Opportunity.StageName,

  (SELECT User.ManagerId, User.Country, User.Name FROM Users)

  FROM Opportunity LIMIT 30

Error :

javax.resource.ResourceException: com.sforce.soap.partner.InvalidSObjectFault: INVALID_TYPE:

User.Country, User.Name FROM Users) FROM Opportunity LIMIT 30

                              ^

ERROR at Row:1:Column:127Didn't understand relationship 'Users' in FROM part of query call. If you are attempting to use a custom relationship,

be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

[shawkins]

> the correct function is : "Modif is marked as Bold"

That is the idea, but the actual fix will be to use the plural label metadata so that we don't have to hardcode the pluralization rules.

On the next issue, I see the source sql as:

SELECT Owner.ManagerId, Opportunity.AccountId, Opportunity.Amount, Opportunity.StageName, Owner.Country, Owner.Name FROM Opportunity LIMIT 30

When the correction from Teiid 8.8 Version : Issues faced with executing query on a Salesforce model in Teiid Embedded has been made, so that is the same issue.

[shawkins]

Logged as [TEIID-3112] Incorrect plural name with parent to child join - JBoss Issue Tracker

[jchidiac]

with the latest modif i have the following problem ::

QueryExecutionImpl.java

function execute() ;

query      :: SELECT CaseSolution.SolutionId, Case.Origin FROM Case LEFT OUTER JOIN CaseSolution ON Case.Id = CaseSolution.CaseId

finalQuery :: SELECT Case.Origin, (SELECT CaseSolution.SolutionId FROM Case Solution) FROM Case

Exception ::

javax.resource.ResourceException: com.sforce.soap.partner.InvalidSObjectFault: INVALID_TYPE:

(SELECT CaseSolution.SolutionId FROM Case Solution) FROM Case

                                                                  ^

ERROR at Row:1:Column:58

Didn't understand relationship 'Case' in FROM part of query call. If you are attempting to use a custom relationship,

be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

[shawkins]

That is due to an invalid assumption on my part.  I had seen that the relationship names were null in many cases, but we were still adding foreign keys for them, so I assumed the plural name was coming from the object plural label.  It does need to come from the relationship name and we need to not add a foreign key when the relationship name is null.

[jchidiac]

Any Prevision for this correction ?

[shawkins]

Yes the change is already in master / 8.9 Beta1.

[jchidiac]

ok thx,it works.