Another Baby Moose Break

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?

HPIM6682a.jpg

Leave a comment

About byterock

user-pic Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations