Problems getting UPDATE statement to work for import script

[shehaal]

Hi there,

I found a script online that imports descriptions from a Gallery2 database to a zenphoto database. I've tried to modify this to bring across some extra info, however when I run it in the console, the following error is displayed (yes, the error is repeated twice in the console):

~/scripts$ ./g2-to-zenphoto-desc-importer.pl
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 = 'This is the main page of your Gallery', tags = NULL WHER at ./g2-to-zenphoto-desc-importer.pl line 54.
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 = 'This is the main page of your Gallery', tags = NULL WHER at ./g2-to-zenphoto-desc-importer.pl line 54.

The script as it stands is below. Any idea why this UPDATE statement won't work? I've gone over it a 1000 times, but can't see what's causing the errors.


#!/usr/bin/perl

##############################################################################
# Define variables

# $host is the hostname of your database, usually just localhost
$host = "localhost";
$user = "me";
$password = "pass";

# $gallery2_db is the name of your Gallery2 database
$gallery2_db = "g2";

# $zenphoto_db is the name of your zenphoto dataabase
$zenphoto_db = "zen";

# $phototitle determines what the zenphoto title will be based upon
# "title" will use the Gallery2 title of a photo
# "summary" will use the Gallery2 summary of a photo
$phototitle = "summary";
##############################################################################

# You shouldn't need to change anything below this line

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});
$counter = 0;
%UMLAUTE = ( 'Ä' => 'Ae', 'Ö' => 'Oe', 'Ü' => 'Ue','ä' => 'ae', 'ö' => 'oe', 'ü' => 'ue', 'ß' => 'ss');
@UMLKEYS = join("|", keys(%UMLAUTE));

$cursor = $dbh_g2->prepare("select g2_FileSystemEntity.g_id, g2_FileSystemEntity.g_pathComponent, g2_Item.g_$phototitle, g2_Item.g_description, g2_Item.g_keywords from g2_FileSystemEntity, g2_Item where g2_FileSystemEntity.g_id = g2_Item.g_id;") || die "Prepare error ($DBI::errstr)";

$cursor->execute() || die "Query error ($DBI::errstr)";
while(defined(my $row = $cursor->fetch)) {
$id = $row->[0];
$filenm = $row->[1];
$summary = $row->[2];
$description = $row->[3];
$keywords = $row->[4];

$summary =~ s/(@UMLKEYS)/$UMLAUTE{$1}/g;
$summary = $dbh_g2->quote($summary);
$description =~ s/(@UMLKEYS)/$UMLAUTE{$1}/g;
$description = $dbh_g2->quote($description);
$keywords =~ s/(@UMLKEYS)/$UMLAUTE{$1}/g;
$keywords = $dbh_g2->quote($keywords);
$filenm = $dbh_g2->quote($filenm);

if ($filename ne NULL) {
my $statement = "UPDATE zp_images SET title = $summary, desc = $description, tags = $keywords WHERE filename = $filenm";
$dbh_zp->do("$statement");
print "$counter: g2 ID $id: $filenm --- $summary --- $description --- $keywords\n";
}
else {
print "$counter: filename is NULL --- NOT INCLUDED!\n";
}

$counter++;
select(undef, undef, undef, 0.2); ## wait 200 ms for the database
}
exit(0);

Thanks for your help,
Stuart.

[shehaal]

Okay, forget the first error; I didn't update one of the variable names. However, I am still getting the following:


~/scripts$ ./g2-to-zenphoto-desc-importer.pl
0: filename is NULL --- NOT INCLUDED!
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 = '', tags = 'Adelaide St Pat\'s Day' WHER at ./g2-to-zenphoto-desc-importer.pl line 54.
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 = '', tags = 'Adelaide St Pat\'s Day' WHER at ./g2-to-zenphoto-desc-importer.pl line 54.

And the script is now:


#!/usr/bin/perl

##############################################################################
# Define variables

# $host is the hostname of your database, usually just localhost
$host = "localhost";
$user = "me";
$password = "pass";

# $gallery2_db is the name of your Gallery2 database
$gallery2_db = "g2";

# $zenphoto_db is the name of your zenphoto dataabase
$zenphoto_db = "zen";

# $phototitle determines what the zenphoto title will be based upon
# "title" will use the Gallery2 title of a photo
# "summary" will use the Gallery2 summary of a photo
$phototitle = "summary";
##############################################################################

# You shouldn't need to change anything below this line

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});
$counter = 0;
%UMLAUTE = ( 'Ä' => 'Ae', 'Ö' => 'Oe', 'Ü' => 'Ue','ä' => 'ae', 'ö' => 'oe', 'ü' => 'ue', 'ß' => 'ss');
@UMLKEYS = join("|", keys(%UMLAUTE));

$cursor = $dbh_g2->prepare("select g2_FileSystemEntity.g_id, g2_FileSystemEntity.g_pathComponent, g2_Item.g_$phototitle, g2_Item.g_description, g2_Item.g_keywords from g2_FileSystemEntity, g2_Item where g2_FileSystemEntity.g_id = g2_Item.g_id;") || die "Prepare error ($DBI::errstr)";

$cursor->execute() || die "Query error ($DBI::errstr)";
while(defined(my $row = $cursor->fetch)) {
$id = $row->[0];
$filenm = $row->[1];
$summary = $row->[2];
$description = $row->[3];
$keywords = $row->[4];

$summary =~ s/(@UMLKEYS)/$UMLAUTE{$1}/g;
$summary = $dbh_g2->quote($summary);
$description =~ s/(@UMLKEYS)/$UMLAUTE{$1}/g;
$description = $dbh_g2->quote($description);
$keywords =~ s/(@UMLKEYS)/$UMLAUTE{$1}/g;
$keywords = $dbh_g2->quote($keywords);
$filenm = $dbh_g2->quote($filenm);

if ($filenm ne NULL) {
my $statement = "UPDATE zp_images SET title = $summary, desc = $description, tags = $keywords WHERE filename = $filenm";
$dbh_zp->do("$statement");
print "$counter: g2 ID $id: $filenm --- $summary --- $description --- $keywords\n";
}
else {
print "$counter: filename is NULL --- NOT INCLUDED!\n";
}

$counter++;
select(undef, undef, undef, 0.2); ## wait 200 ms for the database
}
exit(0);

The record that is causing the error is:
FILENAME=01.jpg
SUMMARY=St Pat's Day.
DESCRIPTION=
KEYWORDS=Adelaide St Pat's Day