[Israel.pm] DBI

Meir Guttman meir at guttman.co.il
Mon Nov 1 01:53:57 PDT 2010


Dear Shlomi,

I must confess that using a placeholder to avoid quoting problems never
occurred to me. Good to learn new tricks. 

And I think that your answer for my "challenge" example wouldn't work. Many
quoting methods failed with quoting of the "xxx" in the "OPTIONALLY ENCLOSED
BY XXX" part. My solution, after much experimentation and grief, was as
follows:

 

      my $sth = $dbh->do (

         "LOAD DATA INFILE '$LDI_file_name' "

        ."REPLACE INTO TABLE $tables "

        ."COLUMNS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY ". qq|"'"| . "
ESCAPED BY '\\\\' "

        ."LINES TERMINATED BY '\r\n\' "

      );

 

I could of course use placeholders for the LDI filename and the Table name,
but this is not the issue. The issue of this whole thread is quoting. And my
example was meant to show one problem, the one I solved with the qq|"'"|
construct.

 

Regards,
Meir

 

-----Original Message-----
From: Shlomi Fish [mailto:shlomif at iglu.org.il] 
Sent: Monday, November 01, 2010 10:30 AM
To: perl at perl.org.il
Cc: Meir Guttman
Subject: Re: [Israel.pm] DBI

 

On Sunday 31 October 2010 14:42:17 Meir Guttman wrote:

 

[snip,snip]

 

I don't see why this query requires placeholders or passing data in the
first 

place. A here-document will do fine in this case. And you may be able to
say:

 

[query]

LOAD DATA INFILE ?

 

INTO TABLE tbl_name

 

COLUMNS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY "'" ESCAPED BY '\\'

 

LINES TERMINATED BY '\r\n'

[/query]

 

Assuming you want to mutate INFILE.

 

Furthermore I was talking about the general case - I don't rule out that
there 

are exceptions (including this MySQL specific one), but we should recommend 

people to use placeholders instead of $dbh->quote normally.

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.perl.org.il/pipermail/perl/attachments/20101101/6232a2a3/attachment.htm 


More information about the Perl mailing list