Please let me know how do I pass apostrophe in Inputs parameter for executestoredprocedure function
ocal!sp: fn!executestoredprocedure( dataSourceName: "jdbc/DRO", procedureName: "procCSS_Search", inputs: { { name: "ssn", value: ri!ssn }, { name: "lastname", value: ri!lastName }, { name: "firstname", value: ri!firstName }, { name: "caseid", value: ri!caseId }, { name: "primaryCauseTF", value: ri!primaryCauseTF }, { name: "causenum", value: ri!causeNum }, { name: "oagnumber", value: ri!oagNumber }, { name: "personid", value: ri!personId }, { name: "phone", value: ri!phone }, { name: "casestatus", value: ri!caseStatusCode }, }, timeout: 300 ),
Discussion posts and replies are publicly visible
Other than Stefan's notes, from an Appian perspective this looks fine to me as there is nothing preventing a text-valued rule input to contain an apostrophe. Are you able to call the procedure directly in the DB with apostrophe inputs? And confirm the procedure is not parsing them out?
Thank you all . I am new to Appian programming . This is what I am getting without apostrophe
But with apostrophe getting incorrect syntax in error messages
I think this is an issue in the stored procedure and not Appian related.
Okay thank you Stefan for the updates
Yes this appears to be due to the procedure likely building a query and not doubling single quotes such as:
Other than updating the procedure, you could try duplicating the single quotes in your call to the procedure, such as with replace(). Note I would recommend building this logic in a new shared/environmental rule where it can be adjusted in one location if needed, such as adding a check for a double apostrophe, etc:
a!localVariables( local!lastName: "joss'e", local!index: search("'",local!lastName), if( local!index>0, /* apostrophe found, double it */ replace( local!lastName, search("'",local!lastName), 1, "''" ), local!lastName ) )
Additionally, if this procedure is not performing complex logic with temp tables, etc, you can likely achieve the same in Appian with a!queryEntity(), much more easily.
Thank you, Chris, and I appreciate your valuable input and suggestion to fix these issues on my end. You guys are really helpful. Thank you All.