Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

Perl DBI to MySQL LOAD_FILE

by Hammer2001 (Novice)
on Jan 05, 2017 at 00:10 UTC ( #1178969=perlquestion: print w/replies, xml ) Need Help??

Hammer2001 has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to use MySQL's LOAD_FILE() function to SQL UPDATE existing records in a table. I am getting a SQL syntax error I suspect because the file contains, single and double quotes as well as slashes. I have also tried reading the file in binary mode using Perl's binmode into a scalar and using MySQL UPDATE....SET, but that also bombs when the SQL is executed with a syntax error.

I am at a loss, as I know INSERT will also be a pain since the data contains quotes which are needed as field separators. Anyone have any ideas on other approaches I might try?

The actual SQL:

UPDATE artists SET page=LOAD_FILE('/tmp/left.txt') where code='aname';
is successful when executed from mysql client, so I know the MySQL requirements are met.

In Perl:

UPDATE artistsfull SET page=LOAD_FILE($infile) where code='$code';
is what bombs. I have checked values of the scalars before SQL execution and they seem fine.

Any Spiritual guidance much appreciated.

Replies are listed 'Best First'.
Re: Perl DBI to MySQL LOAD_FILE
by 1nickt (Canon) on Jan 05, 2017 at 00:45 UTC

    You don't make it easy to help, since you do not share the content of the file, nor the error message, nor your full DBI statement, but it sure looks like you are not using placeholders and bind values. This is what's getting you into the quoting problem addressed by stevieb.

    Try something like:

    my $res = $dbh->do('UPDATE artistsfull SET page=LOAD_FILE( ? ) where c +ode= ?', {}, $infile, $code);

    Hope this helps!


    The way forward always starts with a minimal test.
      Thank you both for your quick responses. It was indeed an interpolation problem. Need the scalar with file name single quoted:
      UPDATE artists SET page=LOAD_FILE('$infile') where code='$code';
      Maybe god exists afterall...

        It's cool that your program works now. Presumably, since the solution was to put $infile inside single quotes, your SQL is inside double quotes not shown in your OP, as in

        $sql = "UPDATE artists SET page=LOAD_FILE('$infile') where code='$code +';";
        I may be wrong about that, but if so, you really should consider using placeholders. As soon as your data value contains an embedded apostrophe your SQL will break. Then you'll find yourself using backslashes, or maybe DBI's quote() ... it's much better practise and allows your code to be much more flexible to use bind values almost always. As I showed above you can even do so with do().

        And if you are working with a list of values to be inserted, as you say, using placeholders will make your process much more efficient since the query can be prepared just once outside the loop. Assuming you had a hash of files keyed by code:

        my $sth = $dbh->prepare('UPDATE artists SET page=LOAD_FILE(?) WHERE co +de=?'); for my $code ( keys %hash ) { $sth->execute( $hash{$code}, $code ); }

        Hope this helps!


        The way forward always starts with a minimal test.
Re: Perl DBI to MySQL LOAD_FILE
by stevieb (Canon) on Jan 05, 2017 at 00:35 UTC

    Quick thing I noticed:

    '$code';

    Single quotes don't interpolate a variable into its value. Try:

    "$code";

    ...or depending on the real code:

    $code;

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (3)
As of 2023-01-27 17:32 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?