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

Hello there! I'm quite confused. I've read many tutorials about variable scope but it seems I still don't understand: I have script like this: 
use strict;
use warnings;
...
sub write_status($$$$) {
  my $dbh = DBI->connect("DBI:mysql:mysql_socket=" ..... );
  ...
  my $result = $dbh->begin_work();
  ...
  $result = $dbh->do("UPDATE results ....");
  ...
  $result = $dbh->commit();
}
sub check_apps($\@) {
  my $dbh = DBI->connect("DBI:mysql:mysql_socket=" ..... );
  ...
  my $sth = $dbh->prepare("SELECT * FROM ....");
  ...
  if (! defined $sth->execute()) { ... }
  ...
  write_status($host, $app, $status, $version);
}

my $dbh = DBI->connect("DBI:mysql:mysql_socket=" ..... );
...
my $sth = $dbh->prepare("SELECT * FROM ....");
...
...
check_apps($host, @processes);

[download]
Its behavior is weird, some db rows get updated, some not. But if I change the $dbh variable names, so that they are different, it works perfectly: 
use strict;
use warnings;
...
sub write_status($$$$) {
  my $dbh_write = DBI->connect("DBI:mysql:mysql_socket=" ..... );
  ...
  my $result = $dbh_write->begin_work();
  ...
  $result = $dbh_write->do("UPDATE results ....");
  ...
  $result = $dbh_write->commit();
}
sub check_apps($\@) {
  my $dbh_apps = DBI->connect("DBI:mysql:mysql_socket=" ..... );
  ...
  my $sth = $dbh_apps->prepare("SELECT * FROM ....");
  ...
  if (! defined $sth->execute()) { ... }
  ...
  write_status($host, $app, $status, $version);
}

my $dbh_main = DBI->connect("DBI:mysql:mysql_socket=" ..... );
...
my $sth = $dbh_main->prepare("SELECT * FROM ....");
...
...
check_apps($host, @processes);

[download]
I thought that when I declare a variable in a subroutine with "my", any references to that variable within the subroutine point to the "local" one and all other variables with the same name defined elsewhere are invisible within that subroutine and thus stay untouched by the subroutine. Anyone capable of explaining it to me? :-)

Replies are listed 'Best First'.
Re: Variable scope & subroutines
by kennethk (Abbot) on Apr 12, 2011 at 14:43 UTC
    Your summary of my functionality is roughly correct - the docs discuss it in Private Variables via my(). The two code examples you have posted should be functionally equivalent.

    When I hear about issues like this, the conclusion I draw is that you have created a closure through a typographical error. You should be able to track this down using -c and strict by changing the name of the top-level variable. If you post a less sanitized version, we should be able to help debug.

[reply]
Re: Variable scope & subroutines
by ikegami (Patriarch) on Apr 12, 2011 at 14:38 UTC
    You are right, There are no differences between those two snippets.
[reply]
Re: Variable scope & subroutines
by InfiniteSilence (Curate) on Apr 12, 2011 at 15:20 UTC

    The two code examples you have posted should be functionally equivalent.

    Yep. So that leaves one possibility that there might be something implemented in either your code (as mentioned) or deep in the DBI code that is causing your problem. However, by looking at your prodigious use of DBI->connect I have the ask: why don't you just create a single database handle and simple reuse it? Last time I checked the database handle does not dictate read/write/update/delete access to the database.

    Celebrate Intellectual Diversity

[reply]
[d/l]

      Last time I checked the database handle does not dictate read/write/update/delete access to the database.

      The handle results from connecting as a user, so it definitely does. That said, I don't see how this relates.

[reply]
        The code shown has a different function for different operations like update, insert, etc. Each function creates another database handle inside of the function rather than accepting the handle as an argument.

        Celebrate Intellectual Diversity

[reply]

      Thank you, I finally found the culprit, it is really DBI (mysql).

      This workaround made my program working:

      The beginning of my subroutine:

      sub write_status($$$$) {
  my $host = $_[0];
  my $app = $_[1];
  my $status = $_[2];
  my $version = $_[3];
  my $result = $dbh->begin_work();
  if (! defined $result) { $result = -1; }
  if ($result < 0) {
    print STDERR "\n" . $0 . ": CHYBA: " . $dbh->errstr . "\n";
    return ISHN_ERR_INTERNAL;
  }

      [download]
      I needed to add this dummy UPDATE to somehow reset something (without it only few or none rows were updated): 
        my $statement = q{UPDATE results
                    SET status = ?, verze = 'N/A'
                    WHERE host = ? AND app = ?;};
  $result = $dbh->do($statement, undef, $status, $host, $app);
  if (! defined $result) { $result = -1; }
  if ($result <= 0) {
    print STDERR "\n" . $0 . ": CHYBA: " . $dbh->errstr . "\n";
    $dbh->rollback();
    return ISHN_ERR_INTERNAL;
  }

      [download]
      This is the rest of my subroutine: 
        $statement = q{UPDATE results
                 SET status = ?, verze = ?
                 WHERE host = ? AND app = ?;};
  $result = $dbh->do($statement, undef, $status, $version, $host, $app
+);
  if (! defined $result) { $result = -1; }
  if ($result <= 0) {
    print STDERR "\n" . $0 . ": CHYBA: " . $dbh->errstr . "\n";
    $dbh->rollback();
    return ISHN_ERR_INTERNAL;
  }
  $statement = q{INSERT INTO history
                 (host, app, status, verze)
                 VALUES (?, ?, ?, ?);};
  $result = $dbh->do($statement, undef, $host, $app, $status, $version
+);
  if (! defined $result) { $result = -1; }
  if ($result <= 0) {
    print STDERR "\n" . $0 . ": CHYBA: " . $dbh->errstr . "\n";
    $dbh->rollback();
    return ISHN_ERR_INTERNAL;
  }
  $result = $dbh->commit();
  if (! defined $result) { $result = -1; }
  if ($result < 0) {
    print STDERR "\n" . $0 . ": CHYBA: " . $dbh->errstr . "\n";
    $dbh->rollback();
    return ISHN_ERR_INTERNAL;
  } else { return ISHN_OK; }
}

      [download]
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom