Results 1 to 2 of 2

Thread: Problems getting UPDATE statement to work for import script

  1. #1
    Join Date
    Nov 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Problems getting UPDATE statement to work for import script

    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):
    Code:
    ~/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.

    Code:
    #!/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.

  2. #2
    Join Date
    Nov 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

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

    Code:
    ~/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:

    Code:
    #!/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

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •