[OT [OT]] - assering database query results

[Bob MacCallum]

My initial thought was that

SELECT ($your_select) IS NOT NULL;

would complain if more than one row is returned.

It does, but it also complains when more than one column returned, so
probably isn't useful.

On Thu, Sep 17, 2009 at 2:32 PM, Joel Bernstein <joel at fysh.org> wrote:
> 2009/9/17 Nicholas Clark <nick at ccl4.org>:
>> Off topicly off topic, as it's not even about that 4 letter P word.
>>
>> So, we have quite a lot of our database queries in config files, with
>> placeholders, etc
>>
>> Some of them are only supposed to return 1 row.
>> However, if our assumptions are wrong, they might return multiple rows.
>> This isn't a fatal bug in our code, which is written such that it takes the
>> first row returned. However, it is a bug in our logic.
>>
>> Is there a good way to "assert" that there is only one row? So that in the
>> development (and QA) environments, it is fatal to breach assumptions.
>> But production keeps going.
>
> DBIx::Class::Storage::DBI has:
> sub select_single {
>  my $self = shift;
>  my ($rv, $sth, @bind) = $self->_select(@_);
>  my @row = $sth->fetchrow_array;
>  my @nextrow = $sth->fetchrow_array if @row;
>  if(@row && @nextrow) {
>    carp "Query returned more than one row.  SQL that returns multiple
> rows is DEPRECATED for ->find and ->single";
>  }
>  # Need to call finish() to work round broken DBDs
>  $sth->finish();
>  return @row;
> }
>
> which is what $schema->resultset("Blah")->search(...)->single hits.
>
> You could reasonably turn that carp into a croak. However, if you
> meant to model this assertion at DB-level rather than in your
> consuming application's code, I'm out of ideas.
>
> /joel
>



-- 
http://evolectronica.com - survival of the funkiest