JSON / SQL Server Database / Incorrect Syntax / Clean

[gpre7086]

Hello Friends!

I have a integration flow where I'm posting JSON via a stored procedure into a MS SQL Server database and some data is causing issues.

The below JSON example illustrates the issue. I'm sending "Smith's Doghouse" - what SQL expects is double apostrophes e.g. "Smith''s Doghouse". I get the error far below. I know I could replace single occurrences of ' with ''; however this seems like something that has already been solved for before. Also - there are likely other valid JSON characters that would be problematic for SQL if not cared for.

Any guidance would be appreciated!!! Thank you. /Greg

{
    "person": {
        "name": "John Smith",
        "company": "Smith's Doghouse",
        "age": 21,
        "has_dog": "Y"
    }
}

Error:

Processing failed failed with below error:Error detail: PreparedStatementCallback; uncategorized SQLException; SQL state [S0001]; error code [102]; Incorrect syntax near 'S'.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'S'.Correlation ID: AGWCPJcdgRRGeeIpSWqiNUD3ajh3Message ID: AGWCPJdAHFuL5Oz5bhLTLt25bETeIflow Name: Package_Pricing_Discounts_From_SAP_To_OMSDate: 20-12-2023 01:00 / SAP CPI – Tenant Number: qa-varsity.integrationsuite.cfapps.us10-002.hana.ondemand.com

[Bais]

https://stackoverflow.com/questions/64470125/how-to-escape-double-and-single-quotes-in-a-json-array

If you are writing the SQL directly then, given a valid JSON text you need to:

• Surround it with single quotes
• Escape any single quotes in it
• Escape any escape sequences

You don't need to do anything with double quotes.

So the JSON: [1, "John O'Brian", { "hello": "world" }]

Would become Messages='[1, "John O\'Brian", { "hello": "world" }]'

[gpre7086]

Thank you Frederico!!

So to confirm - there is no built in function to prep-json prior to sending to the database? Beyond that it looks like a simple groovy script. /Greg

[Bais]

Not for now, you can ask on influence site to add this feature an object in Integration suite

https://influence.sap.com/sap/ino/#/campaign/2282

[Bais]

"This is' it".replace("'", "\\'");