CODEWITHSUNDEEP
perldbi
w.k
w.k

Reputation: 8376

How to force selectrow_array return wrong value?

I remember having problem with DBI method selectrow_array. When i wasn't tidy enough i got back from it not the value of the column i asked, but count of columns (or something unwanted, i can't recall exactly). Now i try to refactor some code and i want to make sure in every possible place, that i get back only expected value. So i try to avoid surprises and find out which the bad behaviour was. From DBI docs i read that this may be really be problematic situation:

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 "selectrow_array" in a scalar context, or just don't do that.

Still i can't force selectrow_array to return anything but value of the col1 (that's it what i am expecting)

my $query = 'SELECT col1, col2, col3 FROM table WHERE id = 112233';

my ( $c ) = ( $dbh->selectrow_array( $query ) );
my $x = ask_from_db();
my $y = $dbh->selectrow_array( $query );
my $z = ( $dbh->selectrow_array( $query ) );
my @A = $dbh->selectrow_array( $query );

say "C: $c"; # C: col1
say "X: $x"; # X: col1
say "Y: $y"; # Y: col1
say "Z: $z"; # Z: col1
say "A: @A"; # A: col1 col2 col3

sub ask_from_db {
  return $dbh->selectrow_array( $query );
}

Every way i ask above, gives me fine result. How should i run the query to get wrong result?

wrong result != col1 value

Upvotes: 2

Views: 1182

Answers (2)

ikegami
ikegami

Reputation: 385847

The difference in outcome will be based on the implementation of the driver.

wantarray ? @row : $row[0]

vs

wantarray ? @row : $row[-1]

You'd use to use a different driver to get a different outcome. That said, I imagine you'll have a hard time finding a driver that doesn't return the first.

If you want to be sure to get the first, use:

( $dbh->selectrow_array( $query ) )[0]

Upvotes: 3

ErikR
ErikR

Reputation: 52039

What the documentation means by "it is undefined whether the driver will return the value of the first column or the last" is that the column returned is defined by the database driver and not DBI.

So the Postgres driver may decide to always return the first column whereas the mysql driver may always return the last column, or the column returned might depend on the query.

So don't call selectrow_array is scalar context - always call it in list context:

my @row = $sth->selectrow_array($query)

and you'll avoid all of the issues that the documentation mentions.

Upvotes: 3

Related Questions