Skip to main content
Question

How do I replace a single quote with 2 x single quotes when passing parameters to a stored procedure?

  • October 6, 2020
  • 2 replies
  • 265 views
G

I have the requirement to grab some data from a SQL Server Database and row by row (so granular error logging can be performed) use a stored procedure to insert that data into another table. However, when calling the stored procedure to perform the insert I need to escape the single quote character (') by replacing it with 2 x single quotes.

 

I have tried using the StringReplacer transformer but when it comes to using the re-formatted string as the input for the stored procedure it does not seem to pull through at all, the SQLExecutor Transformer loops through all the records correctly but when calling:

 

EXECUTE [dbo].[InsertData] @Value(contact_number), '@Value(title)', '@Value(first_name)', '@Value(middle_names)', '@Value(surname)', '@Value(address_1)', '@Value(address_2)', '@Value(address_3)', '@Value(address_4)', '@Value(town)', '@Value(county)', '@Value(postcode)', '@Value(country)', '@Value(email_address)', '@Value(telephone_number)', '@Value(mobile_number)'

 

nothing is entered into the table.

 

However, this does work when directly connecting to the SQLExecutor Transformer, it loops through and inserts the records, but then it errors when it encounters a single quote.

 

How can I achieve this please?

 

Thanks

Gary

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

2 replies

david_r
Celebrity
  • david_rCelebrity
  • October 6, 2020

This is unfortunately a fairly common issue as the SQLExecutor does not support bind variable (vote here!)

Oracle has a pretty good workaround, but it's not available on SQL Server.

However, if your strings may contain single quotes, but no double quotes, you can do the following:

FME_SQL_DELIMITER ;
 
SET QUOTED_IDENTIFIER OFF;
 
EXECUTE [dbo].[InsertData] @Value(contact_number), "@Value(title)", "@Value(first_name)", "@Value(middle_names)", "@Value(surname)", "@Value(address_1)", "@Value(address_2)", "@Value(address_3)", "@Value(address_4)", "@Value(town)", "@Value(county)", "@Value(postcode)", "@Value(country)", "@Value(email_address)", "@Value(telephone_number)", "@Value(mobile_number)";

This way you can use double quotes to pass the strings into the InsertData function, and the single quotes contained in any of the attributes are correctly parsed as such.

G
  • gmatthews
  • Author
  • October 7, 2020

Thanks David, this reduces the number of records in the log file we need to go through for records that have not been imported

Community Stats

32,154
Posts
122,047
Replies
39,812
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.