Another meek DBI question

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

I am trying to query my database to get information for a specifc user... in this case "gwalker". This is my code:

use strict;
use DBI;
$dbh = DBI->connect("DBI:mysql:gds:localhost", "tools", "obviouslyfake
+passwd") or die  ("No love for the DBI connect");
$sql = qq{ SELECT uname, fname, fgroup, dmod, accts FROM noctracker WH
+ERE uname = 'gwalker'} ;
$sth = $dbh->prepare( $sql ) or die ("Could not prepare select");
$sth->execute();

$sth->bind_columns( undef, \$uname, \$fname, \$fgroup, \$dmod, \$accts
+ );

while ( $sth->fetch ) {
    print "<BR> Matched username $uname for name: $fname ";
}

$sth->finish();
$rc = $dbh->disconnect or die  ("No love for the DBI disconnect");

&footer;
exit;
[download]

and the output, oddly enough is:

DBI::st=HASH(0x164ad0)

Matched username for name:
[download]

Which is not what expected... that query works from the msql command line...

mysql> SELECT uname, fname, fgroup, dmod, accts FROM noctracker WHERE 
+uname = 'gwalker';
+---------+----------------+---------+---------------+----------------
+---------------+
| uname   | fname          | fgroup  | dmod          | accts          
+               |
+---------+----------------+---------+---------------+----------------
+---------------+
| gwalker | Glen G. Walker | GDS-NOC | 20030820 1700 | BASTION SECUREI
+D THEQ CLARIFY |
+---------+----------------+---------+---------------+----------------
+---------------+
1 row in set (0.00 sec)
[download]

Comment on Another meek DBI question Select or Download Code

Replies are listed 'Best First'.
Re: Another meek DBI question by antirice (Priest) on Aug 21, 2003 at 22:14 UTC
Few things: I think this is a really reduced version of the code and as such you may be cutting something out. Why? You use strict and don't declare $uname, $fname, $fgroup, $dmon, or $accts yet still get output. $sth->execute() can also fail. Add an or die "Execute failed: ",$dbh->errstr Make certain you're getting results with print "Rows found: ",$sth->rows I think there's a problem with your password ;-P Hope this helps. antirice The first rule of Perl club is - use Perl The ith rule of Perl club is - follow rule i - 1 for i > 1	[reply]
Re: Re: Another meek DBI question by mpeppler (Vicar) on Aug 22, 2003 at 11:11 UTC
3. Make certain you're getting results with print "Rows found: ",$sth->rows Beware! `$sth->rows()` may not return any significant information for SELECT queries (i.e. it may work for MySQL, but it definitely does NOT work for Sybase or Oracle unless all the rows have already been fetched!) Michael	[reply] [d/l]
Re: Another meek DBI question by dws (Chancellor) on Aug 22, 2003 at 01:29 UTC
Passing `undef` as the first parameter to `bind_columns()` is there for backward compatability. I don't trust backwards compatability, having been burned by incomplete backward compatability code a few times. Try removing the `undef`. I'm curious about the output you're getting. I don't see where `DBI::st=HASH(0x164ad0)` is getting printed from. Are you showing us the fragment that you're running, or a subset?	[reply] [d/l] [select]
Re: Another meek DBI question by krisahoch (Deacon) on Aug 21, 2003 at 22:18 UTC
coyotlgw, This seems strange to me. I pick these parts out of your code `use strict; $sth->bind_columns( undef, \$uname, \$fname, \$fgroup, \$dmod, \$accts + );` [download] I don't see you declaring any of these varibles with 'my'. `my ($uname,$fname,$fgroup,$dmod,$accts);` [download] Update: Removed a section based on chromatic's comments. Give that a whirl, and see what happens Kristofer Hoch Si vos can lego is, vos es super erudio	[reply] [d/l] [select]
Re: Re: Another meek DBI question by chromatic (Archbishop) on Aug 21, 2003 at 22:31 UTC
Secondly, the call to bind_columns is a bit off. This is supposed to be an array (see DBI docs) No, it's supposed to be a list. It could only be an array if `bind_columns` were prototyped to expect an array. Even if it were, it wouldn't work, as it's a method call, and those ignore prototypes. Besides all that, your code will be off by one, as a reference to `undef` doesn't look like `undef` or a hash reference. I wouldn't be surprised if your code threw "couldn't modify constant item" errors.	[reply] [d/l] [select]
Re: Re: Another meek DBI question by blokhead (Monsignor) on Aug 21, 2003 at 22:28 UTC
Secondly, the call to bind_columns is a bit off. This is supposed to be an array (see DBI docs) `my $rv = sth->bind_columns(\(undef,$uname,$fname,$fgroup,$dmod,$accts) + );` [download] Er... It's supposed to be a list. And taking a reference to a list (like you have done) makes a list of references (see perlref). So what you wrote was equivalent to what the original poster wrote, except your first argument is a ref to `undef` instead of `undef` itself. `## from perlref @list = (\$a, \@b, \%c); @list = \($a, @b, %c); # same thing!` [download] You'll notice in the DBI docs that any use of `bind_columns` with an actual named array uses the form `\( @array )` -- which also makes a list of references, and not an array reference. It's different than `[ @array ]` and `\@array`. blokhead	[reply] [d/l] [select]