Skip to main content
  • EN
  • Home
  • Forums
  • FME Form
  • General
  • How do I replace a single quote with 2 x single quotes when passing parameters to a stored procedure?

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 Edbo].]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 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 Edbo].OInsertData] @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.

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

Reply


Community Stats

30,162
Posts
114,483
Replies
37,809
Members
Terms & ConditionsCookie settings
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.