Using Progress and Salesforce Lightning Connect getting an error trying to update record in Oracle Database - Forum - DataDirect Cloud - Progress Community

Using Progress and Salesforce Lightning Connect getting an error trying to update record in Oracle Database

 Forum

Using Progress and Salesforce Lightning Connect getting an error trying to update record in Oracle Database

  • We are working on a Proof of Concept using Salesforce Lightning Connect along with Progress DataDirect software to connect Salesforce to our Oracle E-business Suite ERP system. We are facing an issue updating or deleting records from the oracle database when the primary key is a numeric field. Error message received is "Type mismatch while updating the external column ExternalId"

     

    Anybody else faced this issue? This works fine if the oracle table's primary key is a character field. One thing I have noticed is that if the oracle primary key value is 123, the value for the salesforce external id in the external object is displayed as 123.0

  • I tried reproducing this issue, but did not have any success.  Do you know how the column for the primary key column was defined?  For example was it defined as a NUMBER without any precision or scale?
  • Hello,

    I have also had no success reproducing the error myself, but I have been able to reproduce the "123.0" behavior in Salesforce - instead of the "123" ExternalID.

    -When the Primary Key is defined as "number", without any size associated to it, it will display in Salesforce as "1.0" but will display as "1" in DataDirect Cloud SQL Testing.

    -When the Primary Key is deinfed as "number(X)" where X is an integer such as 25, it will display in Salesforce as "1".

    With both of these tests I was still able to update and delete records regardless of the Primary Key definition as number or number(25).

    Best,

    Idaliz Baez

  • When an Oracle column is defined as NUMBER with no precision or scale, our service describes this column as DOUBLE.  This would explain why you see the trailing “.0” in Salesforce WebUI.  This mapping to a DOUBLE is to avoid Lightning Connect issues with their maximum number size.
  • Thank you for the explanation!

  • That explains the issue however we are using an Oracle ERP environment and we do not have the option to re-define some of the tables defined by oracle.

  • I think we have only explained why in some cases you may see a “.0” on numeric values in the Salesforce WebUI.  I am inquiring about the data type to understand and possibly reproduce the issue ourselves.  We are not suggesting that you should need to change the data type in your table.  Ideally, we would reproduce the issue, and then either make fix in our service or possibly reach out to Salesforce to have them make a change in their service.
  • Hello,

    I would like to follow-up and clarify the repro for this issue.

    Step 1. Create an Oracle Table with a Number primary key that does not have precision

    create table employee(

      employee_id    number,

      first_name    varchar2(50),

    last_name      varchar2(50),

      constraint pk_employee primary key (employee_id))

    insert into employee

    values (1, 'Idaliz', 'Baez')

    Step 2. Connect to this table with DataDirect Cloud and execute a 'Select * from employee' --- this will result with a display that looks like:

    EMPLOYEE_ID      FIRST_NAME      LAST_NAME

    1                                Idaliz                     Baez

    Step 3. Expose this table via OData in DataDirect Cloud (copy the OData URI)

    Step 4.  In Salesforce create an External Data Source using Salesforce Connect that utilizes the OData URI and ensure that it allows Create, Update, Edit, Delete

    Step 5. Create a tab with the External Object (the exposed employee table) and view the Oracle employee data within Salesforce. It will be displayed in Salesforce as

    External ID       EMPLOYEE_ID      FIRST_NAME      LAST_NAME

    1.0                      1.0000000                 Idaliz                     Baez

    ^With this repro, I was still able to edit, update or delete the First_Name or Last_Name and did not see the same Type Mismatch error that the prospect received. The prospect did verify that changing the table definition from Number to Number(38) made the above output display without decimals and then they were able to successfully update the fields.

    Best,

    Idaliz Baez