Question about handling apostrophe in stored procedure param

[wasi26]

Just wondering how people have handled this problem.  In my process I am using the execute stored procedure (sp) operation to write some data to a database and it's passing field values to the sp as parameters.  Works fine when everything is normal, but as soon as an apostrophe is added into one of the fields, ka-boom.  It breaks .  So I've been messing around with ways to combat this issue, and haven't really come up wih a suitable option.  I know I can go into the form, and write some RegEx and change the single apostrophe to double which would work, but I'd have to write it in multiple events since Form gets passed to someone else and I can't have them seeing "clerk''s" instead of "clerk's".  Right now I'm trying to write an xpath statement that would replace the single apostrophe with a double with no luck.  Wondering if anyone else has had this issue and have found an easy solution.  Thanks

Mike

[Jasmin_Charbonn · Accepted solution]

Why don't you use the parametrized query option.

That way your parameters are replaced with ? and you don't get messed up with the '

Your select statement would look like: select * from table where column1 = ? and column2 = ?

Then you can map the ?s to proces variables.

Jasmin

[wasi26]

Hey Jasmin!

You may not recognize the name but you helped me a while back with Query for multiple rows and prepopulating a repeating subform with the data.   That was under Michael Whitehead at the old (in Style I guess cause the post is still here I think) Adobe Forums.  Thanks alot for that.

I think this is going to work.  I didn't realize what you were talking about at first but I finally saw the Use 'Parameterized Statement' checkbox.  I think this will work.  The way I have been doing my queries and testing them in SQL had always been using Query Anaylzer and scripting.  So when I just  used the Execute the Stored Procedure option in SQL and inserted values (in an interface that looks strikingly similar to the one you see in Workbench) the Stored procedure worked and SQL injected the neccessary '' in order to escape the apostrophe.  I'm in the process of re-configuring the Operation in Workbench now.  Will post when I've tested.  But I have a feeling this is going to solve the issue.

[wasi26]

Ok I'm trying to test and I'm getting 'Unparseable date'  message.  Got two parameters that are date fields, I've tried entering them in as follows:

10-02-2009

2009-02-10

10/02/2009

Get the error with each one.  Any ideas?

[wasi26]

Okay it's late so just to follow-up.  I've got the stored procedure working thanks, Jasmin. 

Adobe should look at their documentation on calling the stored procedure from within LiveCycle Workbench.  The documentation gives the format to call the statement as 'CALL stored_procedure_name (?);'  With the question mark being for any parameters the SP may require.  Well in my opinion they should add a note saying:

'This is the normal format for calling stored procedures in MySQL.  If you are not using MySQL, or another database that uses this format THIS STATEMENT WILL NOT WORK.  Use the format that your datbase requires for calling stored procedures, i.e Microsoft SQL is "EXECUTE   stored_procedure_name ?,?"  Notice CALL is not used instead it is EXECUTE.  Also there are no parentheses around the parameters nor is a terminator required at the end of the statement.'

That note would have made life alot easier for me today.  But anyway thanks again, Jasmin for your help.

Mike