Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Re: error calling MySQL from Perl

by gmax (Abbot)
on Mar 16, 2003 at 19:45 UTC ( #243518=note: print w/replies, xml ) Need Help??

in reply to error calling MySQL from Perl

Apart from the syntax problem, MySQL versions earlier than 4.1 (which is far from stable at the moment) do not support subqueries.

Here's a workaround to such limitation.

#!/usr/bin/perl -w use strict; use DBI; my ($dbh, $rows); $dbh = DBI->connect('dbi:mysql:test', 'myusername', 'mypassword', {RaiseError=>1}) || die "Error opening database: $DBI::errstr\n"; # # First, get destination from your table # my ($destination) = @{$dbh->selectcol_arrayref(qq{ SELECT destination FROM checkin WHERE firstname='Henry' AND lastname='Rollins'} )} # || die "destination not found\n"; # wrong or die "destination not found\n"; # correct # # Notice that the script fails if destination is not found. # # # then, use $destination to update the table # $rows = $dbh->do(qq{UPDATE checkin SET destination = '$destination' WHERE firstname='Bill' AND lastname='Gates'}) || die "Couldn't update record : $DBI::errstr"; print "$rows row(s) updated in checkin"; $dbh->disconnect || die "Failed to disconnect\n";

A few comments:

  • You are using UPDATE and then printing "rows added."
    Be aware that the UPDATE statement modifies an existing record. It does not add rows.
  • What you are doing with your SQL statements is taking one column (destination) from a row identified by name and surname, and then inserting such column into another record identified by another name/surname pair.
    Make sure that this is really what you want to do.
  • You are using a MySQL installation with "root" user and no password.
    This is a huge security risk. Please fix it as fast as you can.

update - Fixed a small bug in the script.

_ _ _ _ (_|| | |(_|>< _|

Replies are listed 'Best First'.
Re: Re: error calling MySQL from Perl
by mooseboy (Pilgrim) on Mar 16, 2003 at 20:23 UTC

    Thanks for the workaround and the tips -- I tested it but now Bill is flying off to the mysterious destination "1"?! I only installed MySQL yesterday and got so engrossed I forgot to take the most elementary security precautions. Duh!

    Update: tested gmax's code again incorporating small bugfix -- now works perfectly! Looks like I'll have to fork out for a copy of O'Reilly's Programming the Perl DBI. Cheers to all monks for their helpful suggestions.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://243518]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (1)
As of 2021-08-03 05:48 GMT
Find Nodes?
    Voting Booth?
    My primary motivation for participating at PerlMonks is: (Choices in context)

    Results (32 votes). Check out past polls.