Error while Saving the connection and updating the account

[adityachadde]

Hi,

We are trying to save the connection we are using the below update account Json and we are getting an error as an error while parsing JSON beacuse of ${user.jobDescription.replace("'", "''")} function.

{"UpdateAccountQry":["Update Declic01.users set TECHNICAL_VERSION=TECHNICAL_VERSION+1,SURNAME='${user.lastname}',FIRSTNAME='${user.firstname}',AUDIT_LAST_USER='SAVIYNT',AUDIT_LAST_DATE=TO_TIMESTAMP(TO_CHAR(SYSTIMESTAMP, 'DD-MON-RR HH.MI.SS.FF9 AM'), 'DD-MON-RR HH.MI.SS.FF9 AM'),QUALIFICATION='${user.jobDescription}',DOMAIN=(CASE WHEN '${user.customproperty5}' LIKE '%DC=fr,DC=adeccotest,DC=net%' THEN 'FRTEST' WHEN '${user.customproperty5}' LIKE '%DC=emea,DC=adeccotest,DC=net%' THEN 'EMEATEST' ELSE '' END),EMAIL='${user.email}',LOGIN='${user.customproperty1}' WHERE MATRICULE='${user.employeeid}'"]}

It is working as expected in create account JSON but throwing an error as update account JSON.

{"createAccountQry":["INSERT INTO Declic01.USERS (ID,TECHNICAL_VERSION,FIRSTNAME,SURNAME,LOGIN,AUDIT_INITIAL_USER,AUDIT_INITIAL_DATE,DOMAIN,EMAIL,QUALIFICATION,MATRICULE) VALUES(SYS_GUID(),'1','${user.firstname}','${user.lastname}','${user.customproperty1}','SAVIYNT',TO_TIMESTAMP(TO_CHAR(SYSTIMESTAMP, 'DD-MON-RR HH.MI.SS.FF9 AM'), 'DD-MON-RR HH.MI.SS.FF9 AM'),(CASE WHEN '${user.customproperty5}' LIKE '%DC=fr,DC=adeccotest,DC=net%' THEN 'FRTEST' WHEN '${user.customproperty5}' LIKE '%DC=emea,DC=adeccotest,DC=net%' THEN 'EMEATEST' ELSE '' END),'${user.email}','${user.jobDescription.replace("'", "''")}','${user.employeeid}')"]}

When we are tried using the escape characters as below then the query is not working, and we are getting an error while executing the query.

QUALIFICATION='${user.jobDescription.replace(\"'\", \"''\")}'

What else we should use to replace ' (single Quotes )with '' (Double Quotes) in JSON.

Why same json is working in create account JSON and not working in update account JSON.

Best Regards,

Aditya Chadde

[Dhruv_S · Accepted solution]

Hi @adityachadde 

Thanks for reaching out to Saviynt forums. Are you using the above JSON in DB connector?

Can you try with the escapeSql(String str) method mentioned in the below link if it helps. There is an example for the turning single-quotes into doubled single-quotes.

JSON - Handling Special Characters in DB connectio... - Saviynt Forums - 50545

Regards,

Dhruv Sharma

[adityachadde]

Hi,

It worked,

Thanks

[rushikeshvartak]

{
	"CreateAccountQry": 
		[
			"${'INSERT INTO Declic01.USERS (ID,TECHNICAL_VERSION,FIRSTNAME,SURNAME,LOGIN,AUDIT_INITIAL_USER,AUDIT_INITIAL_DATE,DOMAIN,EMAIL,QUALIFICATION,MATRICULE) VALUES(SYS_GUID(),'1','+user.firstname+','+user.lastname+','+user.customproperty1+','SAVIYNT',TO_TIMESTAMP(TO_CHAR(SYSTIMESTAMP, 'DD-MON-RR HH.MI.SS.FF9 AM'), 'DD-MON-RR HH.MI.SS.FF9 AM'),(CASE WHEN '+user.customproperty5+' LIKE '%DC=fr,DC=adeccotest,DC=net%' THEN 'FRTEST' WHEN '+user.customproperty5+' LIKE '%DC=emea,DC=adeccotest,DC=net%' THEN 'EMEATEST' ELSE '' END),'+user.email+','+user.jobDescription.replace(''','')+','+user.employeeid+')}"
		]
}