Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Re^2: Perl DBI to MySQL LOAD_FILE

by Hammer2001 (Novice)
on Jan 05, 2017 at 01:36 UTC ( #1178973=note: print w/replies, xml ) Need Help??


in reply to Re: Perl DBI to MySQL LOAD_FILE
in thread Perl DBI to MySQL LOAD_FILE

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...

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

    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.
      Yes, doing the prepare once will increase performance quite a bit.
      Another thing that can make a HUGE difference in performance is running all of the updates (or insertions) as a single transaction. From one of my DB creation programs (million records). I like to make the "work" as a sub framed by BEGIN and COMMIT to make the transaction scope clear:
      $dbh->do("BEGIN"); #a single transaction makes this MUCH faster import_data(); $dbh->do("COMMIT");

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (2)
As of 2023-02-03 00:43 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    I prefer not to run the latest version of Perl because:







    Results (24 votes). Check out past polls.

    Notices?