Re: Perl DBI to MySQL LOAD

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);
[download]

Hope this helps!

The way forward always starts with a minimal test.

Comment on Re: Perl DBI to MySQL LOAD_FILE Download Code

Replies are listed 'Best First'.
Re^2: Perl DBI to MySQL LOAD_FILE by Hammer2001 (Novice) on Jan 05, 2017 at 01:36 UTC
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';` [download] Maybe god exists afterall...	[reply] [d/l]
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 +';";` [download] 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 ); }` [download] Hope this helps! The way forward always starts with a minimal test.	[reply] [d/l] [select]
Re^4: Perl DBI to MySQL LOAD_FILE by Marshall (Canon) on Jan 05, 2017 at 03:46 UTC
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");` [download]	[reply] [d/l]