Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

DBI::mysql fetch() without execute() PROBLEM

by peppiv (Curate)
on Jan 21, 2002 at 23:34 UTC ( [id://140466]=perlquestion: print w/replies, xml ) Need Help??

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

I have a mysql database on our Web server. If I run mysqladmin (via telnet) I can query the database and everything works fine. I run this exact SELECT statement and it returns 189 records.

As I try to do this in Perl, I come across a statement I don't understand.

Here's the code and error:

#!/usr/bin/perl -w use strict; use DBI; use CGI::Carp qw(fatalsToBrowser); use CGI qw (:standard); print "Content-type: text/html\n\n"; my $dbh = DBI->connect(qq{DBI:mysql:f_dir=/usr/local//subscriptio +ns}, {RaiseError => 1} ); my $sth = $dbh->prepare( q{SELECT email FROM contacts WHERE state + = ?} ); my $rc = $sth->execute('OH'); print "Query will return $sth->{NUM_OF_FIELDS} fields.\n\n"; while (my @result = $sth->fetchrow_array()) { print "@result\n"; } # check for problems which may have terminated the fetch early die $sth->errstr if $sth->err; $dbh->disconnect;

fetch() without execute() at /usr/local/cgi-bin/DBI_mysql_01.pl line 20.(the line that checks for problems which may have terminated the fetch early).

I have DBI installed. DBD::mysql installed.

I can't find this problem in any docs. Can someone please give it a glance and tell me the obvious?

Super thanks -
peppiv

"Put zee candle back!"

Replies are listed 'Best First'.
Re: DBI::mysql fetch() without execute() PROBLEM
by cfreak (Chaplain) on Jan 22, 2002 at 01:00 UTC

    You get that error because there is a problem with your execute statement. It doesn't complete and then fetch without a proper execute causes your program to die. Changing

    my $rc = $sth->execute('OH');

    To:

    $sth->execute('OH') or die $sth->errstr;

    Should give you the error. I would venture to guess its in your original statement. BTW I don't believe capturing the output from the execute statement is particularly useful. I've never seen that used

    Hope that helps

    Chris

      Yes! You were right. It was in my original statement. In fact, it says no database selected. (Which is obvious now why it didn't return any results). Even though I included the exact and full path.

      Is there a better way to select the database if it's in a different directory other than f_dir= ?

        I should have seen that right away. You are trying to connect to a mysql database with syntax for the DBI::CSV module. When using the mysql driver connect like this:

        my $dsn = "DBI:mysql:database=your_db_name;host=your_hostname;"; my $dbh = DBI->connect($dsn,'your_db_username','your_db_password') or +die "Couldn't connect to database";

        So instead of "f_dir" use "database=" and make sure to put the host name. If you are trying to use a comma separated file somewhere on your system then you should use the CSV driver, just keep using the f_dir part and put "CSV" where you have "mysql".

        Hope that helps

        Chris

Re: DBI::mysql fetch() without execute() PROBLEM
by trs80 (Priest) on Jan 21, 2002 at 23:52 UTC
    Instead of
    my $rc = $sth->execute('OH');
    Try just this:
    $sth->execute('OH');

    I think assigning the execute to a variable is causing a scope issue.

Re: DBI::mysql fetch() without execute() PROBLEM
by jonjacobmoon (Pilgrim) on Jan 21, 2002 at 23:53 UTC
    First, let me say this: telnet bad, ssh good!

    But, that is not what you asked...... the error is pretty clear in most cases, except you appear to have run the execute; however, you have run it in a very unorthodox way (at least according to both the docs and the way I normally see it). You wrote:

    my $rc = $sth->execute('OH');

    Try:

    $sth->execute('OH');

    Even if that does not fix it you don't need the equation as far as I can see. I am not sure WHY that would cause it to fail, but it is out of the ordinary so I would point to that as my first guess, something to do with scoping I would think.


    I admit it, I am Paco.
      I tried dropping the my $rc =...... Same result.

      OK, OK. I'll work on the SSH thing.
Re: DBI::mysql fetch() without execute() PROBLEM
by screamingeagle (Curate) on Jan 22, 2002 at 00:43 UTC
    Since the error points to line 20, try replacing that line with this :
    die $dbh->errstr if $dbh->errstr;
    or else
    die $! if $!
      The  die $dbh->errstr if $dbh->errstr; gives me the same problem (fetch() without execute ()).

      The die $! if $! returns nothing.

        I had a similar problem. Looks like that when a script runs fetchrow_array and there is no more lines to be returned, DBD kill the cursor and , so, any try to access methods or attributes of the statement handle ($sth) will raise the error "fetch() without execute()".

        I solved my problem with a conditional :

        $sth->execute() or die "Error executing [$sqlcmd] : " . $dbh->errstr; $end_fetch = 0; do { if (! $end_fetch) { ($key_col, $col1, $col2, $col3) = $sth->fetchrow_array; if (! defined $key_col) { $end_fetch = 1; } } ... } while ($some_condition);

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others pondering the Monastery: (7)
As of 2024-03-28 14:15 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found