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.
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.
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:
Get the error with each one. Any ideas?
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.