Moore Moose That I though

By byterock on October 12, 2018 9:00 PM

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;

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.