Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

MS Sql server CreateAccount json using DB connector

shubhangsinha_
New Contributor III
New Contributor III

Hello Everyone, 

I am trying to provision account in MS Sql server database using the below query and would like any help if possible-->

{
"createaccountqry":"INSERT INTO [dbo].[AMUser] ([Id],[ClusterId] ,[CompanyId],[DepartmentId],[ApplicationId],[UserId],[Email],[LevelId],[StatusId],[CategoryId],[CounterParty],[CreatedUser],[RoleCode])VALUES ('9435','1' , '8' , '105','18','SHUBHANGS','shubhasinha@test.com','2','1','0','L000','Atn7,'Other')"

}

All these attributes are "not null " type in the database so I am populating them all with hardcoded values but it is giving me the below error attached , from which I cannot really understand the problem. I have also tried the below json -->

{
"createaccountqry":"INSERT INTO [dbo].[AMUser] ([Id],[ClusterId] ,[CompanyId],[DepartmentId],[ApplicationId],[UserId],[Email],[LevelId],[StatusId],[CategoryId],[CounterParty],[CreatedUser],[CreatedDate],[RoleCode])VALUES (\'9435\',\'1\' , \'8\' , \'105\',\'18\',\'SHUBHANGS\',\'shubhasinha@test.com\',\'2\',\'1\',\'0\',\'L000\',\'Attn70\',\'Other\')"
}

Error -->

"ecm-worker","2023-06-27T08:19:55.293+00:00","{"log":"2023-06-27 08:19:54,504 [quartzScheduler_Worker-7] DEBUG provisoning.DBProvisioningService - Stmt is not stored proc or function call\n","stream":"stdout","time":"2023-06-27T08:19:54.50502627Z"}"
"ecm-worker","2023-06-27T08:19:55.293+00:00","{"log":"2023-06-27 08:19:54,504 [quartzScheduler_Worker-7] DEBUG provisoning.DBProvisioningService - Exit checkForStoredProcCall\n","stream":"stdout","time":"2023-06-27T08:19:54.505030301Z"}"
"ecm-worker","2023-06-27T08:19:55.293+00:00","{"log":"2023-06-27 08:19:54,545 [quartzScheduler_Worker-7] ERROR provisoning.DBProvisioningService - Error occured in Excecuting Query\n","stream":"stdout","time":"2023-06-27T08:19:54.545779882Z"}"
"ecm-worker","2023-06-27T08:19:55.293+00:00","{"log":"2023-06-27 08:19:54,545 [quartzScheduler_Worker-7] DEBUG provisoning.DBProvisioningService - Closing statements\n","stream":"stdout","time":"2023-06-27T08:19:54.545800581Z"}"
"ecm-worker","2023-06-27T08:19:55.293+00:00","{"log":"2023-06-27 08:19:54,545 [quartzScheduler_Worker-7] DEBUG provisoning.DBProvisioningService - Closing stmt\n","stream":"stdout","time":"2023-06-27T08:19:54.54580481Z"}"
"ecm-worker","2023-06-27T08:19:55.293+00:00","{"log":"2023-06-27 08:19:54,545 [quartzScheduler_Worker-7] DEBUG rest.RestUtilService - Got showLogs = true\n","stream":"stdout","time":"2023-06-27T08:19:54.545890698Z"}"
"ecm-worker","2023-06-27T08:19:55.293+00:00","{"log":"2023-06-27 08:19:54,546 [quartzScheduler_Worker-7] ERROR provisoning.DBProvisioningService - Exception occurred in insert SHUBHANGS-Exception occured while Excecuting Query\n","stream":"stdout","time":"2023-06-27T08:19:54.546051041Z"}"
"ecm-worker","2023-06-27T08:19:55.293+00:00","{"log":"2023-06-27 08:19:54,546 [quartzScheduler_Worker-7] DEBUG provisoning.DBProvisioningService - Status after inserts... false\n","stream":"stdout","time":"2023-06-27T08:19:54.546063398Z"}"
"ecm-worker","2023-06-27T08:19:55.293+00:00","{"log":"2023-06-27 08:19:54,546 [quartzScheduler_Worker-7] ERROR provisoning.DBProvisioningService - Error while creating account -SHUBHANGS stopping the Job\n","stream":"stdout","time":"2023-06-27T08:19:54.546066222Z"}"
"ecm-worker","2023-06-27T08:19:55.293+00:00","{"log":"2023-06-27 08:19:54,548 [quartzScheduler_Worker-7] INFO provisoning.DBProvisioningService - End createAccountDB\n","stream":"stdout","time":"2023-06-27T08:19:54.548514055Z"}"

 

4 REPLIES 4

SB
Saviynt Employee
Saviynt Employee

Can you confirm if the same query is working in case you directly execute it on the DB?


Regards,
Sahil

shubhangsinha_
New Contributor III
New Contributor III

Here is the sample query which works in the database , you can  see the variable types too, for testing purpose we are hardcoding these values. Tried to convert it into json as replied initially but it is failing

INSERT INTO [dbo].[AMUser]
           ([ClusterId]
           ,[CompanyId]
           ,[DepartmentId]
           ,[ApplicationId]
           ,[UserId]
           ,[Email]
           ,[LevelId]
           ,[CategoryId]
           ,[CounterParty]
           ,[StatusId]
           ,[CreatedUser]
           ,[CreatedDate]
           ,[ModifiedUser]
           ,[ModifiedDate]
           ,[RoleCode])
     VALUES
           (<ClusterId, int,>
           ,<CompanyId, int,>
           ,<DepartmentId, int,>
           ,<ApplicationId, int,>
           ,<UserId, varchar(50),>
           ,<Email, varchar(100),>
           ,<LevelId, int,>
           ,<CategoryId, int,>
           ,<CounterParty, varchar(50),>
           ,<StatusId, int,>
           ,<CreatedUser, varchar(50),>
           ,<CreatedDate, datetime,>
           ,<ModifiedUser, varchar(50),>
           ,<ModifiedDate, datetime,>
           ,<RoleCode, varchar(100)>)

armaanzahir
Valued Contributor
Valued Contributor

Hi @shubhangsinha_ ,

It seems you've missed a single quote enclosing the string value of the CounterParty column value defined:

The below json should work:

{
"createaccountqry":"INSERT INTO [dbo].[AMUser] ([Id],[ClusterId] ,[CompanyId],[DepartmentId],[ApplicationId],[UserId],[Email],[LevelId],[StatusId],[CategoryId],[CounterParty],[CreatedUser],[RoleCode])VALUES ('9435','1' , '8' , '105','18','SHUBHANGS','shubhasinha@test.com','2','1','0','L000','Atn7','Other')"
}

For variable inclusion, the sample query would be:

{

"createaccountqry": "INSERT INTO [dbo].[User] ([Username] ,[Firstname],[Lastname]) VALUES (\'${accountName}\' , \'${user.firstname}\' , \'${user.lastname}\')"

}

 

Thanks,

Armaan

Regards,
Md Armaan Zahir

Thank you very much