Moore Moose That I though

It carry on day here in the Moose-Pen

Today I am just going to carry on with a few 'xt' tests in my '25_exe_array.t' test case. Now I started out wanting to do an update with the execute array so I came up with this test;

$da->reset_conditions();
my $update_people = $people->update_people_data();
my $updated_people = $people->updated_people_data();
ok($da->update($dbh,$update_people),"Update Four New Users ");
ok($da->result()->effected == 4,"Four row effected");
Fortunately before I ran this I realized that the above is not going to do what I expect it to do. I think it will just update all the rows in the db four times each wilt an sql like this;

UPDATE people SET id=>?, first_name = ?;
Why I am thinking this is I have no condition on the '$da' I removed it with the 'reset_conditions' and my data from ' update_people_data' looks like this;

    [{id=>6,first_name=>'Tommy'},  
      {id=>7,first_name=>'Tommy2'},  
      {id=>8,first_name=>'Tommy3'},  
      {id=>9,first_name=>'Tommy4'},
     ]
not what I want as I want something like this;

UPDATE people SET first_name = ? WHERE id=>?
Just to see what was going on I gave the above test a run but with the 'da_no_effect' on so nothing would be committed and I got;

Attempt to UPDATE without condition at \GitHub\database-accessor\lib/Database/Accessor.pm line 619.
which is what I sort of suspected as I took the conditions off. Looking at the Driver::DBI code I see in the 'execute' sub I have;

  } 
  elsif ( $action eq Database::Accessor::Constants::UPDATE ) {
          $sql = $self->_update($container);
          $sql .= $self->_where_clause();
  }
So I prepare the update clause first then the where. I know I set the “is_exe_array” flag in the '_update' so I might just be able to make this work;

First let me rewrite my test a little;


$da->reset_conditions();
my $update_ids = $user_db->update_people_id_data();
my $update_people = $user_db->update_people_data();
my $updated_people = $user_db->updated_people_data();

$da->add_condition({
                left => {
                    name => 'id',
                },
                right     => { value => $update_ids},
                operator  => '=',
            });

ok($da->update($dbh,$update_people),"Update Four New Users ");
ok($da->result()->effected == 4,"Four row effected");
This time I have split the '$update_people_data' into two, first the '$update_people_data' which is;

    [{first_name=>'Tommy'},  
     {first_name=>'Tommy2'},  
     {first_name=>'Tommy3'},  
     {first_name=>'Tommy4'},
     ];
and $update_ids which is

     [{id=>6},  
       {id=>7},  
       {id=>8},  
       {id=>9},
      ];
then I add a condition with '$update_ids' as the param then I do update.

Now for the DBI::Driver part.

Again examining the code I think this might work right out of the case as this block of code in the '_field_sql' sub

   }
   elsif ( ref($element) eq "Database::Accessor::Param" ) {

        if ( ref( $element->value ) eq "Database::Accessor" ) {
            my $da = $element->value;
            $da->da_compose_only(1);
            $da->retrieve( $self->dbh() );
            my $sql = $da->result->query();

            foreach my $sub_param ( @{ $da->result->params() } ) {
                $self->add_param(
                    Database::Accessor::Param->new( { value => $sub_param } ) );
            }
            return $sql;
        }
        elsif ( ref( $element->value ) eq "ARRAY" ) {
            $self->is_exe_array(1);
        }
        $self->add_param($element);
        return Database::Accessor::Driver::DBI::SQL::PARAM;
    }
handles the case with my Param is an Array; So I gave it a go and got;
Can\'t bind a reference (HASH(0x5c153d8)) for param 1, entry 0 at
 D:/Perl64/site/lib/DBD/Oracle.pm line 1171
Hmm a an error deep in the DBI code. I think the problem is that '$update_ids' of mine. The bind code of DBI (DBD::Oracle really) does not know what to do with the hash refes in that array.

I remember from some time ago that to do mass updates like this with DBI directly I had to grep out the ids from an array of hashes and then bind the condition with that new array.

So maybe I can just change '$update_ids' to;

   [6,  
     7,  
     8,  
     9, ];
Now lets see what happens when I run the test again?

...
ok 4 - Update Four New Users
ok 5 - Four row effected
Wow I think that actually worked. I had a peek on the DB and I see
+---+---------+--------+----------+ 
| 6 | Atkins  | Tommy  | atkinst  |
+---+---------+--------+----------+ 
| 7 | Atkins2 | Tommy2 | atkinst2 |
+---+---------+--------+----------+ 
| 8 | Atkins3 | Tommy3 | atkinst3 |
+---+---------+--------+----------+ 
| 9 | Atkins4 | Tommy4 | atkinst4 |
+---+---------+--------+----------+ 
So that did work I guess I am smarter than I though; I will have to make some good notes on that on the correct way to call it.

Oh well onto something else for tomorrow.

A-Surprising-Response.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