Solved

PERL Roth ODBC Escape Single Quote MSAccess

Posted on 2011-02-27
4
573 Views
Last Modified: 2012-08-14
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
Comment
Question by:jchauncey60
  • 2
  • 2
4 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 34993434
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
 

Author Comment

by:jchauncey60
ID: 34993810
Thanks for the quick feedback.  That doesn't get me completely there.  There is some trick with the Roth modules and Perl.
0
 

Author Comment

by:jchauncey60
ID: 34993889
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
 
LVL 84
ID: 34996221
Yes - that was my suggestion to you:

"Access expects you to include 2 single quotes"

0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question