[teiid-issues] [JBoss JIRA] (TEIID-4402) Insert statement with a date field fails
J Price (JIRA)
issues at jboss.org
Sun Aug 21 15:03:00 EDT 2016
[ https://issues.jboss.org/browse/TEIID-4402?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
J Price updated TEIID-4402:
---------------------------
Description:
I have a custom object created in Salesforce which has date fields. The model has the fields identified as date correctly. When specifying a date using an insert statement I receive an error back from Salesforce:
_
org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.core.TeiidProcessingException: TEIID30504 Sandbox: Start Date: value not of required type: 2016-03-17_
I have tried forcing it to a date using the parsedate function as well as passing it as is.
I setup a proxy to try and identify the cause and found that the datatype being passed in the underlying SOAP request is xsd:string instead of xsd:date.
Using fiddler I duplicated the underlying SOAP request, but changed the types to xsd:date, and resubmitted the request successfully.
----
As an example I have created a simple custom object in salesforce. The DDL for the imported object is as follows:
{{
CREATE TABLE SimpleDateObjectDemo__c (
Id VARCHAR(18) DEFAULT Generated upon creation NOT NULL,
OwnerId VARCHAR(18),
IsDeleted BOOLEAN,
Name VARCHAR(80),
Start_Date__c DATE
);
}}
The following query produces the same error:
{{
INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c)
VALUES ('2001-01-01')
}}
The above query results in the following log messages:
{{
14:43:28,743 INFO [org.teiid.PLANNER] (Worker3_QueryProcessorQueue19) ffLmdSxeJfyw
============================================================================
USER COMMAND:
INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})
----------------------------------------------------------------------------
OPTIMIZE:
INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})
----------------------------------------------------------------------------
GENERATE CANONICAL:
INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})
CANONICAL PLAN:
Project(groups=[], props={PROJECT_COLS=[Count]})
Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})})
============================================================================
EXECUTING PlaceAccess
AFTER:
Project(groups=[], props={PROJECT_COLS=[Count]})
Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e})
Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})})
============================================================================
EXECUTING RaiseAccess
AFTER:
Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e})
Project(groups=[], props={PROJECT_COLS=[Count]})
Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})})
============================================================================
EXECUTING AssignOutputElements
AFTER:
Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e, OUTPUT_COLS=[Count]})
Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count]})
Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), OUTPUT_COLS=[Count]})
============================================================================
EXECUTING CalculateCost
AFTER:
Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e, OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0})
Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0})
Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0})
============================================================================
EXECUTING PlanSorts
AFTER:
Access(groups=[Sandbox.SimpleDateObjectDemo__c])
Project(groups=[])
Source(groups=[Sandbox.SimpleDateObjectDemo__c])
============================================================================
EXECUTING CollapseSource
AFTER:
Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e, OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0, ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})})
============================================================================
CONVERTING PLAN TREE TO PROCESS TREE
PROCESS PLAN =
AccessNode(0) output=[Count] INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})
============================================================================
----------------------------------------------------------------------------
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
AccessNode(0) output=[Count] INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})
============================================================================
14:43:30,088 WARN [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue20) ffLmdSxeJfyw Connector worker process failed for atomic-request=ffLmdSxeJfyw.0.0.0: org.teiid.translator.TranslatorException: Start Date: value not of required type: 2001-01-01
at org.teiid.translator.salesforce.execution.InsertExecutionImpl.execute(InsertExecutionImpl.java:121)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:403)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:365)
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:211)
at com.sun.proxy.$Proxy57.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
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)
Caused by: javax.resource.ResourceException: Start Date: value not of required type: 2001-01-01
at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.analyzeResult(SalesforceConnectionImpl.java:403)
at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.create(SalesforceConnectionImpl.java:368)
at org.teiid.translator.salesforce.execution.InsertExecutionImpl.execute(InsertExecutionImpl.java:89)
... 19 more
14:43:30,093 WARN [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue21) ffLmdSxeJfyw TEIID30020 Processing exception for request ffLmdSxeJfyw.0 'TEIID30504 Sandbox: Start Date: value not of required type: 2001-01-01'. Originally TeiidProcessingException SalesforceConnectionImpl.java:403. Enable more detailed logging to see the entire stacktrace.
}}
The SOAP request produced for the above query is as follows:
{{
<?xml version="1.0" encoding="UTF-8"?>
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<env:Header>
<CallOptions xmlns="urn:partner.soap.sforce.com">
<client>RedHat/MetaMatrix/</client>
<defaultNamespace xsi:nil="true" />
</CallOptions>
<SessionHeader xmlns="urn:partner.soap.sforce.com">
<sessionId>REMOVED</sessionId>
</SessionHeader>
</env:Header>
<env:Body>
<m:create xmlns:m="urn:partner.soap.sforce.com" xmlns:sobj="urn:sobject.partner.soap.sforce.com">
<m:sObjects>
<sobj:type xsi:type="xsd:string">SimpleDateObjectDemo__c</sobj:type>
<sobj:Start_Date__c xsi:type="xsd:string">2001-01-01</sobj:Start_Date__c>
</m:sObjects>
</m:create>
</env:Body>
</env:Envelope>
}}
The XMI for the above table is as follows:
{{
<relational:BaseTable xmi:uuid="mmuuid:878a18c9-359c-4756-b192-79ed7c91d7af" name="SimpleDateObjectDemo__c" nameInSource="SimpleDateObjectDemo__c">
<columns xmi:uuid="mmuuid:22b29abc-6d51-475e-891c-6a19a4869cee" name="Id" nameInSource="Id" nativeType="id" length="18" nullable="NO_NULLS" defaultValue="Generated upon creation" uniqueKeys="mmuuid/f1d6a7d7-cdff-4bc9-b1df-61096c31aacf">
<type href="http://www.w3.org/2001/XMLSchema#string"/>
</columns>
<columns xmi:uuid="mmuuid:4b64550b-057f-49d5-ac44-6f4275508a0c" name="OwnerId" nameInSource="OwnerId" nativeType="id" length="18">
<type href="http://www.w3.org/2001/XMLSchema#string"/>
</columns>
<columns xmi:uuid="mmuuid:cc2969bd-98fd-4918-b6e2-6c0b98407f8b" name="IsDeleted" nameInSource="IsDeleted" nativeType="boolean">
<type href="http://www.w3.org/2001/XMLSchema#boolean"/>
</columns>
<columns xmi:uuid="mmuuid:0c42849d-7b8c-4bc9-8472-bf976c9946db" name="Name" nameInSource="Name" nativeType="string" length="80">
<type href="http://www.w3.org/2001/XMLSchema#string"/>
</columns>
<columns xmi:uuid="mmuuid:c085614b-49b8-4e30-810b-13f0e8d337c5" name="Start_Date__c" nameInSource="Start_Date__c" nativeType="date">
<type href="http://www.w3.org/2001/XMLSchema#date"/>
</columns>
<primaryKey xmi:uuid="mmuuid:f1d6a7d7-cdff-4bc9-b1df-61096c31aacf" name="Id_PK" columns="mmuuid/22b29abc-6d51-475e-891c-6a19a4869cee"/>
</relational:BaseTable>
}}
was:
I have a custom object created in Salesforce which has date fields. The model has the fields identified as date correctly. When specifying a date using an insert statement I receive an error back from Salesforce:
_
org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.core.TeiidProcessingException: TEIID30504 Sandbox: Start Date: value not of required type: 2016-03-17_
I have tried forcing it to a date using the parsedate function as well as passing it as is.
I setup a proxy to try and identify the cause and found that the datatype being passed in the underlying SOAP request is xsd:string instead of xsd:date.
Using fiddler I duplicated the underlying SOAP request, but changed the types to xsd:date, and resubmitted the request successfully.
----
As an example I have created a simple custom object in salesforce. The DDL for the imported object is as follows:
{{
CREATE TABLE SimpleDateObjectDemo__c (
Id VARCHAR(18) DEFAULT Generated upon creation NOT NULL,
OwnerId VARCHAR(18),
IsDeleted BOOLEAN,
Name VARCHAR(80),
Start_Date__c DATE
);
}}
The following query produces the same error:
{{
INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c)
VALUES ('2001-01-01')
}}
The above query results in the following log messages:
{{
14:43:28,743 INFO [org.teiid.PLANNER] (Worker3_QueryProcessorQueue19) ffLmdSxeJfyw
============================================================================
USER COMMAND:
INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})
----------------------------------------------------------------------------
OPTIMIZE:
INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})
----------------------------------------------------------------------------
GENERATE CANONICAL:
INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})
CANONICAL PLAN:
Project(groups=[], props={PROJECT_COLS=[Count]})
Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})})
============================================================================
EXECUTING PlaceAccess
AFTER:
Project(groups=[], props={PROJECT_COLS=[Count]})
Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e})
Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})})
============================================================================
EXECUTING RaiseAccess
AFTER:
Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e})
Project(groups=[], props={PROJECT_COLS=[Count]})
Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})})
============================================================================
EXECUTING AssignOutputElements
AFTER:
Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e, OUTPUT_COLS=[Count]})
Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count]})
Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), OUTPUT_COLS=[Count]})
============================================================================
EXECUTING CalculateCost
AFTER:
Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e, OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0})
Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0})
Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0})
============================================================================
EXECUTING PlanSorts
AFTER:
Access(groups=[Sandbox.SimpleDateObjectDemo__c])
Project(groups=[])
Source(groups=[Sandbox.SimpleDateObjectDemo__c])
============================================================================
EXECUTING CollapseSource
AFTER:
Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e, OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0, ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})})
============================================================================
CONVERTING PLAN TREE TO PROCESS TREE
PROCESS PLAN =
AccessNode(0) output=[Count] INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})
============================================================================
----------------------------------------------------------------------------
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
AccessNode(0) output=[Count] INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})
============================================================================
14:43:30,088 WARN [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue20) ffLmdSxeJfyw Connector worker process failed for atomic-request=ffLmdSxeJfyw.0.0.0: org.teiid.translator.TranslatorException: Start Date: value not of required type: 2001-01-01
at org.teiid.translator.salesforce.execution.InsertExecutionImpl.execute(InsertExecutionImpl.java:121)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:403)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:365)
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:211)
at com.sun.proxy.$Proxy57.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
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)
Caused by: javax.resource.ResourceException: Start Date: value not of required type: 2001-01-01
at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.analyzeResult(SalesforceConnectionImpl.java:403)
at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.create(SalesforceConnectionImpl.java:368)
at org.teiid.translator.salesforce.execution.InsertExecutionImpl.execute(InsertExecutionImpl.java:89)
... 19 more
14:43:30,093 WARN [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue21) ffLmdSxeJfyw TEIID30020 Processing exception for request ffLmdSxeJfyw.0 'TEIID30504 Sandbox: Start Date: value not of required type: 2001-01-01'. Originally TeiidProcessingException SalesforceConnectionImpl.java:403. Enable more detailed logging to see the entire stacktrace.
}}
The SOAP request produced for the above query is as follows:
{{
<?xml version="1.0" encoding="UTF-8"?>
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<env:Header>
<CallOptions xmlns="urn:partner.soap.sforce.com">
<client>RedHat/MetaMatrix/</client>
<defaultNamespace xsi:nil="true" />
</CallOptions>
<SessionHeader xmlns="urn:partner.soap.sforce.com">
<sessionId>REMOVED</sessionId>
</SessionHeader>
</env:Header>
<env:Body>
<m:create xmlns:m="urn:partner.soap.sforce.com" xmlns:sobj="urn:sobject.partner.soap.sforce.com">
<m:sObjects>
<sobj:type xsi:type="xsd:string">SimpleDateObjectDemo__c</sobj:type>
<sobj:Start_Date__c xsi:type="xsd:string">2001-01-01</sobj:Start_Date__c>
</m:sObjects>
</m:create>
</env:Body>
</env:Envelope>
}}
> Insert statement with a date field fails
> ----------------------------------------
>
> Key: TEIID-4402
> URL: https://issues.jboss.org/browse/TEIID-4402
> Project: Teiid
> Issue Type: Bug
> Components: Salesforce Connector
> Affects Versions: 9.0.2
> Environment: WildFly Full 9.0.2.Final (WildFly Core 1.0.2.Final)
> Teiid 9.0.2
> jdk1.8.0_101
> Salesforce sandbox
> Salesforce connectors / translators: salesforce and salesforce-34
> Reporter: J Price
> Assignee: Steven Hawkins
>
> I have a custom object created in Salesforce which has date fields. The model has the fields identified as date correctly. When specifying a date using an insert statement I receive an error back from Salesforce:
> _
> org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.core.TeiidProcessingException: TEIID30504 Sandbox: Start Date: value not of required type: 2016-03-17_
> I have tried forcing it to a date using the parsedate function as well as passing it as is.
> I setup a proxy to try and identify the cause and found that the datatype being passed in the underlying SOAP request is xsd:string instead of xsd:date.
> Using fiddler I duplicated the underlying SOAP request, but changed the types to xsd:date, and resubmitted the request successfully.
> ----
> As an example I have created a simple custom object in salesforce. The DDL for the imported object is as follows:
> {{
> CREATE TABLE SimpleDateObjectDemo__c (
> Id VARCHAR(18) DEFAULT Generated upon creation NOT NULL,
> OwnerId VARCHAR(18),
> IsDeleted BOOLEAN,
> Name VARCHAR(80),
> Start_Date__c DATE
> );
> }}
> The following query produces the same error:
> {{
> INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c)
> VALUES ('2001-01-01')
> }}
> The above query results in the following log messages:
> {{
> 14:43:28,743 INFO [org.teiid.PLANNER] (Worker3_QueryProcessorQueue19) ffLmdSxeJfyw
> ============================================================================
> USER COMMAND:
> INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})
> ----------------------------------------------------------------------------
> OPTIMIZE:
> INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})
> ----------------------------------------------------------------------------
> GENERATE CANONICAL:
> INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})
> CANONICAL PLAN:
> Project(groups=[], props={PROJECT_COLS=[Count]})
> Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})})
> ============================================================================
> EXECUTING PlaceAccess
> AFTER:
> Project(groups=[], props={PROJECT_COLS=[Count]})
> Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e})
> Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})})
> ============================================================================
> EXECUTING RaiseAccess
> AFTER:
> Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e})
> Project(groups=[], props={PROJECT_COLS=[Count]})
> Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})})
> ============================================================================
> EXECUTING AssignOutputElements
> AFTER:
> Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e, OUTPUT_COLS=[Count]})
> Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count]})
> Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), OUTPUT_COLS=[Count]})
> ============================================================================
> EXECUTING CalculateCost
> AFTER:
> Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e, OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0})
> Project(groups=[], props={PROJECT_COLS=[Count], OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0})
> Source(groups=[Sandbox.SimpleDateObjectDemo__c], props={ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), VIRTUAL_COMMAND=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'}), OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0})
> ============================================================================
> EXECUTING PlanSorts
> AFTER:
> Access(groups=[Sandbox.SimpleDateObjectDemo__c])
> Project(groups=[])
> Source(groups=[Sandbox.SimpleDateObjectDemo__c])
> ============================================================================
> EXECUTING CollapseSource
> AFTER:
> Access(groups=[Sandbox.SimpleDateObjectDemo__c], props={SOURCE_HINT=null, MODEL_ID=Schema name=Sandbox, nameInSource=null, uuid=mmuuid:e1fb1f99-dc05-42fc-9e59-1ff9c172756e, OUTPUT_COLS=[Count], EST_CARDINALITY=-1.0, ATOMIC_REQUEST=INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})})
> ============================================================================
> CONVERTING PLAN TREE TO PROCESS TREE
> PROCESS PLAN =
> AccessNode(0) output=[Count] INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})
> ============================================================================
> ----------------------------------------------------------------------------
> OPTIMIZATION COMPLETE:
> PROCESSOR PLAN:
> AccessNode(0) output=[Count] INSERT INTO Sandbox.SimpleDateObjectDemo__c (Start_Date__c) VALUES ({d'2001-01-01'})
> ============================================================================
> 14:43:30,088 WARN [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue20) ffLmdSxeJfyw Connector worker process failed for atomic-request=ffLmdSxeJfyw.0.0.0: org.teiid.translator.TranslatorException: Start Date: value not of required type: 2001-01-01
> at org.teiid.translator.salesforce.execution.InsertExecutionImpl.execute(InsertExecutionImpl.java:121)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:403)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:365)
> 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:211)
> at com.sun.proxy.$Proxy57.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> 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)
> Caused by: javax.resource.ResourceException: Start Date: value not of required type: 2001-01-01
> at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.analyzeResult(SalesforceConnectionImpl.java:403)
> at org.teiid.resource.adapter.salesforce.SalesforceConnectionImpl.create(SalesforceConnectionImpl.java:368)
> at org.teiid.translator.salesforce.execution.InsertExecutionImpl.execute(InsertExecutionImpl.java:89)
> ... 19 more
> 14:43:30,093 WARN [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue21) ffLmdSxeJfyw TEIID30020 Processing exception for request ffLmdSxeJfyw.0 'TEIID30504 Sandbox: Start Date: value not of required type: 2001-01-01'. Originally TeiidProcessingException SalesforceConnectionImpl.java:403. Enable more detailed logging to see the entire stacktrace.
> }}
> The SOAP request produced for the above query is as follows:
> {{
> <?xml version="1.0" encoding="UTF-8"?>
> <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <env:Header>
> <CallOptions xmlns="urn:partner.soap.sforce.com">
> <client>RedHat/MetaMatrix/</client>
> <defaultNamespace xsi:nil="true" />
> </CallOptions>
> <SessionHeader xmlns="urn:partner.soap.sforce.com">
> <sessionId>REMOVED</sessionId>
> </SessionHeader>
> </env:Header>
> <env:Body>
> <m:create xmlns:m="urn:partner.soap.sforce.com" xmlns:sobj="urn:sobject.partner.soap.sforce.com">
> <m:sObjects>
> <sobj:type xsi:type="xsd:string">SimpleDateObjectDemo__c</sobj:type>
> <sobj:Start_Date__c xsi:type="xsd:string">2001-01-01</sobj:Start_Date__c>
> </m:sObjects>
> </m:create>
> </env:Body>
> </env:Envelope>
> }}
> The XMI for the above table is as follows:
> {{
> <relational:BaseTable xmi:uuid="mmuuid:878a18c9-359c-4756-b192-79ed7c91d7af" name="SimpleDateObjectDemo__c" nameInSource="SimpleDateObjectDemo__c">
> <columns xmi:uuid="mmuuid:22b29abc-6d51-475e-891c-6a19a4869cee" name="Id" nameInSource="Id" nativeType="id" length="18" nullable="NO_NULLS" defaultValue="Generated upon creation" uniqueKeys="mmuuid/f1d6a7d7-cdff-4bc9-b1df-61096c31aacf">
> <type href="http://www.w3.org/2001/XMLSchema#string"/>
> </columns>
> <columns xmi:uuid="mmuuid:4b64550b-057f-49d5-ac44-6f4275508a0c" name="OwnerId" nameInSource="OwnerId" nativeType="id" length="18">
> <type href="http://www.w3.org/2001/XMLSchema#string"/>
> </columns>
> <columns xmi:uuid="mmuuid:cc2969bd-98fd-4918-b6e2-6c0b98407f8b" name="IsDeleted" nameInSource="IsDeleted" nativeType="boolean">
> <type href="http://www.w3.org/2001/XMLSchema#boolean"/>
> </columns>
> <columns xmi:uuid="mmuuid:0c42849d-7b8c-4bc9-8472-bf976c9946db" name="Name" nameInSource="Name" nativeType="string" length="80">
> <type href="http://www.w3.org/2001/XMLSchema#string"/>
> </columns>
> <columns xmi:uuid="mmuuid:c085614b-49b8-4e30-810b-13f0e8d337c5" name="Start_Date__c" nameInSource="Start_Date__c" nativeType="date">
> <type href="http://www.w3.org/2001/XMLSchema#date"/>
> </columns>
> <primaryKey xmi:uuid="mmuuid:f1d6a7d7-cdff-4bc9-b1df-61096c31aacf" name="Id_PK" columns="mmuuid/22b29abc-6d51-475e-891c-6a19a4869cee"/>
> </relational:BaseTable>
> }}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
More information about the teiid-issues
mailing list