Error with SELECT and MySQL

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

Why ? Why everything is against me ? :)
Well let's tell you. Here is some small code that doesn't work. Very easy, just connect to database MySQL, select, (print), and disconnect. But... It's my first use of DBI and DBD::Mysql.

#! perl -w

use strict;
use DBI;
use DBD::mysql;

# Connect DataBase
my $dbh = DBI->connect('DBI:mysql:CCM', 'root', '');

###### THIS DOESN'T WORK :( #####
# Prepare the SQL query for execution
my $sth = $dbh->prepare(<<End_SQL) or die "Couldn't prepare statement:
+ $DBI::errstr; stopped";
SELECT f1, f2, f3, f4, f5, f6 FROM forum WHERE f1 = 10
End_SQL

###### THIS WORK FINE #####
# my $sth = $dbh->prepare(<<End_SQL) or die "Error: i$DBI::errstr; sto
+pped";
# INSERT INTO forum values(10, "foo", "bar", "foobar", "barfoo", "barb
+ar")
# End_SQL

$sth->execute() or die "Error: $DBI::errstr; stopped";

# Fetch each row and print it
my ($f1, $f2, $f3, $f4, $f5, $f6) = $sth->fetchrow_array();
print STDOUT "$f1 $f2 $f3 $f4 $f5 $f6\n";

my $title = $sth->fetchrow_array();
print $title;

# Disconnect Database
$dbh->disconnect();
[download]

In fact, this may work but give me this error message:

DBI::db=HASH(0x1d2bae0)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at C:\WINDOWS\Profiles\bob\Desktop\ccm_forum.pl line 32.

That mean on the last line during DISCONNECT(). I don't really understand this message...
So I am searching for enlightement from The God PERL.
Thanks.

BobiOne KenoBi ;)

Comment on Error with SELECT and MySQL Select or Download Code

Replies are listed 'Best First'.
Re: Error with SELECT and MySQL by tachyon (Chancellor) on Jul 05, 2001 at 00:25 UTC
You need to call finish() on $sth before you disconnect: `$sth->finish(); # wrap it all up before disconnecting $dbh->disconnect;` [download] tachyon s&&rsenoyhcatreve&&&s&n\w+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print	[reply] [d/l]
Re: Re: Error with SELECT and MySQL by bobione (Pilgrim) on Jul 05, 2001 at 01:01 UTC
Ok, thanks a lot... That was my mistake. BobiOne KenoBi ;)	[reply]
Re: Error with SELECT and MySQL by MZSanford (Curate) on Jul 05, 2001 at 11:10 UTC
While a fix for your error has been provided, i would like to briefly cover the cause ... so it does not happen to you over and over. When you run your select statement, you only read one line of the result. When calling disconnect() if there are any statement handles which have data waiting, you get the `invalidates X active statement handle(s)` message. So, the rason one worked and the other did not is because one query had a singl result line, while the other had multiple. If you only want one line, regardless of the number returned, `$sth->finish()` is a fix, but sometimes, it hides the issue of truncating result sets. may the foo be with you	[reply] [d/l] [select]
Re: Error with SELECT and MySQL by bobione (Pilgrim) on Jul 05, 2001 at 00:16 UTC
Dan, I was sure I miss something... I forgot to remove* this code: `my $title = $sth->fetchrow_array(); print $title;` [download] BobiOne KenoBi ;)	[reply] [d/l]
Re: Error with SELECT and MySQL by tachyon (Chancellor) on Jul 05, 2001 at 00:23 UTC
When you say your select does not work what do you mean - do you get an error or the wrong data? tachyon s&&rsenoyhcatreve&&&s&n\w+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print	[reply]
Re: Re: Error with SELECT and MySQL by bobione (Pilgrim) on Jul 05, 2001 at 01:15 UTC
No it work in fact. There was just a error message. BobiOne KenoBi ;)	[reply]


"be consistent"
	PerlMonks