Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation

Error with SELECT and MySQL

by bobione (Pilgrim)
on Jul 05, 2001 at 00:13 UTC ( #93930=perlquestion: print w/replies, xml ) Need Help??

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();

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\ 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.

BobiOne KenoBi ;)

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;



      Ok, thanks a lot... That was my mistake.

      BobiOne KenoBi ;)

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
Re: Error with SELECT and MySQL
by bobione (Pilgrim) on Jul 05, 2001 at 00:16 UTC
    Da*n, I was sure I miss something...
    I forgot to remove this code:

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

    BobiOne KenoBi ;)

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?



      No it work in fact.
      There was just a error message.

      BobiOne KenoBi ;)

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://93930]
Approved by root
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (3)
As of 2020-10-31 14:13 GMT
Find Nodes?
    Voting Booth?
    My favourite web site is:

    Results (290 votes). Check out past polls.