DBD problem

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

HJey guys, I am trying to read a db and while reading update specific records. Problem is that the while loop works just fine except when I write into the db the while loop stops withy a fetch array error. Here is my code:

# --------------------------------------------------------------------
+-------------------------------------------
# --------------------------------------------------------------------
+-------------------------------------------
# --------------------------------------------------------------------
+-------------------------------------------
# Update the selected Database record
sub UpdateDb {
    $Dbh = DBI->connect($DbConnectionInfo,$DbUserid,$DbPasswd);
    $Sth = $Dbh->prepare($UpdQuery);
    $Sth->execute() or $ErrNum = "3007"; $ErrMess = "$DBI::errstr";
    $Sth->finish();
    $Dbh->disconnect;
    print "HIER $ErrMess<br>"
    }

# --------------------------------------------------------------------
+-------------------------------------------
# --------------------------------------------------------------------
+-------------------------------------------
# --------------------------------------------------------------------
+-------------------------------------------
# Query the database, and replace the labels with global values
if ($ErrNum eq "0") {
    $RangeQuery = "WHERE $Field LIKE '%$String%' ORDER BY LstNum ASC";
    $Dbh = DBI->connect($DbConnectionInfo,$DbUserid,$DbPasswd);
    $Sth = $Dbh->prepare("SELECT LstNum, LstName31, LstName01, LstSize
+, LstDesc31, LstDesc01, LstPurch, LstSaleP, LstSaleW, LstAmount, LstI
+nfo FROM $DbTable $RangeQuery");
    $Sth->execute or print "$DBI::errstr";
 
    while (($LstNum, $LstName31, $LstName01, $LstSize, $LstDesc31, $Ls
+tDesc01, $LstPurch, $LstSaleP, $LstSaleW, $LstAmount, $LstInfo) = $St
+h->fetchrow_array){        
        if ($Field eq "LstName31") {
            if ($Replace ne "") {
                print "String: $String<br>";
                print "Replace: $Replace<br><br>";
                $LstName31Old             = $LstName31;
                $LstName31                 =~ s/$String/$Replace/i;
                print "Updated $LstNum:<br>Old Value: $LstName31Old<br
+>New Value: $LstName31<br>";
                }
                
            if ($NewValue ne "") {
                print "String: $String<br>";
                print "NewValue: $NewValue<br><br>";
                $LstName31Old             = $LstName31;
                $LstName31                 = $NewValue;
                print "Updated $LstNum:<br>Old Value: $LstName31Old<br
+>New Value: $LstName31<br>";
                }
            
            if (($Replace eq "") and ($NewValue eq "")) {
                print "Current $LstNum:<br>Current Value: LstName31 $L
+stName31<br>";
                }
            $UpdQuery                 = "UPDATE $DbTable SET LstName31
+ = '$LstName31' WHERE LstNum = '$LstNum'";
            UpdateDb();
            }
        }
    $Sth->finish();
    $Dbh->disconnect;
    }
    if (($Replace eq "") and ($NewValue eq "")) {
        print "<br><br>$Mess<br>";
        print "No new values received. No records are updated";
    }

# --------------------------------------------------------------------
+-------------------------------------------
# --------------------------------------------------------------------
+-------------------------------------------
# --------------------------------------------------------------------
+-------------------------------------------
[download]

Comment on DBD problem Download Code

Replies are listed 'Best First'.

Re: DBD problem
by Athanasius (Archbishop) on Jan 16, 2017 at 03:25 UTC

Hello Umdurman,

You call $Dbh = DBI->connect() before the while loop and $Dbh->disconnect after the loop terminates. But within the loop you call UpdateDb() which itself calls $Dbh = DBI->connect (again!) and then calls $Dbh->disconnect before returning. So the next call to $Sth->fetchrow_array in the while loop condition occurs with the db disconnected, resulting in the error message you are seeing.

This is an excellent illustration of the problem with global variables: action at a distance, making it difficult to debug. If you pass the $Dbh variable into sub UpdateDb explicitly, you will easily see that the calls to connect and disconnect within sub UpdateDb are unnecessary, as the db connection is already open, and must remain so when the subroutine returns.

Hope that helps,

Athanasius <°(((>< contra mundum Iustus alius egestas vitae, eros Piratica,

[reply]
[d/l]
[select]

Re^2: DBD problem

by Umdurman (Acolyte) on Jan 16, 2017 at 14:56 UTC

Hey Athanasius, thank you, I was expecting such a problem. I tried to disable the connect and the disconnect in the sub before I looked for help here but that didnt do the job. I should be able to write to the db from withinn the while loop. I know I did this before... Could you give a hint please? Kind regards, Ton

[reply]

Re^3: DBD problem

by Athanasius (Archbishop) on Jan 17, 2017 at 03:06 UTC

I tried to disable the connect and the disconnect in the sub before I looked for help here but that didnt do the job.

Looking again at sub UpdateDb, I see you have the same problem with the $Sth variable: you call prepare and finish on it, so, when the subroutine returns, the call to $Sth->fetchrow_array in the while loop is no longer valid. Here’s a suggested rewrite of the subroutine (untested):

# Update the selected Database record

sub UpdateDb
{
    my ($dbh, $upd_query) = @_;             # $dbh is already connecte
+d

    my $sth = $dbh->prepare($upd_query);    # create $sth as a local, 
+lexically-scoped variable unrelated to the global $Sth
    $sth->execute() or $ErrNum = '3007';
    $ErrMess = $DBI::errstr;
    $sth->finish();
    print "HIER $ErrMess<br>"
}
[download]

You would call it like this: UpdateDb($Dbh, $UpdQuery);

This is still far from ideal, as $ErrNum and $ErrMess are still being used as (effectively) global variables¹. But localising $sth, by declaring it within the scope of the UpdateDb subroutine, will allow the call to $Sth->fetchrow_array in the while loop condition to work correctly.

¹Strictly speaking, the only true global variables in Perl are the built-ins, such as $| and $/. Variables either declared with our or used without being declared (in the absence of use strict 'vars';) are package globals. Variables declared with my are lexicals, meaning they have lexical scope. But when I talk of “(effectively) global variables” I’m referring to the “action at a distance” problem which arises when a variable is used across so wide a scope that it becomes difficult to manage.

Hope that helps,

Athanasius <°(((>< contra mundum Iustus alius egestas vitae, eros Piratica,

[reply]
[d/l]
[select]

Re^4: DBD problem

by soonix (Canon) on Jan 17, 2017 at 16:28 UTC

Re^4: DBD problem

by Anonymous Monk on Jan 19, 2017 at 08:49 UTC


Keep It Simple, Stupid
	PerlMonks