Perl DBI to MySQL LOAD

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

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

is what bombs. I have checked values of the scalars before SQL execution and they seem fine.

Any Spiritual guidance much appreciated.

Comment on Perl DBI to MySQL LOAD_FILE Select or Download Code

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);` [download] Hope this helps! The way forward always starts with a minimal test.	[reply] [d/l]
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
Re: Perl DBI to MySQL LOAD_FILE by stevieb (Canon) on Jan 05, 2017 at 00:35 UTC
Quick thing I noticed: `'$code';` [download] Single quotes don't interpolate a variable into its value. Try: `"$code";` [download] ...or depending on the real code: `$code;` [download]	[reply] [d/l] [select]