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

Hi perl monks

I'm trying to mock an sql call (for practice purposes) using DBD::Mock and i'm getting a wrong result.

Code.t

#!use/local/bin/perl use Test::More qw (no_plan); use Data::Dumper; use DIR::DBTeams; use constant DB => "dbi::Mock:"; use constant DB_USER => "mock"; use constant DB_PWD => "mock"; my $db; my $query; my $columns; my @boundparams; my @results; my $test_teams; $query = q(select decode(substr(te.TEAM_ID,1,2),'MU','Manchester Unite +d', 'RM', 'Real Madrid')) from Teams t inner join TEAMS_EUROPE.TEAM_REF te + on t.TEAM_REF = te.TEAM_REF and t.TEAM_REF=:ref); $boundparams=('123'); #123 is reference for MU $columns=('TEAM_ID'); @results=('Manchester United'); $db = &connectDB(DB,DB_USER,DB_PWD); my $session = DBD::Mock::Session->new('my_sesion' => ( { statement => $query, bound_params = [@boundparams], results => [[$columns],[@results]] } ) ); $db->{mock_session}=$session; $test_teams = new DBTeams($db, $boundparams[0]); is($test_teams, "Manchester United", "Test Teams"); sub connectDB($$$) { my $dbh = DBI->connect($_[0],$_[1],$_[2]); return $dbh; }

Code : DBTeams

package DIR::DBTeams; use DBI; sub new { my $class = shift; my $self = {}; my ($dbh,$ref) = @_; $self->{dbh} = $dbh; &getTeam($ref); } sub getTeam { my $me = shift; my $ref = shift; my $ret; my $cur = $me->{dbh}->prepare( q(select decode(substr(te.TEAM_ID,1,2),'MU','Manchester United', 'RM', 'Real Madrid')) from Teams t inner join TEAMS_EUROPE.TEAM_REF te + on t.TEAM_REF = te.TEAM_REF and t.TEAM_REF=:ref)); $cur->bind_param(':ref',$ref); $cur->execute() or croak $cur->errstr; $ret = $cur->fetchrow_array(); $cur->finish; return $ret; }

The code seems ok, but when it runs, instead of getting 'Manchester United', the method returns 1.

I tried debugging and several alternatives and none seem to work.

Finaly i tried changing the original code:

Code : DBTeams

... ($ret) = $cur->fetchrow_array(); ...

By placing $ret in ( ), the code now returns what i want. However, i want to know if there is an alternative that allows me to get the result i want without changing the original code.

Can someone help me? Thanks

Replies are listed 'Best First'.
Re: Mocked query returns wrong result
by choroba (Cardinal) on Oct 24, 2014 at 14:50 UTC
    fetchrow_array returns each row as an array. You cannot assign an array to a scalar variable (array in scalar context returns its size). To assign an array reference, you can use fetchrow_arrayref. By adding the parentheses, you create a list on the left hand side, and the first column is assigned to the variable in the parentheses.

    The original code has a bug. You can't get a better result without fixing it.

    لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
Re: Mocked query returns wrong result
by chacham (Prior) on Oct 24, 2014 at 16:16 UTC

    Side comment: I never understood why people like DECODE() over CASE. I find CASE to be much clearer. Though, iirc, speed is the same for both.

      Thanks for the information and suggestion.

        In this particular case, CASE might be better because it can use LIKE, which is better than SUBSTR which would negate the use of an index.

        Though, specifying LIKE for each option might work against that, so its hard to tell.

        CASE WHEN te.TEAM_ID LIKE 'MU%' THEN 'Manchester United' WHEN te.TEAM_ID LIKE 'RM%' THEN 'Real Madrid' END Team