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

Stuart,

Looking at your first example, it appears you have two place-holders in your prepare, but pass only one value to execute. I would suspect that is your problem.


$zenitem_cursor = $dbh_zp->prepare("
  SELECT zp_images.filename,zp_images.albumid, zp_albums.title 
  FROM zp_images, zp_albums 
  WHERE zp_images.filename = ? 
   AND zp_images.albumid = zp_albums.id 
   AND zp_albums.title = ? LIMIT 1;") || die "Prepare error ($DBI::errstr)";

$zenitem_cursor->execute($album_title) || die "Query error ($DBI::errstr)";

You need something of the sort

$zenitem_cursor->execute($filename, $album_title) || die "Query error ($DBI::errstr)";

--Patrick



Stuart Morris wrote:

>Hi again,
>
>
>Hi Stuart,
>  
>
>># Bad:
>>$user = $dbh->selectrow_hashref("select * from users where id = $id");
>>
>># Good:
>>$sth = $dbh->prepare("select * from users where id = ?");
>>$sth->execute($id);
>>$user = $sth->fetchrow_hashref();
>>
>>This approach avoids all manner of performance and security problems,
>>not to mention bugs :-)  It would not have saved you from the syntax
>>error, but I just wanted to mention it because it's a common mistake.
>>
>>Cheers
>>Baron
>>    
>>
>
>
>
>
>I am trying to implement the method in Baron's email above to run a SQL
>statement, however I am getting some unusual results.
>
>For example, this doesn't work:
>
>use DBI();
>$dbh_g2 = DBI->connect("DBI:mysql:database=$gallery2_db;host=$host",
>"$user", "$password", {'RaiseError' => 1, Taint => 1});
>$dbh_zp = DBI->connect("DBI:mysql:database=$zenphoto_db;host=$host",
>"$user", "$password", {'RaiseError' => 1, Taint => 1});
>$zenitem_cursor = $dbh_zp->prepare("SELECT zp_images.filename,
>zp_images.albumid, zp_albums.title FROM zp_images, zp_albums WHERE
>zp_images.filename = ? AND zp_images.albumid = zp_albums.id AND
>zp_albums.title = ? LIMIT 1;") || die "Prepare error ($DBI::errstr)";
>$zenitem_cursor->execute($album_title) || die "Query error ($DBI::errstr)";
>$update_statement = $dbh_zp->prepare("UPDATE zp_images SET title = ?, `desc`
>= ?, tags = ? WHERE zp_images.filename = ? AND zp_images.albumid = ?;") ||
>die "Prepare error ($DBI::errstr)";
>$update_statement->execute($title, $description, $keywords, $filenm,
>$zen_albumid) || die "Query error ($DBI::errstr)";
>
>However, this does work:
>
>use DBI();
>$dbh_g2 = DBI->connect("DBI:mysql:database=$gallery2_db;host=$host",
>"$user", "$password", {'RaiseError' => 1, Taint => 1});
>$dbh_zp = DBI->connect("DBI:mysql:database=$zenphoto_db;host=$host",
>"$user", "$password", {'RaiseError' => 1, Taint => 1});
>$zenitem_cursor = $dbh_zp->prepare("SELECT zp_images.filename,
>zp_images.albumid, zp_albums.title FROM zp_images, zp_albums WHERE
>zp_images.filename = $filenm AND zp_images.albumid = zp_albums.id AND
>zp_albums.title = $album_title;") || die "Prepare error ($DBI::errstr)";
>$zenitem_cursor->execute() || die "Query error ($DBI::errstr)";
>$update_statement = $dbh_zp->prepare("UPDATE zp_images SET title = $title,
>`desc` = $description, tags = $keywords WHERE zp_images.filename = $filenm
>AND zp_images.albumid = $zen_albumid;") || die "Prepare error
>($DBI::errstr)";
>$update_statement->execute() || die "Query error ($DBI::errstr)";
>
>The $zenitem_cursor... statement in the first example finds no match,
>however I can run the exact same query in the database and get a result.
>The $update_statement... statement in the first example runs okay, but
>doesn't actually update any records.  No error is given, so it seems they
>should work okay.  The statements only do what they're meant to in the
>second example.  Have I missed something?  It seems that the only difference
>is whether the variables are inserted directly into the SQL statement, or
>are later substituted in place of the ? placeholder.
>
>Thanks for your help.
>
>Regards,
>Stuart.
>
>
>
>On Sun, Mar 23, 2008 at 10:33 AM, Baron Schwartz <[email protected]> wrote:
>
>  
>
>>Hi Stuart,
>>
>># Bad:
>>$user = $dbh->selectrow_hashref("select * from users where id = $id");
>>
>># Good:
>>$sth = $dbh->prepare("select * from users where id = ?");
>>$sth->execute($id);
>>$user = $sth->fetchrow_hashref();
>>
>>This approach avoids all manner of performance and security problems,
>>not to mention bugs :-)  It would not have saved you from the syntax
>>error, but I just wanted to mention it because it's a common mistake.
>>
>>Cheers
>>Baron
>>
>>On Sat, Mar 22, 2008 at 7:29 PM, Stuart Morris <[email protected]> wrote:
>>    
>>
>>>Hi Paul and Baron,
>>>
>>>Thanks for the tip.. completely forgot about desc being a reserved word.
>>>
>>>Regarding the string interpolation, I'm not too sure what you mean by
>>>      
>>>
>>that
>>    
>>
>>>sorry, but I'm all ears if you can suggest any improvements. :)
>>>
>>>Regards,
>>>Stuart.
>>>
>>>
>>>On Sun, Mar 23, 2008 at 2:40 AM, Baron Schwartz <[email protected]> wrote:
>>>      
>>>
>>>>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
>>>>
>>>>        
>>>>
>>>      
>>>
>
>  
>


-- 
Patrick Galbraith, Senior Programmer 
Grazr - Easy feed grazing and sharing
http://www.grazr.com 

Satyam Eva Jayate - Truth Alone Triumphs
Mundaka Upanishad




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