Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

DBI and some complicated quotes

by Cody Fendant (Pilgrim)
on Sep 21, 2012 at 04:04 UTC ( #994803=perlquestion: print w/ replies, xml ) Need Help??
Cody Fendant has asked for the wisdom of the Perl Monks concerning the following question:

I want to replace some things in MySQL fields.

Unfortunately those things are quotes.

So I have a statement like this:

UPDATE table_name SET `fieldname` = REPLACE(`fieldname`,'','''')

which replaces a "smart quote" type apostrophe with a regular one (MySQL's way of escaping a quote is to double it).

That statement you can see above works from the command line, but not if I use DBI's do() function.

Any suggestions? Any way in which I can pass in quotes without having problems with DBI quoting the quotes around the quotes?

Any other way to attack this problem?

Comment on DBI and some complicated quotes
Select or Download Code
Re: DBI and some complicated quotes
by davido (Archbishop) on Sep 21, 2012 at 04:13 UTC

    You need to look at the section in the DBI documentation called "Placeholders and Bind Values"

    my $sth = $dbh->prepare( 'UPDATE table_name SET `fieldname` = REPLACE( + `fieldname`, ?, ? )' ); $sth->execute( q{}, q{'} );

    ...this assuming you're correct when you say it works from the command line. You may have to check your database-specific DBD::* POD to see if there are any particulars you need to know.


    Dave

      I think you really meant...

      $sth->execute( q{}, q{'} );
      perl -E'sub Monkey::do{say$_,for@_,do{($monkey=[caller(0)]->[3])=~s{::}{ }and$monkey}}"Monkey say"->Monkey::do'

        Oh....., I suppose. ;) (Updated now)


        Dave

Re: DBI and some complicated quotes
by tobyink (Abbot) on Sep 21, 2012 at 08:56 UTC

    "MySQL's way of escaping a quote is to double it."

    Actually, that's not something specific to MySQL; it's part of the SQL standard.

    davido's answer is a good one. In the spirit of "there's more than one way to do it"...

    $dbh->do(<<'GO'); UPDATE table_name SET `fieldname`=REPLACE(`fieldname`,'','''') GO

    ... or ...

    $dbh->do("UPDATE table_name SET `fieldname`=REPLACE(`fieldname`,'','' +'')");

    ... or ...

    $dbh->do('UPDATE table_name SET `fieldname`=REPLACE(`fieldname`,\'\', +\'\'\'\')');

    ... or ...

    $dbh->do(q(UPDATE table_name SET `fieldname`=REPLACE(`fieldname`,'',' +''')));

    Lastly, if you've got non-ASCII characters in your source code (such as your "smart quote"), make sure your editor is set up to save your script as UTF-8; and make sure you include the pragma use utf8 somewhere near the top of your script.

    perl -E'sub Monkey::do{say$_,for@_,do{($monkey=[caller(0)]->[3])=~s{::}{ }and$monkey}}"Monkey say"->Monkey::do'

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://994803]
Approved by GrandFather
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (13)
As of 2014-09-18 17:21 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (120 votes), past polls