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

At 2:23 AM +1100 3/23/08, Stuart Morris 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.

DESC is a reserved word.

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

You should quote it as `desc`, as described in

http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

But you'll need to make sure that Perl doesn't interpret the ` characters
itself.


>
>(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!
>
>Please advise if you need the entire script and I'll post it.  Thanks in
>advance for your help.
>
>Regards,
>Stuart.


-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

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