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

Insert statement with a date field fails

    Details

    • Type: Bug
    • Status: Resolved (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 9.0.2
    • Fix Version/s: 9.1, 9.0.4
    • Component/s: Salesforce Connector
    • Labels:
      None
    • 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

    • Steps to Reproduce:
      Hide

      Create a custom object in salesforce with a date data type
      Import the custom object using the salesforce connector
      Execute an insert statement against the VDB/model specifying a string (or PARSEDATE()) for the date field

      Show
      Create a custom object in salesforce with a date data type Import the custom object using the salesforce connector Execute an insert statement against the VDB/model specifying a string (or PARSEDATE()) for the date field
    • Affects:
      Compatibility/Configuration

      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>
      }}

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                tuxcode J Price
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: