Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5923

Salesforce: UPDATE fails for BOOLEAN and DATE fields

    XMLWordPrintable

Details

    • DV Sprint 61
    • Hide

      1. add salesforce resource adapter in standalone-teiid.xml:

                      <resource-adapter id="salesforce-34">
                          <module slot="main" id="org.jboss.teiid.resource-adapter.salesforce-34"/>
                          <connection-definitions>
                              <connection-definition class-name="org.teiid.resource.adapter.salesforce.SalesForceManagedConnectionFactory" jndi-name="java:/salesforce34" enabled="true" use-java-context="true" pool-name="sfDS34">
                                 <config-property name="URL">
                                    https://login.salesforce.com/services/Soap/u/34.0
                                 </config-property>
                                 <config-property name="username">
                                    xxxxx
                                 </config-property>
                                 <config-property name="password">
                                    xxxxx
                                 </config-property>
                              </connection-definition>
                          </connection-definitions>
                      </resource-adapter>
      

      2. add sf34 model:

          <model visible="true" name="sf34">
              <source name="sf34" translator-name="mySalesforce-34" connection-jndi-name="java:/salesforce34"/>    
          </model>
          <translator name="mySalesforce-34" type="salesforce-34">
              <property name="SupportsNativeQueries" value="true"/>
          </translator>
      

      3. create

      test_type__c

      table in salesforce, unfortunately I don't have a script for creating the table as it was created via Salesforce web interface but it has three fields (standard OwnerId, boolean bo_c and string str_c fields).

      4. in Teiid create the test data:

      insert into sf34.test_type__c (OwnerId, bo__c, str__c) values ('005i0000002dtHTAAY', true, 'test_updates') ;;
      insert into sf34.Contact (OwnerId, LastName, Birthdate) values ('005i0000002dtHTAAY', 'test_updates', '2020-03-22') ;;
      

      5. run the following queries:

      --1.1 Update some BOOLEAN field and check if it's value changed (it should).
      --Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: bo: value not of required type: FALSE
      update sf34.test_type__c set bo__c = false where str__c = 'test_updates' ;;
       
      --1.2 Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: bo: value not of required type: FALSE
      update sf34.test_type__c set bo__c = false ;;
       
      --1.3 DEFECT(value has not changed)
      select OwnerId, bo__c, str__c from sf34.test_type__c where str__c = 'test_updates' ;;
      
      --2.1 Insert some value into BOOLEAN field and check if it's there (it should be).
      --Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: bo: value not of required type: true
      select w.* from (call sf34.native('create;type=test_type__c;attributes=OwnerId, bo__c, str__c', '005i0000002dtHTAAY', true, 'test_updates')) as w ;;
       
      --2.2 DEFECT(value has not changed)
      select OwnerId, bo__c, str__c from sf34.test_type__c where str__c = 'test_updates' ;;
      
      --3.1 Update some DATE field and check if it's value changed (it should).
      --Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: Birthdate: value not of required type: {d &#39;2002-11-11&#39;}
      update sf34.Contact set Birthdate = '2002-11-11' where LastName = 'test_updates' ;;
       
      --3.2 Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: Birthdate: value not of required type: {d &#39;2002-11-11&#39;}
      update sf34.Contact set Birthdate = cast('2002-11-11' as date) where LastName = 'test_updates' ;;
       
      --3.3 DEFECT(value has not changed)
      select OwnerId, LastName, Birthdate from sf34.Contact where LastName = 'test_updates' ;;
      
      --4.1 Insert some value into DATE field and check if it's there (it should be).
      --Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: Birthdate: value not of required type: 2020-03-22
      select w.* from (call sf34.native('create;type=Contact;attributes=OwnerId, LastName, Birthdate', '005i0000002dtHTAAY', 'test_updates', '2020-03-22')) as w ;;
       
      --4.2 Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: Birthdate: value not of required type: 2020-03-22
      select w.* from (call sf34.native('create;type=Contact;attributes=OwnerId, LastName, Birthdate', '005i0000002dtHTAAY', 'test_updates', cast('2020-03-22' as date)) ) as w ;;
       
      --4.3 DEFECT(value has not changed)
      select OwnerId, LastName, Birthdate from sf34.Contact where LastName = 'test_updates' ;;
      
      Show
      1. add salesforce resource adapter in standalone-teiid.xml: <resource-adapter id= "salesforce-34" > <module slot= "main" id= "org.jboss.teiid.resource-adapter.salesforce-34" /> <connection-definitions> <connection-definition class-name= "org.teiid.resource.adapter.salesforce.SalesForceManagedConnectionFactory" jndi-name= "java:/salesforce34" enabled= "true" use-java-context= "true" pool-name= "sfDS34" > <config-property name= "URL" > https://login.salesforce.com/services/Soap/u/34.0 </config-property> <config-property name= "username" > xxxxx </config-property> <config-property name= "password" > xxxxx </config-property> </connection-definition> </connection-definitions> </resource-adapter> 2. add sf34 model: <model visible= "true" name= "sf34" > <source name= "sf34" translator-name= "mySalesforce-34" connection-jndi-name= "java:/salesforce34" /> </model> <translator name= "mySalesforce-34" type= "salesforce-34" > <property name= "SupportsNativeQueries" value= "true" /> </translator> 3. create test_type__c table in salesforce, unfortunately I don't have a script for creating the table as it was created via Salesforce web interface but it has three fields (standard OwnerId, boolean bo_ c and string str _c fields). 4. in Teiid create the test data: insert into sf34.test_type__c (OwnerId, bo__c, str__c) values ( '005i0000002dtHTAAY' , true , 'test_updates' ) ;; insert into sf34.Contact (OwnerId, LastName, Birthdate) values ( '005i0000002dtHTAAY' , 'test_updates' , '2020-03-22' ) ;; 5. run the following queries: --1.1 Update some BOOLEAN field and check if it's value changed (it should). --Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: bo: value not of required type : FALSE update sf34.test_type__c set bo__c = false where str__c = 'test_updates' ;; --1.2 Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: bo: value not of required type : FALSE update sf34.test_type__c set bo__c = false ;; --1.3 DEFECT( value has not changed) select OwnerId, bo__c, str__c from sf34.test_type__c where str__c = 'test_updates' ;; --2.1 Insert some value into BOOLEAN field and check if it's there (it should be). --Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: bo: value not of required type : true select w.* from ( call sf34.native( ' create ; type =test_type__c; attributes =OwnerId, bo__c, str__c' , '005i0000002dtHTAAY' , true , 'test_updates' )) as w ;; --2.2 DEFECT( value has not changed) select OwnerId, bo__c, str__c from sf34.test_type__c where str__c = 'test_updates' ;; --3.1 Update some DATE field and check if it's value changed (it should). --Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: Birthdate: value not of required type : {d &#39;2002-11-11&#39;} update sf34.Contact set Birthdate = '2002-11-11' where LastName = 'test_updates' ;; --3.2 Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: Birthdate: value not of required type : {d &#39;2002-11-11&#39;} update sf34.Contact set Birthdate = cast ( '2002-11-11' as date ) where LastName = 'test_updates' ;; --3.3 DEFECT( value has not changed) select OwnerId, LastName, Birthdate from sf34.Contact where LastName = 'test_updates' ;; --4.1 Insert some value into DATE field and check if it's there (it should be). --Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: Birthdate: value not of required type : 2020-03-22 select w.* from ( call sf34.native( ' create ; type =Contact; attributes =OwnerId, LastName, Birthdate' , '005i0000002dtHTAAY' , 'test_updates' , '2020-03-22' )) as w ;; --4.2 Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: Birthdate: value not of required type : 2020-03-22 select w.* from ( call sf34.native( ' create ; type =Contact; attributes =OwnerId, LastName, Birthdate' , '005i0000002dtHTAAY' , 'test_updates' , cast ( '2020-03-22' as date )) ) as w ;; --4.3 DEFECT( value has not changed) select OwnerId, LastName, Birthdate from sf34.Contact where LastName = 'test_updates' ;;

    Description

      1. Running the following queries:

      --1.1 Update some BOOLEAN field and check if it's value changed (it should).
      --Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: bo: value not of required type: FALSE
      update sf34.test_type__c set bo__c = false where str__c = 'test_updates' ;;
       
      --1.2 Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: bo: value not of required type: FALSE
      update sf34.test_type__c set bo__c = false ;;
      

      will lead to the following error:

      2020-03-26 16:49:42,926 WARN  [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue15) 1yWWIDJQwoJo Connector worker process failed for atomic-request=1yWWIDJQwoJo.3.0.3: org.teiid.translator.TranslatorException: bo: value not of required type: FALSE                                                                                                                             at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.analyzeResult(SalesforceConnectionImpl.java:431)                                                                    at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.update(SalesforceConnectionImpl.java:425)                                                                           at org.teiid.translator.salesforce.execution.UpdateExecutionImpl.processIds(UpdateExecutionImpl.java:74)                                                                              at org.teiid.translator.salesforce.execution.AbstractUpdateExecution.execute(AbstractUpdateExecution.java:124)                                                                        at org.teiid.translator.salesforce.execution.UpdateExecutionImpl.execute(UpdateExecutionImpl.java:51)                                                                                 at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:443)                                                                                             at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:402)                                                                                               at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)                                                                                                                        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)                                                                                                      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)                                                                                              at java.lang.reflect.Method.invoke(Method.java:498)                                                                                                                                   at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:228)                                                                                                at com.sun.proxy.$Proxy44.execute(Unknown Source)                                                                                                                                     at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)                                                                                        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)                                                                                            at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)                                                                                            at java.util.concurrent.FutureTask.run(FutureTask.java:266)                                                                                                                           at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:59)                                                                                                                  at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281)                                                                                                at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:124)                                                                               at org.teiid.dqp.internal.process.ThreadReuseExecutor$2.run(ThreadReuseExecutor.java:212)                                                                                             at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)                                                                                                    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)                                                                                                    at java.lang.Thread.run(Thread.java:745)
      
      --1.3 DEFECT(value has not changed)
      select OwnerId, bo__c, str__c from sf34.test_type__c where str__c = 'test_updates' ;;
      

      2. Running the following queries:

      --2.1 Insert some value into BOOLEAN field and check if it's there (it should be).
      --Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: bo: value not of required type: true
      select w.* from (call sf34.native('create;type=test_type__c;attributes=OwnerId, bo__c, str__c', '005i0000002dtHTAAY', true, 'test_updates')) as w ;;
      

      will lead to the following error:

      2020-03-26 16:53:31,525 WARN  [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue29) 1yWWIDJQwoJo Connector worker process failed for atomic-request=1yWWIDJQwoJo.7.3.7: org.teiid.translator.TranslatorException: bo: value not of required type: true                                                                                                                              at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.analyzeResult(SalesforceConnectionImpl.java:431)                                                                    at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.create(SalesforceConnectionImpl.java:396)                                                                           at org.teiid.translator.salesforce.execution.DirectQueryExecution.doInsert(DirectQueryExecution.java:133)                                                                             at org.teiid.translator.salesforce.execution.DirectQueryExecution.execute(DirectQueryExecution.java:97)                                                                               at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:402)                                                                                               at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)                                                                                                                        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)                                                                                                      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)                                                                                              at java.lang.reflect.Method.invoke(Method.java:498)                                                                                                                                   at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:228)                                                                                                at com.sun.proxy.$Proxy44.execute(Unknown Source)                                                                                                                                     at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)                                                                                        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)                                                                                            at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)                                                                                            at java.util.concurrent.FutureTask.run(FutureTask.java:266)                                                                                                                           at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:59)                                                                                                                  at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281)                                                                                                at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:124)                                                                               at org.teiid.dqp.internal.process.ThreadReuseExecutor$2.run(ThreadReuseExecutor.java:212)                                                                                             at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)                                                                                                    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)                                                                                                    at java.lang.Thread.run(Thread.java:745)
      
      --2.2 DEFECT(value has not changed)
      select OwnerId, bo__c, str__c from sf34.test_type__c where str__c = 'test_updates' ;;
      

      3. Running the following queries:

      --3.1 Update some DATE field and check if it's value changed (it should).
      --Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: Birthdate: value not of required type: {d &#39;2002-11-11&#39;}
      update sf34.Contact set Birthdate = '2002-11-11' where LastName = 'test_updates' ;;
       
      --3.2 Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: Birthdate: value not of required type: {d &#39;2002-11-11&#39;}
      update sf34.Contact set Birthdate = cast('2002-11-11' as date) where LastName = 'test_updates' ;;
      

      will lead to the following error:

      2020-03-26 16:56:03,868 WARN  [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue42) 1yWWIDJQwoJo Connector worker process failed for atomic-request=1yWWIDJQwoJo.11.0.11: org.teiid.translator.TranslatorException: Birthdate: value not of required type: {d &#39;2002-11-11&#39;}                                                                                                 at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.analyzeResult(SalesforceConnectionImpl.java:431)                                                                    at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.update(SalesforceConnectionImpl.java:425)                                                                           at org.teiid.translator.salesforce.execution.UpdateExecutionImpl.processIds(UpdateExecutionImpl.java:74)                                                                              at org.teiid.translator.salesforce.execution.AbstractUpdateExecution.execute(AbstractUpdateExecution.java:124)                                                                        at org.teiid.translator.salesforce.execution.UpdateExecutionImpl.execute(UpdateExecutionImpl.java:51)                                                                                 at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:443)                                                                                             at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:402)                                                                                               at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)                                                                                                                        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)                                                                                                      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)                                                                                              at java.lang.reflect.Method.invoke(Method.java:498)                                                                                                                                   at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:228)                                                                                                at com.sun.proxy.$Proxy44.execute(Unknown Source)                                                                                                                                     at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)                                                                                        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)                                                                                            at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)                                                                                            at java.util.concurrent.FutureTask.run(FutureTask.java:266)                                                                                                                           at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:59)                                                                                                                  at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281)                                                                                                at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:124)                                                                               at org.teiid.dqp.internal.process.ThreadReuseExecutor$2.run(ThreadReuseExecutor.java:212)                                                                                             at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)                                                                                                    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)                                                                                                    at java.lang.Thread.run(Thread.java:745)
      
      --3.3 DEFECT(value has not changed)
      select OwnerId, LastName, Birthdate from sf34.Contact where LastName = 'test_updates' ;;
      

      4. Running the following queries:

      --4.1 Insert some value into DATE field and check if it's there (it should be).
      --Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: Birthdate: value not of required type: 2020-03-22
      select w.* from (call sf34.native('create;type=Contact;attributes=OwnerId, LastName, Birthdate', '005i0000002dtHTAAY', 'test_updates', '2020-03-22')) as w ;;
       
      --4.2 Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 sf34: Birthdate: value not of required type: 2020-03-22
      select w.* from (call sf34.native('create;type=Contact;attributes=OwnerId, LastName, Birthdate', '005i0000002dtHTAAY', 'test_updates', cast('2020-03-22' as date)) ) as w ;;
      

      will lead to the following error:

      2020-03-26 16:57:49,333 WARN  [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue51) 1yWWIDJQwoJo Connector worker process failed for atomic-request=1yWWIDJQwoJo.14.3.14: org.teiid.translator.TranslatorException: Birthdate: value not of required type: 2020-03-22                                                                                                               at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.analyzeResult(SalesforceConnectionImpl.java:431)                                                                    at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.create(SalesforceConnectionImpl.java:396)                                                                           at org.teiid.translator.salesforce.execution.DirectQueryExecution.doInsert(DirectQueryExecution.java:133)                                                                             at org.teiid.translator.salesforce.execution.DirectQueryExecution.execute(DirectQueryExecution.java:97)                                                                               at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:402)                                                                                               at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)                                                                                                                        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)                                                                                                      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)                                                                                              at java.lang.reflect.Method.invoke(Method.java:498)                                                                                                                                   at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:228)                                                                                                at com.sun.proxy.$Proxy44.execute(Unknown Source)                                                                                                                                     at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)                                                                                        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)                                                                                            at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)                                                                                            at java.util.concurrent.FutureTask.run(FutureTask.java:266)                                                                                                                           at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:59)                                                                                                                  at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281)                                                                                                at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:124)                                                                               at org.teiid.dqp.internal.process.ThreadReuseExecutor$2.run(ThreadReuseExecutor.java:212)                                                                                             at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)                                                                                                    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)                                                                                                    at java.lang.Thread.run(Thread.java:745)
      
      --4.3 DEFECT(value has not changed)
      select OwnerId, LastName, Birthdate from sf34.Contact where LastName = 'test_updates' ;;
      

      Attachments

        Issue Links

          Activity

            People

              rhn-engineering-shawkins Steven Hawkins
              dalex005 Dmitrii Pogorelov
              Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: