2:03 p.m.
[
https://issues.jboss.org/browse/TEIID-4402?page=com.atlassian.jira.plugin...
]
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)
3513
days inactive
3513
days old
0 comments
1 participants
participants (1)
-
J Price (JIRA)