[teiid-issues] [JBoss JIRA] (TEIID-5923) Salesforce: UPDATE fails for BOOLEAN and DATE fields
Steven Hawkins (Jira)
issues at jboss.org
Thu Mar 26 14:46:30 EDT 2020
[ https://issues.redhat.com/browse/TEIID-5923?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14010448#comment-14010448 ]
Steven Hawkins edited comment on TEIID-5923 at 3/26/20 2:45 PM:
----------------------------------------------------------------
Updated the title and added a link to a similar older issue - there are only exceptions thrown from these updates, it is not expected that they will have the intended side effects.
The problem before is that single insert was updated but direct and update was not - they all should be using object instead of string values. From what I recall bulk insert however needs to stay string values. Additionally there was no handling previously added for time types. As an object value the salesforce logic automatically handles java.sql.Dates based upon the super class java.util.Date mapping.
was (Author: shawkins):
Updated the title and added a link to a similar older issue. The problem is that single insert was updated but direct and update was not - they all should be using object instead of string values. From what I recall bulk insert however needs to stay string values. Additionally there was no handling previously added for time types. As an object value the salesforce logic automatically handles java.sql.Dates based upon the super class java.util.Date mapping.
> Salesforce: UPDATE fails for BOOLEAN and DATE fields
> ----------------------------------------------------
>
> Key: TEIID-5923
> URL: https://issues.redhat.com/browse/TEIID-5923
> Project: Teiid
> Issue Type: Bug
> Components: Salesforce Connector
> Affects Versions: 13.1
> Reporter: Dmitrii Pogorelov
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 14.0, 13.1.1
>
>
> 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