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

I am having a HUG problem figuring out the cause of an error in a perl script. My perl code is as follows: 
#!/user/bin/perl -w
######################################################################
+#########
# Copyright (c) 2005 Matt Verstraete - All Rights Reserved.           
+        #
# Modification, distribution, selling of this script is prohibited.   
+        #
# Use of this script anywhere but crystalreverie.com is prohibited.   
+        #
######################################################################
+#########
use strict;
use CGI q~:standard~;
use DBI;
my ($dbh, $sth, @resultholder, $uid, $password, $character);
$uid = param('uid');
$password = param('password');
print "Content-type: text/html\n\n";
print q~<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
+    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>Modify Your Character's Information</title>
<meta name="publisher" content="Matt Verstraete" />
<meta name="copyright" content="2005 Matthew Verstraete" />
<meta http-equiv="Expires" content="-1" />
<meta http-equiv="pragma" content="no-cache" />
<meta http-equiv="cache-control" content="no-cache" />
<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1
+" />
<style type="text/css"><!--
body {background-color: #cccccc; color: #009900}
.center {text-align: center}
--></style>
</head>
<body>~;
$dbh = DBI -> connect ('dbi:ODBC:read', 'uid', 'pass') or die "Could n
+ot open database: $DBI::errstr; stopped";
$sth = $dbh -> prepare (qq~select character_name, uid from characters 
+where character_name = 'Sirmatt'~) or die "Could not prepare statemen
+t: $DBI::errstr; stopped";
$sth -> execute() or die "Could not execute SQL statment: $DBI::errstr
+; stopped";
@resultholder = $sth -> fetchrow_array;
$dbh -> disconnect();
print qq~\n</body>
</html>~;

[download]
The error I get is: 
DBI::db=HASH(0x18a0ea0)->disconnect invalidates 1 active statement han
+dle (eithe
r destroy statement handles or call finish on them before disconnectin
+g) at D:\crystalreverie.com\www\members\modify.pl line 34.

[download]
Line 34 is the dbh -> prepare code. I have about 5 other scripts running fine and all the code related to the DB is copyed from one of the working ones with NO modification. So my question is, Why does this code work perficly fine in the other scripts but in this extreamly simple script does it fail?

Replies are listed 'Best First'.
Re: Can not figure out cause of error.
by jZed (Prior) on Aug 13, 2005 at 02:47 UTC
    fetchrow_array() only fetches one row. If your result set has more than one row, you need to either fetch them all (e.g. in a loop), or else use $sth->finish after the fetch and before disconnect.
[reply]
Re: Can not figure out cause of error.
by pg (Canon) on Aug 13, 2005 at 03:28 UTC

    The error basically says that the prepared statement is still active.

    Why don't you always see this error? The key here is that your driver will automatically close the prepared statement for you after the entire result set is fetched. If the query returns a single row, your code will be fine, as after one fetchrow_array, the statement will be closed. In case the query returns multiple rows, this is where the problem is in introduced: as there are more rows left to be fetched, the statement stays active, and the error comes up when you disconnect.

    To get all rows, either loop through the result set by calling fetchrow_array, or call fetchall_arrayref to return the entire result set.

[reply]
      But the select statment only returns one row. And it is the exact same select statment used in another script which works fine. Which is why I am confused as to why that exact select statment works in one script and not this one.
[reply]
        Do you still get the error if you put $sth->finish() after $sth->fetchrow_array()?
[reply]
        Opps forgot to login on that post sorry.
[reply]

Back to Seekers of Perl Wisdom