• State Not Answered
  • Replies 3 replies
  • Subscribers 93 subscribers
  • Views 982 views
  • Users 0 members are here
Options
Related

WhereClause validation issue when creating Dynamic Role with process step "HandleObjectComponent - Insert"

manu@unibe
over 2 years ago

Dear all,

I am currently working on a custom process where I would like to create a new Dynamic Role in a process step.

Therefore, I added a proccess step of type "HandleObjectComponent - Insert" with the following parameter definitions

val_ObjectKeyBaseTree

Value = "&Out(XObjectKeyOrg)&"

val_UID_DialogTableObjectClass

Value = "QER-T-Person"

val_WhereClause

Value = String.Format("EXISTS (SELECT 1 FROM (SELECT UID_Person FROM PersonInAERole WHERE EXISTS (SELECT 1 FROM (SELECT UID_AERole FROM AERole WHERE UID_AERole = '{0}') as X WHERE X.UID_AERole = PersonInAERole.UID_AERole)) as X WHERE X.UID_Person = Person.UID_Person)", $CCC_UID_AERole$)

The goal of the WhereClause is to select all objects from Person which are assigned to a particular OneIM Application Role (AERole).

Once I let my process be generated for the Job Qeue, the created process step ends in the frozen state with the following error:

(2022-03-31 13:00:54.763) [810306] Error while running 'OnSaving' in logic module 'QER.Customizer.DynamicGroup'.
[2133271] The SQL statement in the field 'Condition' is not correct.
[System.NullReferenceException] Object reference not set to an instance of an object.
   at VI.JobService.ProcessorThread._ExecuteJob(Job job, JobResult result)
   at VI.JobService.JobComponents.HandleObjectComponent.Activate(String task)
   at VI.DB.Implementation.HandleObjectKernel.Activate(String task)
   at VI.DB.Implementation.HandleObjectKernel._OperationInsert(IUnitOfWork optionalUnitOfWork)
   at VI.DB.Implementation.HandleObjectKernel._Save(IUnitOfWork unitOfWork, IEntity entity)
   at VI.DB.Sync.SyncUnitOfWorkExtensions.Put(IUnitOfWork unitOfWork, IEntity entity, PutOptions options)
   at VI.Base.SyncActions.Do[T1,T2,T3](T1 p1, T2 p2, T3 p3, Func`4 action)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
…

Since I was not able to identify an SQL syntax error in the WhereClause, I copied the generated WhereClause parameter value from the parameters section in the JobQeueInfo tool and manually added a Dynamic Role in Manager with the same WhereClause. This worked just fine. I was able to save the Dynamic Role without an error.

Does somebody sees what I am doing wrong with the WhereClause definition in the process step?

Thank you.

Manuel

Top Replies

  • 0 over 2 years ago

    Hi , Manuel,

    I can't find the point of error either, but a workaround could be creating the Dynamic Role from a  process step that runs a script?   See here:

    https://www.oneidentity.com/community/identity-manager/f/forum/20917/create-dynamic-role-for-itshop-from-script

    Maybe that'll help you debug your problem , too.

    Hth!

  • 0 over 2 years ago in reply to CVU

    Thank you for your answer and sharing your idea of creating a Dynamic Role from a script.

    I will try this approach.

  • 0 over 2 years ago

    As suggested by  , I wrote a simple scripts to reach my goal. This is working perfectly for now.

    Public Function CCC_HostingOU_CreateDynamicGroup(objectKey As String, uidAERole As String) As String
	
	Dim dynamicGroup As IEntity = Nothing
	Dim f As ISqlFormatter = Session.SqlFormatter
	
	Dim whereClause = String.Format("EXISTS (SELECT 1 FROM (SELECT UID_Person FROM PersonInAERole WHERE EXISTS (SELECT 1 FROM (SELECT UID_AERole FROM AERole WHERE UID_AERole = '{0}') as X WHERE X.UID_AERole = PersonInAERole.UID_AERole)) as X WHERE X.UID_Person = Person.UID_Person)", uidAERole)
	
	dynamicGroup = Session.Source.CreateNew("DynamicGroup")
	
	dynamicGroup.PutValue("ObjectKeyBaseTree", objectKey)
	dynamicGroup.PutValue("UID_DialogTableObjectClass", "QER-T-Person")
	dynamicGroup.PutValue("WhereClause", whereClause)
	
	Using uow As IUnitOfWork = Session.StartUnitOfWork()
        uow.Put(dynamicGroup)
        uow.Commit()
    End Using
	
	Return dynamicGroup.GetValue("UID_DynamicGroup")
	
End Function