13 Replies Latest reply on Sep 9, 2014 10:37 AM by jchidiac

    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.

        • 1. Re: Sales Force with TeiidEmbedded
          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

          • 2. Re: Sales Force with TeiidEmbedded
            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.

            • 3. Re: Sales Force with TeiidEmbedded
              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?

              • 4. Re: Sales Force with TeiidEmbedded
                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 ?

                • 5. Re: Sales Force with TeiidEmbedded
                  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();

                    }

                  • 6. Re: Sales Force with TeiidEmbedded
                    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.

                    • 7. Re: Sales Force with TeiidEmbedded
                      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.

                      • 8. Re: Sales Force with TeiidEmbedded
                        shawkins
                        • 9. Re: Sales Force with TeiidEmbedded
                          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.

                          • 10. Re: Sales Force with TeiidEmbedded
                            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.

                            • 11. Re: Sales Force with TeiidEmbedded
                              jchidiac

                              Any Prevision for this correction ?

                              • 12. Re: Sales Force with TeiidEmbedded
                                shawkins

                                Yes the change is already in master / 8.9 Beta1.

                                • 13. Re: Sales Force with TeiidEmbedded
                                  jchidiac

                                  ok thx,it works.