Escape single quotes in sql variables

[rickc]

I have a project with a SQL task.

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_Rick]

You will need to double-up on quotes before you insert, or replace them as you insert.

You could replace with something like '-X-', then go back after the insert to replace the '-X-' with a '

[rickc]

I mocked up a project that illustrates how I worked through this problem.
Basically I used the replace function in MFT to escape the single quote.

Rick

Code: Select all

---

<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="&apos;" version="2.0" />


      <setVariable label="Constant: DOUBLEQUOTE" name="C_DOUBLEQUOTE" value="&apos;&apos;" version="2.0" />


      <setVariable label="Set vLastName" name="vLastName" value="De&apos;Vonte" version="2.0" />


      <sql resourceId="openid-dev" version="1.0">
         <query label="SQL Insert">
            <statement>Insert into tempchudebug values(&apos;${replace(vLastName,C_SINGLEQUOTE,C_DOUBLEQUOTE)}&apos;)


</statement>
         </query>
      </sql>

   </module>

</project>