• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 650
  • Last Modified:

PERL Roth ODBC Escape Single Quote MSAccess

I have an SQL statement that the value I am inserting / updating in the DB has an embedded single quote (e.g. Let's Grow). I cannot figure out how to escape the string in PERL using Roth's ODBC utilitiles. My backend DB is MS/Access 2007

Any help would be appreciated.
0
jchauncey60
Asked:
jchauncey60
  • 2
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Access expects you to include 2 single quotes:

UPDATE MyTable SET MyField="Let''s Grow"

Note that is [DQ]=[DQ]Let[SQ][SQ]s Grow[DQ]

[DQ]= "
[SQ] = '

I'm not sure how you'll do that in Perl, but that is the syntax Access expects you to use.
0
 
jchauncey60Author Commented:
Thanks for the quick feedback.  That doesn't get me completely there.  There is some trick with the Roth modules and Perl.
0
 
jchauncey60Author Commented:
You got me close to the answer and I figured the rest of it out.

MS/Access wants the values surrounded by single quotes and then you escape the embedded single quote with two single quotes

$value = Let's Grow
$value =~ s/'/''/g;

So in the SQL string it would look like 'Let''s Grow'
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Yes - that was my suggestion to you:

"Access expects you to include 2 single quotes"

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now