[teiid-issues] [JBoss JIRA] (TEIID-5919) Salesforce: Insert fails in case of tables with salesforce standard objects

[Steven Hawkins (Jira)]

     [ https://issues.redhat.com/browse/TEIID-5919?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins updated TEIID-5919:
----------------------------------
               Component/s: Query Engine
         Original Estimate: 2 hours
        Remaining Estimate: 2 hours
              Story Points: 0.5
                    Sprint: DV Sprint 61
    Workaround Description: 
Add ddl to the vdb to alter the affected columns to be auto_increment:

alter table contacts alter column name string not null auto_increment;
                Workaround: Workaround Exists
                  Priority: Major  (was: Critical)


The importer looks for this case only on key columns currently.

However since the column is marked as non-updatable, we could also look at making this change in the engine as part of the insert validation.

> Salesforce: Insert fails in case of tables with salesforce standard objects
> ---------------------------------------------------------------------------
>
>                 Key: TEIID-5919
>                 URL: https://issues.redhat.com/browse/TEIID-5919
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine, Salesforce Connector
>    Affects Versions: 13.1
>            Reporter: Dmitrii Pogorelov
>            Assignee: Steven Hawkins
>            Priority: Major
>             Fix For: 14.0, 13.0.3, 13.1.1
>
>   Original Estimate: 2 hours
>  Remaining Estimate: 2 hours
>
> An insert fails in the case of a Salesforce table with standard objects. For example, the Contact table (https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_objects_contact.htm) in salesforce contains Name standard object: https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_objects_name.htm. The field can't be null, doesn't support create(), insert() and so on operations but should be filled automatically by salesforce. As a result when running the following query:
> {code:sql}
> INSERT INTO sf34.Contact
> ("LastName", "FirstName", "Salutation", "MailingStreet", "MailingCity", "MailingPostalCode", "MailingCountry")
> VALUES
> ('Hillrichsen', 'Suse', 'Mrs.', 'Frankfurter Allee 29', 'Berlin', '10247','Deutschland') ;;
> {code}
> Teiid generates the following error:
> {code}
> 2020-03-16 16:35:15,105 WARN  [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue50) ij6teT/JNA+c TEIID30020 Processing exception for request ij6teT/JNA+c.18 'TEIID30492 Element sf34.Contact.Name of sf34.Contact is neither nullable nor has a default value. A value must be specified in the insert.'. Originally QueryValidatorException Request.java:343. Enable more detailed logging to see the entire stacktrace.
> {code}
> The problem can be solved by a native call, for example:
> {code:sql}
> SELECT * FROM (call "sf34.native"('create;type=Contact;attributes=LastName,FirstName,Salutation,MailingStreet,MailingCity,MailingPostalCode,MailingCountry','Erich','Honecker','Herr','Magdalenenstr. 99','Berlin','11799','DE')) lpk ;;
> {code}
> but it won't work with dates/timestamps types (no sample request available).



--
This message was sent by Atlassian Jira
(v7.13.8#713008)