• State Not Answered
  • Locked Locked
  • Replies 8 replies
  • Subscribers 93 subscribers
  • Views 7689 views
  • Users 0 members are here
Options
Related
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Synchronizing Integer Column with NULL

fmoideen
over 8 years ago

I'm trying to push identity data from D1IM.Person to an Oracle database table using the Synchronization Editor in D1IM 7.0.1. The Oracle table has an integer column (EOG_User_ID) that has a unique constraint while allowing NULLs. We do not store the value for, neither do we need to push a value for EOG_User_ID from D1IM. Hence, EOG_User_ID is left out of the mapping. 

The issue is that when the sync runs, Dell sends the value for EOG_User_ID as the integer 0 instead of a NULL. With multiple identities pushed with the same 0 value, this violates the unique constraint in the database and the sync fails. This is the error I'm getting when I run the sync:

ErrorMessages = [2134003] Error executing a full projection! [1777018] Error executing workflow (Workforce Sync) of synchronization project (Workforce Synchronization Project). [1777124] Error executing projection step (Person - wf_worker Export) of projection configuration (Workforce Sync (ba74949b-35b4-46f2-abaf-d253ded407ce)). [1777219] Error executing projection step (Person - wf_worker Export)! [1777165] The value (0) of the key property (EOG_USER_ID@WF_DBA.WF_WORKER) of system object (Doe, John (xxxxxx)) is not unique! Another system object already has this value. at StdioProcessor.StdioProcessor._Execute(Job job) at VI.Projector.JobComponent.ProjectorComponent.Activate(String task) at VI.Projector.JobComponent.ProjectorComponent._FullProjection() ---- Start of Inner Exception ---- at VI.Projector.JobComponent.ProjectorComponent._FullProjection() at VI.Projector.Database.ProjectorExecutor.Project(ISession session, IProjectionConfiguration configuration, ProjectionOption options, ISystemObject adHocObject, CancellationToken cancellationToken) at VI.Projector.Projection.Extensions.Execute(IProjectorEngine engine, IProjectionConfiguration configuration, ProjectionOption options, ISystemObject adHocObject, CancellationToken cancellationToken) at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken) at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions) ---- Start of Inner Exception ---- at VI.Projector.Projection.ProjectorEngine.<ExecuteAsync>d__4.MoveNext() ---- Start of Inner Exception ---- at VI.Projector.Projection.ProjectorEngine.<ExecuteAsync>d__4.MoveNext() ---- Start of Inner Exception ---- at VI.Projector.Projection.ProjectorEngine.<ExecuteAsync>d__4.MoveNext() at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) --- End of stack trace from previous location where exception was thrown --- at VI.Projector.Projection.Internal.ProjectorEngineExecutionHelper.<ExecuteStepAsync>d__24.MoveNext() ---- Start of Inner Exception ---- at VI.Projector.Projection.Internal.ProjectorEngineExecutionHelper.<ExecuteStepAsync>d__24.MoveNext() at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) --- End of stack trace from previous location where exception was thrown --- at VI.Projector.Projection.Internal.ProjectorEngineExecutionHelper.<_ProcessDifferenceSetAsync>d__a5.MoveNext() at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source) at System.Linq.Buffer`1..ctor(IEnumerable`1 source) at VI.Projector.Projection.Internal.ProjectorEngineExecutionHelper.<_ReloadSystemObjectPartition>d__172.MoveNext()

How can I force a NULL to be sent for EOG_User_ID?

Thanks in advance,

Febin

  • 0 over 8 years ago

    Hi Febin,

    can you provide a screenshot of the mapping?

    And just for clarification, you are using the native Oracle connector and not the ADO.NET generic option?

    If so did you overwrite any of the DMLs (Script or SingleLine DML)?

  • 0 over 8 years ago

    Hi Markus,

    Thanks for jumping in to help. Here is a screenshot of the mapping. I've highlighted the field that is causing the issue:

    Yes, I chose Oracle Database from the options for the Native Database Connector. I did not overwrite the DML.

    I'm updating the original post with the error I'm getting on synchronization.

    Thanks,

    Febin

  • 0 over 8 years ago

    Hi Febin,

    the column EOG_USER_ID is marked as primary key column which sounds strange to me if the column allows NULLs for it.

    Can you send me the table definition please? I'm trying to dig down a little further.

  • 0 over 8 years ago

    Hi Markus,

    Sorry, I was out of office last week.

    EOG_USER_ID is only a unique key. Here are the column properties for it in sync editor:

    Whereas the primary key, WORKER_ID, has Mandatory set to Always:

    You'll see the same for Person.CentralAccount (Unique Key) and Person.UID_Person (Primary Key) respectively.

    By table definition do you mean the CREATE statement for the table?

    Again, thanks for the help!

    -Febin

  • 0 over 7 years ago

    Hi Markus,

    Just want to make sure you saw my response. Any ideas?

    Thanks,

    Febin

  • 0 over 7 years ago

    Hi Febin,

    we would need the table definition from Oracle to reproduce the issue.

  • 0 over 7 years ago

    Hi Febin,

    if you are using D1IM 7.0.2 you could ask Support to get a Hotfix for VPR#25831.

    This fixes the detection as unique key column for integer columns that do allow NULL values.

  • 0 over 7 years ago
    Did you ever get a resolution for this?

    I have a similar problem in v7.1.1. In my scenario however the column has no unique constraints but it is instead a foreign key reference.

    The connector attempts to set NULL values as the Integer 0 which is incorrect