[teiid-issues] [JBoss JIRA] (TEIID-5923) Salesforce: UPDATE fails or does nothing for BOOLEAN and DATE fields
Dmitrii Pogorelov (Jira)
issues at jboss.org
Thu Mar 26 12:06:16 EDT 2020
[ https://issues.redhat.com/browse/TEIID-5923?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dmitrii Pogorelov updated TEIID-5923:
-------------------------------------
Steps to Reproduce:
1. add salesforce resource adapter in standalone-teiid.xml:
{code: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>
{code}
2. add sf34 model:
{code:xml}
<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>
{code}
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:
{code:sql}
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') ;;
{code}
5. run the following queries:
{code:sql}
--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 '2002-11-11'}
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 '2002-11-11'}
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' ;;
{code}
was:
1. add salesforce resource adapter in standalone-teiid.xml:
{code: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>
{code}
2. add sf34 model:
{code:xml}
<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>
{code}
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:
{code:sql}
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') ;;
{code}
5. run the following queries:
{code:sql}
--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 '2002-11-11'}
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 '2002-11-11'}
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' ;;
{code}
> Salesforce: UPDATE fails or does nothing for BOOLEAN and DATE fields
> --------------------------------------------------------------------
>
> Key: TEIID-5923
> URL: https://issues.redhat.com/browse/TEIID-5923
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine, Salesforce Connector
> Affects Versions: 13.1
> Reporter: Dmitrii Pogorelov
> Assignee: Steven Hawkins
> Priority: Critical
>
> 1. Running the following queries:
> {code:sql}
> --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 ;;
> {code}
> will lead to the following error:
> {code}
> 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)
> {code}
> {code:sql}
> --1.3 DEFECT(value has not changed)
> select OwnerId, bo__c, str__c from sf34.test_type__c where str__c = 'test_updates' ;;
> {code}
> 2. Running the following queries:
> {code:sql}
> --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 ;;
> {code}
> will lead to the following error:
> {code}
> 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)
> {code}
> {code:sql}
> --2.2 DEFECT(value has not changed)
> select OwnerId, bo__c, str__c from sf34.test_type__c where str__c = 'test_updates' ;;
> {code}
> 3. Running the following queries:
> {code:sql}
> --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 '2002-11-11'}
> 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 '2002-11-11'}
> update sf34.Contact set Birthdate = cast('2002-11-11' as date) where LastName = 'test_updates' ;;
> {code}
> will lead to the following error:
> {code}
> 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 '2002-11-11'} 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)
> {code}
> {code:sql}
> --3.3 DEFECT(value has not changed)
> select OwnerId, LastName, Birthdate from sf34.Contact where LastName = 'test_updates' ;;
> {code}
> 4. Running the following queries:
> {code:sql}
> --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 ;;
> {code}
> will lead to the following error:
> {code}
> 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)
> {code}
> {code:sql}
> --4.3 DEFECT(value has not changed)
> select OwnerId, LastName, Birthdate from sf34.Contact where LastName = 'test_updates' ;;
> {code}
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
More information about the teiid-issues
mailing list