Escape single quotes in sql variables
The SQL task updates a record in the database with a statement like:
update foo
set ProcessedDate= current_timestamp
where FirstName='${myFirstName[2]}'
The variable ${myFirstName[2]} contains a value like De'Vonte.
The single quote in the value is causing the package to fail. How do I escape the single quote?
- Support Specialist
- Posts: 592
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
You could replace with something like '-X-', then go back after the insert to replace the '-X-' with a '
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
Basically I used the replace function in MFT to escape the single quote.
Rick
<project name="Forum post to handle quotes" mainModule="Main" version="2.0" logLevel="verbose">
<description>If the data has quotes, then insert will have a problem. Must escape quote.</description>
<module name="Main">
<setVariable label="Constant: SINGLEQUOTE" name="C_SINGLEQUOTE" value="'" version="2.0" />
<setVariable label="Constant: DOUBLEQUOTE" name="C_DOUBLEQUOTE" value="''" version="2.0" />
<setVariable label="Set vLastName" name="vLastName" value="De'Vonte" version="2.0" />
<sql resourceId="openid-dev" version="1.0">
<query label="SQL Insert">
<statement>Insert into tempchudebug values('${replace(vLastName,C_SINGLEQUOTE,C_DOUBLEQUOTE)}')
</statement>
</query>
</sql>
</module>
</project>