I am attempting to test some code in various situations (for various result sets). I have got the very first test running well, but the next is attempting to reuse the very first "table".

My result sets:

my $usernames_many = [
      { username => '1234567' },
      { username => '2345678' },
   ];
my $usernames_empty = [
   ];

however after i try these calls:

$mock_dbi->set_retval_scalar(MOCKDBI_WILDCARD, "SELECT username FROM location", $usernames_many);
is_deeply(find_multiple_registrations($mock_db, 15), [ '1234567', '2345678' ], "many entries");

$mock_dbi->set_retval_scalar(MOCKDBI_WILDCARD, "SELECT username FROM location", $usernames_empty);
is_deeply(find_multiple_registrations($mock_db, 15), [ ], "no entries");

The very first test passes, however the second one leads to:

not ok 3 - no entries
#   Failed test 'no entries'
#   at ./report_many_registrations_test.pl line 28.
#     Structures begin differing at:
#          $got->[0] = '1234567'
#     $expected->[0] = Does not exist

Which appears to point the very first resultset was adopted again rather. How do i clean a resultset? Or totally reset the condition in certain other way?

Should you (can) alter the second test to something similar to:

$mock_dbi->set_retval_scalar( 
    MOCKDBI_WILDCARD, 
    "Get me username stuff",   # <= something different
    $usernames_empty
);

you might discover that the exam now works.

The reason being Test::MockDBI just uses the SQL text provided includes a placeholder that it returns the DBI object following a matching dbi->prepare( 'Get me username stuff' );

Update - This is a workaround that does not require altering the SQL:

BEGIN { push @ARGV, "--dbitest=1"; }

use 5.012;
use warnings;
use Test::More;
use Test::MockDBI ':all';

my $mock_dbi = Test::MockDBI::get_instance;
my $dbh      = DBI->connect(q{}, q{}, q{});
my $sql      = 'SELECT username FROM location';

my $Data =  [
    { username => '1234567' },
    { username => '2345678' },
];

$mock_dbi->set_retval_scalar( MOCKDBI_WILDCARD, $sql, sub { $Data } );

is_deeply( get_mock_user($dbh, $sql), [1234567,2345678], 'many entries' );

$Data = [];  # change the data!

is_deeply( get_mock_user($dbh, $sql), [], 'no entries' );

done_testing;

sub get_mock_user {
    my ($dbh, $sql) = @_;
    $dbh->prepare( $sql );
    [ map { $_->{username} } @{ $dbh->fetchrow_arrayref } ];
}

/I3az/