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

Hi monks,

I'm trying to write a test which mocks a database connection. The code prepares the SQL statement and then executes it several times with differing bind parameters. This is what I tried using DBD::Mock:

use Test::More qw(no_plan);
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect('dbi:Mock:','','' );

my @session_ar =
    ( {
       statement     => "select foo,bar from baz where fi=?",
       bound_params  => [ 1 ],
       results       => [['foo','bar'],
                         ['1foo','1bar']],
   },
      {
       statement     => "select foo,bar from baz where fi=?",
       bound_params  => [ 2 ],
       results       => [['foo','bar'],
                         ['2foo','2bar']],
   });
my $session = DBD::Mock::Session->new(@session_ar);
$dbh->{mock_session}=$session;

my $sth = $dbh->prepare("select foo,bar from baz where fi=?");

for my $i (1..2) {
    $sth->execute($i);
    my @result=$sth->fetchrow_array();
    is($result[0],"${i}foo","Got right foo");
    is($result[1],"${i}bar","Got right bar");
}

[download]

Running this code with the current version of DBD::Mock gives an error on the second execute because of a wrong number of bound parameters:

ok 1 - Got right foo
ok 2 - Got right bar
DBD::Mock::st execute failed: Session Error: Not the same number of bo
+und params in current state in DBD::Mock::Session (Session 1) at /usr
+/share/perl5/DBD/Mock.pm line 1094. at t/90.dbd-mock.t line 28.
not ok 3 - Got right foo
#     Failed test (t/90.dbd-mock.t at line 30)
#          got: undef
#     expected: '2foo'
not ok 4 - Got right bar
#     Failed test (t/90.dbd-mock.t at line 31)
#          got: undef
#     expected: '2bar'
1..4
# Looks like you failed 2 tests of 4.

[download]

There's a bug report in RT regarding this problem. Trying to apply the 'fix' from the bug report gets me a little further:

ok 1 - Got right foo
ok 2 - Got right bar
not ok 3 - Got right foo
#     Failed test (t/90.dbd-mock.t at line 30)
#          got: '1foo'
#     expected: '2foo'
not ok 4 - Got right bar
#     Failed test (t/90.dbd-mock.t at line 31)
#          got: '1bar'
#     expected: '2bar'
1..4
# Looks like you failed 2 tests of 4.

[download]

But it now returns the result from the first session entry even when called with the second value. If I move the prepare call into the loop it works correctly with and without the "fix", but that's not what I want to do in my code ;-). I tried using mock_add_results instead of DBD::Mock::Session, but that didn't work either.

I've looked through the DBD::Mock code for a bit, but it doesn't seem obvious to me how to add this behaviour and I'm in a bit of a rush to get this test written. Is there any way I can do this either with DBD::Mock or another module (short of setting up my own test database)? Thanks in advance.


Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. -- Brian W. Kernighan

Replies are listed 'Best First'.
Re: How to mock results to repeated $sth->execute() calls (DBD::Mock::Session)
by ChemBoy (Priest) on Dec 11, 2005 at 14:10 UTC

    This is a slightly thorny problem—when I was working on some tests with DBD::Mock, I tried to come up with a good general solution for this issue and the possibility of failure during a prepare call, and ran out of time to do it. I did come up with an adequate hack for this part, though: 

    @@ -1081,7 +1087,11 @@
 #    print STDERR "Adding Results: " . (join " | " => map { join ", "
+ => @{$_} } @{$current_state->{results}}) . "\n";
     # copy the result sets so that 
     # we can re-use the session 
-    $dbh->STORE('mock_add_resultset' => [ @{$current_state->{results}
+} ]);
+    if (my @results =  @{$current_state->{results}}  ) {
+        $dbh->STORE('mock_add_resultset' => [ @results ]);
+    } else {
+          $self->{state_index}++;
+    }
 }
 
 sub verify_bound_params {

    [download]

    To use it, you'd put an entry in the session looking like {statement => "select ...", results => [] } at the place you expected the prepare to happen, then normal entries for the execute calls.

    Hope this helps!


    If God had meant us to fly, he would *never* have given us the railroads.
        --Michael Flanders

[reply]
[d/l]
[select]

      Thanks a lot ChemBoy, that put me on the right track. I still had to modify execute() to put the correct result set into the statement tracker, but now it seems to work fine.

      Here's the change I made, for posterity (it's a godawful hack, but maybe it'll still be useful to someone at some point):

      @@ -559,6 +559,10 @@
         my $dbh = $sth->{Database};
         eval {
             $session->verify_bound_params($dbh, $tracker->bound_param
+s());
+            my $idx=$session->{state_index}-1;
+            my @results=@{$session->{states}[$idx]{results}};
+            shift @results;
+            $tracker->{return_data}=\@results;
         };
         if ($@) {
             my $session_error = $@;

      [download]

      Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. -- Brian W. Kernighan
[reply]
[d/l]
[select]
Re: How to mock results to repeated $sth->execute() calls (DBD::Mock::Session)
by dragonchild (Archbishop) on Dec 11, 2005 at 19:53 UTC
    Please post all of this to DBDMock@groups.google.com, which is the mailing list.
    My criteria for good software:
    1. Does it work?
    2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
[reply]

Back to Seekers of Perl Wisdom