Escape single quotes in sql variables
Post any question you may have in regards to GoAnywhere MFT and let our talented support staff and other users assist you.
If you need a quicker response, please create a support ticket via the customer portal my.goanywhere.com or contact our support team by email at [email protected].
If you need a quicker response, please create a support ticket via the customer portal my.goanywhere.com or contact our support team by email at [email protected].
3 posts
Page 1 of 1
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?
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: 590
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
-
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 '
You could replace with something like '-X-', then go back after the insert to replace the '-X-' with a '
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
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
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="'" 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>
3 posts
Page 1 of 1