Re: dbi retieve single col value

$sth->fetchrow_array () returns what it indicates: a list not a scalar

If you use bind_columns () (A GOOD CHOICE!), the call should be fetch_arrayref (), not fetch_array ()

Use UNIQUE or DISTINCT as already suggested if you want just a single value

or, remember what you saw:

$dbh->{RaiseError} = 1;
my $sth = $dbh->prepare (qq;
    select tax_id
    from   gene2acc
    where  genomic_ac_ver = '$ref';
    ); # qq;; is a nice way to make cut-n-paste to sql easier
$sth->execute;
my $tax_id;
$sth->bind_columns (\$tax_id);
my %seen;
while ($sth->fetch) {
    $seen{$tax_id}++ and next; # Only use first occurance
    print "Entering taxID_format with $tax_id\n";
   #taxID_format ($tax_id);
    }
keys %seen or print "No records matched for $ref\nn";
[download]

Enjoy, Have FUN! H.Merijn

Comment on Re: dbi retieve single col value Select or Download Code

Replies are listed 'Best First'.
Re^2: dbi retieve single col value by gone2015 (Deacon) on Aug 28, 2008 at 17:00 UTC
`$sth->fetchrow_array ()` returns what it indicates: a list* not a scalar* ...up to a point. In the case: `while($array = $sth1->fetchrow_array())` [download] i.e. in scalar context, Programming the Perl DBI says it will return the contents of the first column. However, the documentation http://search.cpan.org/~timb/DBI-1.607/DBI.pm#fetchrow_array is more equivocal: If called in a scalar context for a statement handle that has more than one column, it is undefined whether the driver will return the value of the first column or the last. So don't do that. Also, in a scalar context, an undef is returned if there are no more rows or if an error occurred. That undef can't be distinguished from an undef returned because the first field value was NULL. For these reasons you should exercise some caution if you use fetchrow_array in a scalar context. This doesn't appear to be the root of the problem. But when faced with an apparently hidden problem, I'm a big believer in clearing away the obviously problematic.	[reply] [d/l]
Re^2: dbi retieve single col value by Anonymous Monk on Aug 28, 2008 at 13:24 UTC
thats the output.... when I do the same query in mysql with all these ids: pulls out matching records : here the syntax I used on mysql: SELECT tax_id FROM gene2acc where genomic_ac_ver = 'CP000950.1'; here is what I used in the script: SELECT tax_id FROM gene2acc where genomic_ac_ver = '?'; nin foreach loop with CP000950.1 No records matched for CP000950.1 nin foreach loop with CP000950.1 No records matched for CP000950.1 please help me ....got lot of work!	[reply]
Re^3: dbi retieve single col value by Anonymous Monk on Aug 28, 2008 at 13:26 UTC
out put is here...sorry about that: nin foreach loop with CP000720.1 No records matched for CP000720.1 nin foreach loop with CP000950.1 No records matched for CP000950.1 nin foreach loop with CP000950.1	[reply]
Re^4: dbi retieve single col value by Tux (Canon) on Aug 28, 2008 at 15:00 UTC
Pasting output like this doesn't realy help. You should enclose it in `<code>` `</code>` tags Ever considered using `DBI->trace (8);` or something alike? We're not here to fix your database or guess it's content. We can however point at program errors or scripting mistakes, and we might be able to explain a database error. Enjoy, Have FUN! H.Merijn	[reply] [d/l] [select]