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