byterock

Its still param day here in the Moose-Pen

Carrying on from yesterday's post I, today I added in one more test;

ok($user->update( $utils->connect(),$container),"update with present container");

$user->reset_conditions();

$container = [{address =>'MNO'},

              {address =>'PQR'},

              {address =>'STU'},

              ];

$user->add_condition({left  =>{ name  => 'username',

                                view  => 'user'},

                      right =>{ value => ['Bill','Jane','Joe']}

                    });

ok($user->update( $utils->connect(),$container),"update array on where param");

This time round I have cleared out my current conditions, changed the container to be just addresses and then in the condition I have added an array ref for the value. So what I hope to happen is I will get this SQL

UPDATE user SET user.address = ? WHERE user.username = ?

which will bind to the $container above and the right value in the condition to the SQL set of;

UPDATE user SET user.address = 'MNO' WHERE user.username = 'Bill'

UPDATE user SET user.address = 'PQR' WHERE user.username = 'Jane'

UPDATE user SET user.address = 'STU' WHERE user.username = 'Joe'

and when I run my tests I get'

…

ok 14 - update with present container

ok 15 - update array on where param

and if I dump the table contents I get;

...

'John', 'HIJ'

'Bill', 'MNO'

'Jane', 'PQR'

'Joe', 'STU'

Cool, it worked, without any code changes! I guess I am better than I thought. Onto the next test;

Now I will test the reverse of the above;

$user->reset_conditions();

$container = {address =>'VWX'};

$user->add_condition({left  =>{ name  => 'username',

                                view  => 'user'},

                      right =>{ value => ['Bill','Jane','Joe']}

                    });

ok($user->update( $utils->connect(),$container),"update hash conter with array");

Which I expect will do an SQL set like this;

UPDATE user SET user.address = 'VWX' WHERE user.username = 'Bill'

UPDATE user SET user.address = 'VWX' WHERE user.username = 'Jane'

UPDATE user SET user.address = 'VWX' WHERE user.username = 'Joe'

and when I run my test the result is a 'Pass' but it is a false pass as my DB dump is still

...

'John', 'HIJ'

'Bill', 'MNO'

'Jane', 'PQR'

'Joe', 'STU'

and there is no error on the DA result class. First I checked in the returned result class to see if my biding params are correct;

   'params' => [ 'VWX',

                      [ 'Bill', 'Jane', 'Joe' ]

                       ],

and they are. So into the code I go.

Doing a few runs with DBI trace set to an nice high '15' I found that the SQL I was trying to run was a single statement of

UPDATE user SET user.address = 'VWX'  WHERE user.username = 'BillJaneJoe'

and was binding using the regular 'bind_param' rather than 'bind_param_array'. So there is no SQL or DB error but that SQL will not effect anything in the DB.

After another few mins of poking about I found that the change to make this work was just a trivial little if statement

sub _element_sql {

  my $self = shift;

  my ($element,$use_alias) = @_;

  if (ref($element) eq 'Database::Accessor::Param'){

++   if (ref($element->value) eq "ARRAY"){

++     $self->is_exe_array(1);

++   }

    $self->add_param($element);

   

in the sub that generates the SQL for an param. It simply flips the 'is_exe_array' flag to on if the param is an array ref and all the rest works as my DB now looks like this;

…

'John', 'HIJ'

'Bill', 'VWX'

'Jane', 'VWX'

'Joe', 'VWX'

...

after I run my test case, not bad. Time for another break, maybe a cookie today?