DBI Error

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

Could someone help me. I'm new to CGI. I encoded the following code and got an error message which i don't know how to fix it.

#!/usr/bin/perl

print "Content-type:text/html\n\n";

use DBI;
use CGI;
$query = new CGI;

my $dbh=DBI->connect('DBI:mysql:test','edward','password');

my $logname=$query->param('log');
my $passwd=$query->param('pass');

my $sth=$dbh->prepare(<<End_SQL);
SELECT * from login where log = "$logname"
End_SQL

$sth->execute();

@row=$sth->fetchrow_array;

$dbh->disconnect();
print <"TESTING</br>">;
print "</body></html>";
[download]

when i try to check this using the "./new.response.cgi" command, i get the following error.

Content-type:text/html

DBI::db=HASH(0x81dc3e0)->disconnect invalidates 1 active statement han
+dle (eithe
r destroy statement handles or call finish on them before disconnectin
+g) at ./new.response.cgi line 22.
[download]

Hope u could help me.

Comment on DBI Error Select or Download Code

Replies are listed 'Best First'.
Re: DBI Error by BBQ (Curate) on Jul 10, 2000 at 07:26 UTC
Just cleaning up the AM's post... `#!/usr/bin/perl print "Content-type:text/html\n\n"; use DBI; use CGI; $query = new CGI; my $dbh=DBI->connect('DBI:mysql:test','edward','password'); my $logname=$query->param('log'); my $passwd=$query->param('pass'); my $sth=$dbh->prepare(<<End_SQL); SELECT * from login where log = "$logname" End_SQL $sth->execute(); @row=$sth->fetchrow_array; $dbh->disconnect(); print <"TESTING</br>">; print "</body></html>";` [download] `Content-type:text/html DBI::db=HASH(0x81dc3e0)->disconnect invalidates 1 active statement han +dle (eithe r destroy statement handles or call finish on them before disconnectin +g) at ./new.response.cgi line 22.` [download] Update: BTW: I just noticed this. Is there any specific reason why you are not using placeholders with prepare? I'd do it this way... `$sth = $dbh->prepare(qq{ SELECT * from login where log = ? }) or die("Failed to prepare: $DBI::errstr\n"); $sth->execute($logname) or die("Failed to execute: $DBI::errstr\n");` [download] Be lazy, and let DBI quote for you, its easier! :) #!/home/bbq/bin/perl # Trust no1!	[reply] [d/l] [select]
Re: DBI Error by btrott (Parson) on Jul 10, 2000 at 07:18 UTC
Do what the error message says. Add `$sth->finish;` [download] before you disconnect. That error is DBI telling you that you still have an open statement handle when you're disconnecting. It's not a fatal error because DBI can always close them up for you. But an open statement handle at the time of disconnecting could be a sign of problems, and not closing the statement handles could cause you problems. So just call finish on the statement handle.	[reply] [d/l]
Re: DBI Error by turnstep (Parson) on Jul 10, 2000 at 14:59 UTC
To follow up on what BBQ says, it's always a good idea to quote any user input, especially anything arriving from a form, so that someone who enters a logname of "Jack's Giant House o' Pancakes!" won't throw an error. Another way (besides placeholders) to do this is to use the quote function. Once you have an active db handle (e.g. $dbh) just use $dbh->quote("string") and it will return a quoted version. For example, your script could read: `my $logname=$dbh->quote($query->param('log')); my $passwd=$dbh->quote(query->param('pass'));` [download] For most databases, this means that a single apostrophe becomes a double apostrophe (among other things) and the string is now "safe" to pass to the database.	[reply] [d/l]
Re: DBI Error by agoth (Chaplain) on Jul 10, 2000 at 13:12 UTC
As btrott above, use $sth->finish to close your cursor, You could also use : `my $dbh = DBI->connect('dsn', 'uid', 'pwd', {RaiseError => 1});` [download] to raise your errors if your version of DBI is up to date enough.	[reply] [d/l]