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

I use this code for executing db queries like so. I am now getting the error can't call method prepare on undefined value. How an this be when I define $dbh on the line before the prepare? Is there a better way to do this in General?
my $query = "SELECT id FROM table WHERE user=$user";

    my $results=$self->query($query)->fetchrow_array;

[download]
#-----------------------------------------------------------
# db query
#----------------------------------------------------------

sub query {
    # Do database query
    my $self=shift;
    
    my $dbh=$self->param('dbh');
    my $sth = $dbh->prepare(shift());
    $sth->execute();
    return $sth;
}##END query
############################################################

[download]

Replies are listed 'Best First'.
Re: Best way to call DBI query
by gmax (Abbot) on Mar 14, 2002 at 15:18 UTC
    Others have pointed out that the problem should be in your $dbh not being defined.
    However, there is something else that will give you problems, once you fix the $dbh business.
    fetchrow_array returns an array, not a scalar, so your result variable should be @results.

    update
    Unless you want to get the first column only, as johannz reminds. Thanks.
    However, be aware that, according to DBI docs, the result of fetchrow_array in scalar context is undefined. Therefore, it's better to ask for the result always in list context.

    If you want to use a scalar, then the method to call is fetchrow_arrayref, which returns an array reference.

     _  _ _  _  
(_|| | |(_|><
 _|
[reply]

      In scalar context, fetchrow_array returns the first column. Since he is only getting one column, this should be ok.

[reply]
Re: Best way to call DBI query
by derby (Abbot) on Mar 14, 2002 at 15:10 UTC
    hakkr,

    I'm not sure why you're currently getting error messages, maybe something else is overwriting dbh. But I am concerned about your lack of error checking and your use of prepare (and if this was just a stripped down example, much apologies). 

    my $dbh = $self->param('dbh') or carp "dbh is not behaving\n";
...
$sth->execute() or carp "problems executing ...\n"

    [download]

    note bad code showing use of "do" vice prepare deleted - it was just wrong but you probably should try prepare_cached and bind_params. 

    my $query = "SELECT id FROM table WHERE user = ?";
my $results=$self->query($query, $user )->fetchrow_array;

sub query {
    # Do database query
    my($self, $sql, $val ) = @_;
    
    my $dbh=$self->param('dbh') or die "what's up with dbh?\n";
    my $sth = $dbh->prepare_cached( $sql );
    $sth->execute( $val );
    return $sth;
}

    [download]

    But maybe I'm all wet and you were just showing a snippet.

    -derby Update: nevermind that whole "do" thingy - that only works on non-SELECT sql. drats.

[reply]
[d/l]
[select]
      Derby, I am using raise error, as for prepare I just thought that it gave the database a chance to optimise the query and get ready for action. I tried it woith do instead of prepare and got can't call do on undefined value. I put in my $dbh=$self->param('dbh') or die 'it is undef'; and it did't die

      MZSanford the db handle is defined cos I use it elsewhere. Weird thing is I am getting the erro even though my program does not even call the sub anywhere.

      cheers
      UPDATE Problem solved, I am using CGI::Application which has a method called query, calling my subroutine 'query' has caused this rather unhelpful error. Thanks for the tips anyway, maybe I should have mentioned CGI::application to begin with. A failed unintentional attempt at method overriding ha:)
[reply]
[d/l]
Re: Best way to call DBI query
by talexb (Chancellor) on Mar 14, 2002 at 14:43 UTC
    One gotcha that I've run into before is trying to access a table without telling DBI what database I'm interested in. So the query 
      select name, address from bar where age < 25;

    [download]
    needs to become 
      select name, address from foo.bar where age < 25;

    [download]
    I don't know if this is happening in your case, I just thought I'd pass it along in case it is.

    --t. alex

    "Here's the chocolates, and here's the flowers. Now how 'bout it, widder hen, will ya marry me?" --Foghorn Leghorn

[reply]
[d/l]
[select]
Re: Best way to call DBI query
by rdfield (Priest) on Mar 14, 2002 at 15:03 UTC
    As you say $dbh is set on the previous line, but what is the value of $self->param('dbh')? If you're trying to pass a reference from one CGI call to another - you can't, you'll have to use mod_perl/Apache::DBI.

    rdfield

[reply]
[d/l]
Re: Best way to call DBI query
by MZSanford (Curate) on Mar 14, 2002 at 14:43 UTC
    I believe this is because shift() is diffrent than shift. Calling shift() i believe forces shift to be called with no arguments, rather than with @_ as is done when called without parens.

    Reference : Programming Perl, 3rd Edition -- Chapter 6 -- Page 218
    from the frivolous to the serious
[reply]
[d/l]
[select]
      >I believe this is because shift() is diffrent than shift. Calling shift() i believe forces shift to be called with no arguments, rather than with @_ as is done when called without parens.

      If you are right, why does the following work correctly: 

      sub AnySub {
  my @list = @{ shift() }; # automatic dereferencing list
  ...

      [download]

      Best regards,
      perl -le "s==*F=e=>y~\*martinF~stronat~=>s~[^\w]~~g=>chop,print"

[reply]
[d/l]
        In that case, i stand (well, sit actually), corrected. Was worth a shot.
        As a side comment, i assume something is assigning a database handle to the $self object, and that $self has a param() method which returns that value. You probably shoud be checking defined($dbh) to make sure the Database connection was successful, and that nothing has gone wrong to this point.
        from the frivolous to the serious
[reply]
[d/l]
[select]
A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom