Hi. I am trying to design a job which takes input from a data table containing filepaths to documents
e.g.
In the job I want to replace the relative path C:\1stdir32\ with nothing.
In SQL, I would simply run this:
UPDATE Documents
SET [Path] = REPLACE([Path], 'C:\1stdir32\', '')
However, I want to achieve this in a job with a context parameter that will prompt the user for a relative path at runtime. I would like this context parameter to be of type "Directory".
The issue with this though is Talend's insistence on using forward slashes instead of the standard Windows backslashes in paths (I know this is due to it being Java based.)
So when I set this up, the directory being passed to the context parameter at runtime is C:/1stdir32/ and not C:\1stdir32\.
This basically means nothing gets updated when the job runs.
So I'm seeing is there is a way I can replace the forward slashes in the context parameter to backslashes using the StringHandling function. But I'm not getting any joy.
I am executing the code via a tDBRow component. Here's my code:
UPDATE Documents
SET [Path] = REPLACE(CAST([Path] AS VARCHAR(MAX)), '" + StringHandling.CHANGE(context.parameter_path_relative, "/", "\") + "', '')
WHERE CAST([Path] AS VARCHAR(MAX)) LIKE '" +StringHandling.CHANGE(context.parameter_path_relative, "/", "\") + "%'
This doesn't work, so I tried escaping the backslash by doubling up on it, but still no joy.
UPDATE Documents
SET [Path] = REPLACE(CAST([Path] AS VARCHAR(MAX)), '" + StringHandling.CHANGE(context.parameter_path_relative, "/", "\\") + "', '')
WHERE CAST([Path] AS VARCHAR(MAX)) LIKE '" +StringHandling.CHANGE(context.parameter_path_relative, "/", "\\") + "%'
