Appian Community and Appian Academy are being upgraded. As a part of the upgrade, Appian Community is currently in read-only mode, and user registration is disabled until August 3. We apologize for any inconvenience this may cause, but a more secure, stable, and performant Community experience is coming soon!
The new Appian Community launches August 3, followed by Appian Academy on August 7. During the migration, Appian Community Edition, Appian Academy, Documentation, Certifications, Instructor-led Customer Training, Partner Sales Training & Accreditation, and Forum (for Appian Partners and Customers only) will remain available.
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.