How to add single quote in DB query

[IAM-VP]

Hi Team ,

I want to achive below use case :

if End Date is NULL :

  "updateaccountqry": 
  "update Tablename set Email='XXXX' ,LASTNAME = 'XXX',firstname = 'XXX',Status = '1',COMPANY = 'XX',COUNTRY = 'XX',TITLE = 'XX',DivShortName = 'XX',BaShortName = 'XX',PRODUCTGROUPCODE = 'XX',DISPLAYNAME = 'XX',Department = 'XX,XX,XX',enddate = null where Identifier = 'XX'"

If End Date has some value :
"updateaccountqry": 
  "update Tablename set Email='XXXX' ,LASTNAME = 'XXXX',firstname = 'XXXX',Status = '1',COMPANY = 'XXXX',COUNTRY = 'XXXX',TITLE = 'XXXX New',DivShortName = 'XXXX',BaShortName = 'XXXX',PRODUCTGROUPCODE = 'XXXX',DISPLAYNAME = 'XXXX',Department = 'XXXX',enddate = '2023-04-20 00:00:00.0' where Identifier = 'XXXX'"

Update Account JSON is :
{
"updateaccountqry":
"update Tablename set Email='${user.email}' ,LASTNAME = '${user.lastname}',firstname = '${user.firstname}',Status = '${user.statuskey}',COMPANY = '${user.companyname}',COUNTRY = '${user.country}',TITLE = '${user.title}',DivShortName = 'PG',BaShortName = '${user.customproperty19}',PRODUCTGROUPCODE = '${user.customproperty21}',DISPLAYNAME = '${user.firstname} ${user.lastname}',Department = 'PG,${user.customproperty19},${user.customproperty21}',enddate = ${null!=user.enddate?'${user.enddate}':null} where Identifier = '${user.employeeid}'"
}

what should be the syntax if I want above output

[RakeshMG]

Please try using backward slash "\"

Refer to following link for example : https://dev.mysql.com/doc/refman/8.0/en/string-literals.html

[DixshantValecha]

Based on the provided use case, you can use the following syntax to achieve the output:

updateaccountqry field in your JSON object based on whether the enddate field is null or not. You can use a conditional statement to achieve this. Here’s an example:

if (user.enddate == null) {
    updateaccountqry = "update Tablename set Email='${user.email}' ,LASTNAME = '${user.lastname}',firstname = '${user.firstname}',Status = '${user.statuskey}',COMPANY = '${user.companyname}',COUNTRY = '${user.country}',TITLE = '${user.title}',DivShortName = 'PG',BaShortName = '${user.customproperty19}',PRODUCTGROUPCODE = '${user.customproperty21}',DISPLAYNAME = '${user.firstname} ${user.lastname}',Department = 'PG,${user.customproperty19},${user.customproperty21}',enddate = null where Identifier = '${user.employeeid}'";
} else {
    updateaccountqry = "update Tablename set Email='${user.email}' ,LASTNAME = '${user.lastname}',firstname = '${user.firstname}',Status = '${user.statuskey}',COMPANY = '${user.companyname}',COUNTRY = '${user.country}',TITLE = '${user.title} New',DivShortName = 'PG',BaShortName = '${user.customproperty19}',PRODUCTGROUPCODE = '${user.customproperty21}',DISPLAYNAME = '${user.firstname} ${user.lastname}',Department = 'PG,${user.customproperty19},${user.customproperty21}',enddate = '${user.enddate}' where Identifier = '${user.employeeid}'";
}

Please validate and let us know if further details are needed on this.

[IAM-VP]

Hi @DixshantValecha ,

Thansk @DixshantValecha prompt response. I tried proposed solution is not working. Getting error in logs.

[DixshantValecha]

Hi @IAM-VP,

Thank you for the update. To better assist you, could you please provide more information about the error you are encountering.

Also, I would like to clarify that the syntax I provided earlier was intended as an example, and may need to be modified to fit the specific requirements of your use case.

[IAM-VP]

Hi @DixshantValecha ,

Updateaccount json :

if (user.enddate == null) {
"updateaccountqry":
"update AdImportedPrincipal set Email='${user.email}' ,LASTNAME = '${user.lastname}',firstname = '${user.firstname}',Status = '${user.statuskey}',COMPANY = '${user.companyname}',COUNTRY = '${user.country}',TITLE = '${user.title}',DivShortName = 'PG',BaShortName = '${user.customproperty19}',PRODUCTGROUPCODE = '${user.customproperty21}',DISPLAYNAME = '${user.firstname} ${user.lastname}',Department = 'PG,${user.customproperty19},${user.customproperty21}',enddate = null where Identifier = '${user.employeeid}'"
}else{
"updateaccountqry":
"update AdImportedPrincipal set Email='${user.email}' ,LASTNAME = '${user.lastname}',firstname = '${user.firstname}',Status = '${user.statuskey}',COMPANY = '${user.companyname}',COUNTRY = '${user.country}',TITLE = '${user.title}',DivShortName = 'PG',BaShortName = '${user.customproperty19}',PRODUCTGROUPCODE = '${user.customproperty21}',DISPLAYNAME = '${user.firstname} ${user.lastname}',Department = 'PG,${user.customproperty19},${user.customproperty21}',enddate = '${user.enddate}' where Identifier = '${user.employeeid}'"
}

[rushikeshvartak]

Please share error

[IAM-VP]

Hi @rushikeshvartak ,

below error logged in the file :

2023-04-13/11:54:37.262 [{}] [quartzScheduler_Worker-5] DEBUG provisoning.DBProvisioningService - Enter checkForStoredProcCall
2023-04-13/11:54:37.262 [{}] [quartzScheduler_Worker-5] DEBUG provisoning.DBProvisioningService - in lowercaseQuery not null
2023-04-13/11:54:37.262 [{}] [quartzScheduler_Worker-5] DEBUG provisoning.DBProvisioningService - Stmt is not stored proc or function call
2023-04-13/11:54:37.262 [{}] [quartzScheduler_Worker-5] DEBUG provisoning.DBProvisioningService - Exit checkForStoredProcCall
2023-04-13/11:54:37.270 [{}] [quartzScheduler_Worker-5] ERROR provisoning.DBProvisioningService - Error occured in Excecuting Query
2023-04-13/11:54:37.270 [{}] [quartzScheduler_Worker-5] DEBUG provisoning.DBProvisioningService - Closing statements
2023-04-13/11:54:37.270 [{}] [quartzScheduler_Worker-5] DEBUG provisoning.DBProvisioningService - Closing stmt
2023-04-13/11:54:37.270 [{}] [quartzScheduler_Worker-5] DEBUG provisoning.DBProvisioningService - Inside Exception..
2023-04-13/11:54:37.270 [{}] [quartzScheduler_Worker-5] DEBUG rest.RestUtilService - Got showLogs = true
2023-04-13/11:54:37.271 [{}] [quartzScheduler_Worker-5] ERROR provisoning.DBProvisioningService - Error while Executing update query - Exception occured while Excecuting Query

[DixshantValecha]

Thank you for the update. We kindly request that you refer to the below query as an example and attempt to develop the code accordingly.

{
"updateaccountqry": "update Tablename set Email='${user.email}' ,LASTNAME = '${user.lastname}',firstname = '${user.firstname}',Status = '${user.statuskey}',COMPANY = '${user.companyname}',COUNTRY = '${user.country}',TITLE = '${user.title}',DivShortName = 'PG',BaShortName = '${user.customproperty19}',PRODUCTGROUPCODE = '${user.customproperty21}',DISPLAYNAME = '${user.firstname} ${user.lastname}',Department = 'PG,${user.customproperty19},${user.customproperty21}',enddate = ${user.enddate != null ? (user.enddate == '' ? 'null' : '''' + user.enddate + '''') : 'null'} where Identifier = '${user.employeeid}'"
}

Thanks.