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;

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;

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/ 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;

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();

                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;

and $update_ids which is

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->retrieve( $self->dbh() );
            my $sql = $da->result->query();

            foreach my $sub_param ( @{ $da->result->params() } ) {
                    Database::Accessor::Param->new( { value => $sub_param } ) );
            return $sql;
        elsif ( ref( $element->value ) eq "ARRAY" ) {
        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/ 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;

     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.


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