Database provisioning using stored procedures and request access attibutes

[kunaljain83]

I am trying to invoke a stored procedure for provisioning using the DB connector. As part of the SP call, I need to pass request form attributes as well. But I receive an error.

{

  "createaccountqry": "SET @xx_debug_flag := 1; SET @xx_debug_message := '${requestAccessAttributes.get('Attribute1')}' ; CALL staging.spCRHChgOwn(@xx_debug_message)"

}

2022-12-08 22:33:52,805 [quartzScheduler_Worker-4] DEBUG provisoning.DBProvisioningService  - Inside Exception..

2022-12-08 22:33:52,819 [quartzScheduler_Worker-4] ERROR provisoning.DBProvisioningService  - Error while Executing update query - No signature of method: java.lang.String.get() is applicable for argument types: (java.lang.String) values: [Attribute1]

Possible solutions: getAt(java.lang.String), next(), grep(), next(), getAt(groovy.lang.Range), getAt(groovy.lang.EmptyRange)

groovy.lang.MissingMethodException: No signature of method: java.lang.String.get() is applicable for argument types: (java.lang.String) values: [Attribute1]

Possible solutions: getAt(java.lang.String), next(), grep(), next(), getAt(groovy.lang.Range), getAt(groovy.lang.EmptyRange)

        at SimpleTemplateScript8159.run(SimpleTemplateScript8159.groovy:2)

        at com.saviynt.provisoning.DBProvisioningService$_updateAccountDB_closure3.doCall(DBProvisioningService.groovy:557)

        at com.saviynt.provisoning.DBProvisioningService.updateAccountDB(DBProvisioningService.groovy:546)

        at com.saviynt.ecm.services.ArsTaskService.updateAccountTarget(ArsTaskService.groovy:10677)

        at com.saviynt.ecm.services.ArsTaskHelperService$_whenTaskTypeIsTwelveUpdateAccount_closure44.doCall(ArsTaskHelperService.groovy:2659)

        at com.saviynt.ecm.services.ArsTaskHelperService.whenTaskTypeIsTwelveUpdateAccount(ArsTaskHelperService.groovy:2649)

        at com.saviynt.ecm.services.ArsTaskHelperService$_completeAutoProvTasksUpgraded_closure1.doCall(ArsTaskHelperService.groovy:192)

        at com.saviynt.ecm.services.ArsTaskHelperService.completeAutoProvTasksUpgraded(ArsTaskHelperService.groovy:152)

        at MultipleProvisioningJob.execute(MultipleProvisioningJob.groovy:216)

        at org.quartz.core.JobRunShell.run(JobRunShell.java:199)

        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)

2022-12-08 22:33:52,833 [quartzScheduler_Worker-4] DEBUG provisoning.DBProvisioningService  - acctask completed

2022-12-08 22:33:52,834 [quartzScheduler_Worker-4] DEBUG provisoning.DBProvisioningService  - acctask each loop complete..

2022-12-08 22:33:52,834 [quartzScheduler_Worker-4] INFO  provisoning.DBProvisioningService  - End updateAccountDB

2022-12-08 22:33:52,834 [quartzScheduler_Worker-4] DEBUG services.ArsTaskService  - Inside updateProvisioningTries..

2022-12-08 22:33:52,878 [quartzScheduler_Worker-4] DEBUG services.ArsTaskService  -  Entering provisionAccesstoAccountSaviynt

2022-12-08 22:33:52,878 [quartzScheduler_Worker-4] DEBUG services.ArsTaskService  - {XBBNWQX=[]}

2022-12-08 22:33:52,878 [quartzScheduler_Worker-4] DEBUG services.ArsTaskService  - UPDATEACCOUNT

2022-12-08 22:33:52,878 [quartzScheduler_Worker-4] DEBUG services.ArsTaskService  - accountID before merge = XBBNWQX

2022-12-08 22:33:52,882 [quartzScheduler_Worker-4] DEBUG services.ArsTaskService  - accountID after merge = XBBNWQX

2022-12-08 22:33:52,885 [quartzScheduler_Worker-4] DEBUG services.ArsTaskService  - Config for ARSTASKCOMPNEWPWDcom.saviynt.ecm.utility.domain.EcmConfig : ARSTASKCOMPNEWPWD

2022-12-08 22:33:52,887 [quartzScheduler_Worker-4] DEBUG services.ArsTaskService  - Task Complete Email Template For New Account Password = null

2022-12-08 22:33:52,887 [quartzScheduler_Worker-4] DEBUG services.ArsTaskService  - start converting accTasksMap to endpointaccTasksMap

2022-12-08 22:33:52,887 [quartzScheduler_Worker-4] DEBUG services.ArsTaskService  - finished converting accTasksMap to endpointaccTasksMap

2022-12-08 22:33:52,889 [quartzScheduler_Worker-4] DEBUG services.ArsTaskHelperService  - In whenTaskTypeIsNineUpdateUser

2022-12-08 22:33:52,889 [quartzScheduler_Worker-4] DEBUG services.ArsTaskHelperService  - Query Update User Tasks=

                select rr from ArsTasks rr where rr.users is not null and rr.externalConnection is not null

                and rr.tasktype = 9 and (rr.endDate = null or rr.endDate > :ed)

                and rr.id in (29921772)

[rushikeshvartak]

Dynamic attribute can be access directly 

example : Attribute1 

then json will be ${Attribute1}

{

  "createaccountqry": "SET @xx_debug_flag := 1; SET @xx_debug_message := '${Attribute1}' ; CALL staging.spCRHChgOwn(@xx_debug_message)"

}

[rushikeshvartak]

task.requestKey for requestKey

requestAccesskey will be individually not as array

[kunaljain83]

I am able to retrieve the requestkey but have run into another problem. One of the dynamic attributes has new line characters and when I pass it to the stored procedure it breaks with unable to execute the updatequery

{
"updateaccountqry": "SET @xx_debug_flag := 2; SET @xx_attr1 := '${task.requestKey}' ; SET @xx_attr2 := '${AccountType}' ; SET @xx_attr3 := replace('${AdditionalAccountDetails}','\n','|') ; SET @xx_attr4 := '${CurrentAccountOwner}' ; SET @xx_attr5 := '${NewAccountOwner}'; CALL staging.spCRHChgOwn(@xx_debug_flag,@xx_attr1,@xx_attr2,@xx_attr3,@xx_attr4,@xx_attr5)"
}

[rushikeshvartak]

AdditionalAccountDetails.replace('\n', '|')

if above does not work change sql query to remove newline

[kunaljain83]

I did try that, and didn't work. Which query do I need to change

2022-12-13 20:04:36,786 [quartzScheduler_Worker-15] DEBUG provisoning.DBProvisioningService - Binding map is [lastApprover:XBBNWQX, accountName:XBBNWQX, requestkey:com.saviynt.ecm.workflow.ARS_Requests : 2978783, AdditionalAccountDetails:MNE: UAM
TYPE: WINDOWS
ACCOUNTID: UAMM01M , CurrentAccountOwner:xbbnwqx, AccountType:HELLOWORLD, requestor:XBBNWQX, usermanager:XBBNQ9Z, endpoint:Service Account Ownership Change, task:com.saviynt.ecm.task.ArsTasks : 30195864, ENDPOINT:6308, updatetaskuser:null, ACCOUNTKEY:25187530, NewAccountOwner:XBBNTXX, user:XBBNWQX]
2022-12-13 20:04:36,787 [quartzScheduler_Worker-15] DEBUG provisoning.DBProvisioningService - templateUpdateAccountStr after make is {
"updateaccountqry": "SET @xx_debug_flag := 2; SET @xx_attr1 := 'com.saviynt.ecm.workflow.ARS_Requests : 2978783' ; SET @xx_attr2 := 'HELLOWORLD' ; SET @xx_attr3 := 'MNE: UAM
TYPE: WINDOWS
ACCOUNTID: UAMM01M '.replace('
','|') ; SET @xx_attr4 := 'xbbnwqx' ; SET @xx_attr5 := 'XBBNTXX'; CALL staging.spCRHChgOwn(@xx_debug_flag,@xx_attr1,@xx_attr2,@xx_attr3,@xx_attr4,@xx_attr5)"
}
2022-12-13 20:04:36,787 [quartzScheduler_Worker-15] DEBUG provisoning.DBProvisioningService - Inside Exception..
2022-12-13 20:04:36,788 [quartzScheduler_Worker-15] ERROR provisoning.DBProvisioningService - Error while Executing update query - Error parsing JSON
org.codehaus.groovy.grails.web.converters.exceptions.ConverterException: Error parsing JSON
at grails.converters.JSON.parse(JSON.java:292)
at com.saviynt.provisoning.DBProvisioningService$_updateAccountDB_closure3.doCall(DBProvisioningService.groovy:563)
at com.saviynt.provisoning.DBProvisioningService.updateAccountDB(DBProvisioningService.groovy:546)
at com.saviynt.ecm.services.ArsTaskService.updateAccountTarget(ArsTaskService.groovy:10677)
at com.saviynt.ecm.services.ArsTaskHelperService$_whenTaskTypeIsTwelveUpdateAccount_closure44.doCall(ArsTaskHelperService.groovy:2659)
at com.saviynt.ecm.services.ArsTaskHelperService.whenTaskTypeIsTwelveUpdateAccount(ArsTaskHelperService.groovy:2649)
at com.saviynt.ecm.services.ArsTaskHelperService$_completeAutoProvTasksUpgraded_closure1.doCall(ArsTaskHelperService.groovy:192)
at com.saviynt.ecm.services.ArsTaskHelperService.completeAutoProvTasksUpgraded(ArsTaskHelperService.groovy:152)
at MultipleProvisioningJob.execute(MultipleProvisioningJob.groovy:216)
at org.quartz.core.JobRunShell.run(JobRunShell.java:199)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)
Caused by: org.codehaus.groovy.grails.web.json.JSONException: Unterminated string at character 179 of {
"updateaccountqry": "SET @xx_debug_flag := 2; SET @xx_attr1 := 'com.saviynt.ecm.workflow.ARS_Requests : 2978783' ; SET @xx_attr2 := 'HELLOWORLD' ; SET @xx_attr3 := 'MNE: UAM
TYPE: WINDOWS
ACCOUNTID: UAMM01M '.replace('
','|') ; SET @xx_attr4 := 'xbbnwqx' ; SET @xx_attr5 := 'XBBNTXX'; CALL staging.spCRHChgOwn(@xx_debug_flag,@xx_attr1,@xx_attr2,@xx_attr3,@xx_attr4,@xx_attr5)"
}
at grails.converters.JSON.parse(JSON.java:283)
... 10 more
2022-12-13 20:04:36,803 [quartzScheduler_Worker-15] DEBUG provisoning.DBProvisioningService - acctask completed
2022-12-13 20:04:36,803 [quartzScheduler_Worker-15] DEBUG provisoning.DBProvisioningService - acctask each loop complete

[rushikeshvartak]

Dynamic attribute query