Returning multiple values

[JackTheRussel]

Hi.

I have function which runs SQL-sentences and returns a
table where the results are

sub select {
    my ( $sql_sentence, @parameters ) = @_;
    $sth = $db_connection->prepare($sql_sentence);
    $sth->execute( @parameters ); 
    my @data = $sth->fetchrow_array;
    while (my $_f = $sth ->fetchrow_array){
    push(@data, $_f);
    }
   $sth->finish();
         return @data;  #Return records
    $sth->finish();
}

This is ok. Return-sentence returns values to me.

Now I would like to know how many records I get.
and I add one variable into function.


$howMany = $sth->execute( @parameters );



Now how I get both of these values out (return-sentence).

 

[stevexff]

You could add it as the first item in @data, and shift it off when you get the data back, or you could just use scalar(@data) which would give you the number of rows added to the array, which is kind of $howMany by proxy. I'd probably go for the latter, as it's cleaner.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[MillerH]

Well ... first off, I'd like to do a little clean up of your code:

sub select {
    my ( $sql_sentence, @parameters ) = @_;
    $sth = $db_connection->prepare($sql_sentence);
    $sth->execute( @parameters ) or die $db_connection->errstr;
    my @data = ();
    while (my ($column) = $sth->fetchrow_array){
        push @data, $column;
    }
    $sth->finish();
    return @data;  #Return records
}

Previously, you were forcing at least one record into @data. This code will not properly handle the case when no records are returned. Also, I'm assuming that you are only pulling a single column in your select statement, as otherwise fetchrow_array will be dropping information when you assign it to a scalar. If there is more than one column use fetchrow_arrayref.

Finally, now that your code is fixed, all you need to do to know the number of rows returned is count your records.

my @data = select("SELECT yourColumn FROM yourTable WHERE otherColumn=?", 'foobar');
my $count = @data;
print "Records = $count\n";

 

[stevexff]

Alternative - ditch the loop and just use fetchall_arrayref()

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[MillerH]

Slight typo, that definitely changes meaning.

...This code will not now properly handle...

 

[JackTheRussel]

Thanks all of you !