Re: Problems getting UPDATE statement to work for Perl import script

Stuart,

On Sat, Mar 22, 2008 at 11:23 AM, Stuart Morris <[email protected]> wrote:
> Hi there,
>
>  I came across a Perl script recently to import captions from a Menalto
>  Gallery 2 database to a zenphoto database. I've gone through and made some
>  changes to do more checks to ensure the correct record is being updated and
>  to import more data, however when I run the UPDATE query, I am receiving the
>  following error on the first record:
>
>  DBD::mysql::db do failed: You have an error in your SQL syntax; check the
>  manual that corresponds to your MySQL server version for the right syntax to
>  use near 'desc = 'A couple of people from our backpackers on St Pat\'s Day
>  at ./g2-to-zenphoto-desc-importer.pl line 194.
>  DBD::mysql::db do failed: You have an error in your SQL syntax; check the
>  manual that corresponds to your MySQL server version for the right syntax to
>  use near 'desc = 'A couple of people from our backpackers on St Pat\'s Day
>  at ./g2-to-zenphoto-desc-importer.pl line 194.
>
>  (yes, it's repeated twice in the console)
>
>  The part of the code related to the query is:
>
>  use DBI();
>  $dbh_g2 = DBI->connect("DBI:mysql:database=$gallery2_db;host=$host",
>  "$user", "$password", {'RaiseError' => 1});
>  $dbh_zp = DBI->connect("DBI:mysql:database=$zenphoto_db;host=$host",
>  "$user", "$password", {'RaiseError' => 1});
>  $statement = "UPDATE zp_images SET title = $title, desc = $description, tags
>  = $keywords WHERE zp_images.filename = $filenm AND zp_images.albumid =
>  $zen_albumid;";
>  $dbh_zp->do("$statement");
>
>  Which produces the following SQL statement for the record causing the error:
>
>  UPDATE zp_images SET title = '01', desc = 'A couple of people from our
>  backpackers on St Pat\'s Day.', tags = 'St Pat\'s Day' WHERE
>  zp_images.filename = '01.jpg' AND zp_images.albumid = 3;
>
>  If I only update title (and take out the part to update desc and tags), it
>  works fine.  If I try to only update desc or tags, I get the above error.
>  I've checked the SQL documentation; there's nothing to say you can't update
>  more than one field in a given query.  Any idea on what may be causing this?
>  I am at a loss!

The desc field needs to be quoted as an identifier, because it is a
keyword.  Use backticks: `desc`

Whoever wrote the script needs to be flogged with a prepared
statement.  Using string interpolation to create SQL statements is
bad, bad, bad...

Baron

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]