Merlin83 has asked for the wisdom of the Perl Monks concerning the following question:

I'm comfortable with DBI. I use it all the time. On local machines. The time has come to use DBI to update a database on a remote machine, and as far as I can tell it should really be working. Obviously it doesn't, otherwise it'd be no fun! Here's the code: 
my $dbh = DBI->connect('DBI:mysql:psa', 'admin', 'XXXXX')
       or die "Couldn't connect to database: " . DBI->errstr;
my $sthGetBandwidth = $dbh->prepare('SELECT domains.name, hosting.real
+_traffic/1024/1024 FROM domains, hosting WHERE hosting.dom_id=domains
+.id LIMIT 10')
       or die "Couldn't prepare to get bandwidth: " . $dbh->errstr;

my $remotedbh = DBI->connect('DBI:mysql:all34SP:<IP address>, 'server'
+, 'XXXXX')
        or die "Couldn't connect to remote database: " . DBI->errstr;
my $sthUpdateBandwidth = $remotedbh->prepare('UPDATE users SET bandwid
+th=? WHERE address LIKE ?')
        or die "Couldn't prepare to update bandwidth: " . $remotedbh->
+errstr;
my $sthGetOldBandwidth = $remotedbh->prepare('SELECT bandwidth FROM us
+ers WHERE address LIKE ?')
        or die "Couldn't prepare to get old bw: " . $remotedbh->errstr
+;


$sthGetBandwidth->execute or die "Couldn't get bandwidth: " . $sthGetB
+andwidth->errstr;

my @data;
while (@data = $sthGetBandwidth->fetchrow_array()) {
        $sthGetOldBandwidth->execute($data[0]);
        unless ($sthGetOldBandwidth->rows == 0) {
                my @oldbw = $sthGetOldBandwidth->fetchrow_array();
                my $newbw = sprintf '%.2f', $data[1];
                print "Setting bandwidth for $data[0] to $newbw (was $
+oldbw[0])\n";
                $sthUpdateBandwidth->execute($data[0], $newbw)
                        or die "Couldn't update bandwidth for $data[0]
+: " . $sthUpdateBandwidth->errstr;
        }
}

[download]
Those of you in the know might recognize what I'm trying to do. I'm pulling bandwidth usage information from a database on the local machine and putting it into a database on a remote machine. Here's the output: 
Setting bandwidth for napiersnowsports.com to 3.92 (was 0.00)
Setting bandwidth for pc-helper.net to 6.02 (was 0.00)
Setting bandwidth for leemajors.co.uk to 144.66 (was 0.00)
Setting bandwidth for kamonohashi.net to 0.00 (was 0.00)
Setting bandwidth for smszone.net to 1.35 (was 0.00)
Setting bandwidth for ukundergraduate.com to 0.31 (was 0.00)
Setting bandwidth for win2000b.34SP.com to 0.07 (was 0.00)
Setting bandwidth for mettacattery.org to 8.30 (was 0.00)
Setting bandwidth for interlink-wireless.com to 2.57 (was 0.00)

[download]
And on a second running: 
Setting bandwidth for napiersnowsports.com to 3.92 (was 0.00)
Setting bandwidth for pc-helper.net to 6.02 (was 0.00)
Setting bandwidth for leemajors.co.uk to 144.66 (was 0.00)
Setting bandwidth for kamonohashi.net to 0.00 (was 0.00)
Setting bandwidth for smszone.net to 1.35 (was 0.00)
Setting bandwidth for ukundergraduate.com to 0.31 (was 0.00)
Setting bandwidth for win2000b.34SP.com to 0.07 (was 0.00)
Setting bandwidth for mettacattery.org to 8.30 (was 0.00)
Setting bandwidth for interlink-wireless.com to 2.57 (was 0.00)

[download]
So basically it's not getting updated. The zeroes for previous bandwidth are correct. If I change the value in the db, it does change there. And now to the question: Why isn't it updated?

Replies are listed 'Best First'.
Re: DBI UPDATE statement failing
by perrin (Chancellor) on Dec 14, 2001 at 22:42 UTC
    $sthUpdateBandwidth->execute($data[0], $newbw)
    Shouldn't that be
    $sthUpdateBandwidth->execute($newbw, $data[0])
    instead?

    Also, if you really want to use LIKE, you should put the value to be searched inside of %, so it will do a "LIKE '%foo%'" search.

[reply]
[d/l]
[select]
      Yep, as andye has pointed out, I had the parameters the wrong way round. In this use of LIKE, I'm simply using to to remove case-sensitivity, not for wildcard use.
[reply]
        It's usually faster and more obvious to do case-insensitivity by converting both to lowercase. You can use lc() in perl and lower() in most SQL databases. If you do this all the time and it performs badly, you might want to investigate building an index for the lowercased version of the column.
[reply]
Re: DBI UPDATE statement failing
by cfreak (Chaplain) on Dec 15, 2001 at 00:40 UTC
    You first execute statement could be failing: 
    $sthGetOldBandwidth->execute($data[0]);

    [download]

    Put 'or die $sth->errstr()' after that one (like you have after the second one) and it might give you an idea of the problem. The second one probably isn't failing because you just have NULL returning from the first one (if I understand your code correctly).

    Off the top of my head a possible problem, if this works from local machines but not on remote, the remote database may not be allowing connection from your machine, mysql databases tend to check for certain hosts as well as the username and password, depending on how the admin set them up.

    Hope that helps
    Chris
[reply]
[d/l]
Re: DBI UPDATE statement failing
by andye (Curate) on Dec 14, 2001 at 22:18 UTC
    Could the problem be to do with rows() ?

    From http://search.cpan.org/doc/TIMB/DBI-1.20/DBI.pm: Generally, you can only rely on a row count after a non-SELECT execute (for some specific operations like UPDATE and DELETE), or after fetching all the rows of a SELECT statement.

    Maybe you should SELECT COUNT etc instead.

    andy.

[reply]
[d/l]
[select]
      The rows check is only so sites that aren't in the remote database don't mess up the script. The fact that it says what it's going to do correctly with the print statement says to me that the problem lies elsewhere.
[reply]
        You're right, of course.

        But aren't these parameters the wrong way round? 

        $sthUpdateBandwidth->execute($data[0], $newbw)

        [download]

        andy.

[reply]
[d/l]

Back to Seekers of Perl Wisdom