byterock

Well it is look at another module day here in the Moose-Pen.

So yesterday I ran into this problem;

DBD::DBM::st execute_array failed: 4 bind values supplied but 2 expected [for Statement "INSERT INTO user ( user.address, user.username ) VALUES( ?, ? )"] at …

Which rather peeved me as I tried the same code on two other DBs and the both ran. So it it just me? I took a look at example in the DBI POD;

$dbh->{RaiseError} = 1;        # save having to check each method call

$sth = $dbh->prepare("INSERT INTO staff (first_name, last_name, dept) VALUES(?, ?, ?)");

$sth->bind_param_array(1, [ 'John', 'Mary', 'Tim' ]);

$sth->bind_param_array(2, [ 'Booth', 'Todd', 'Robinson' ]);

$sth->bind_param_array(3, "SALES"); # scalar will be reused for each row

$sth->execute_array( { ArrayTupleStatus => \my @tuple_status } );

unfortunately with DBD::DBM I only have two fields so I had to adapted the above code to this;

$dbh->{RaiseError} = 1;        # save having to check each method call

$sth = $dbh->prepare("INSERT INTO staff (first_name, last_name) VALUES(?, ?)");

$sth->bind_param_array(1, [ 'John', 'Mary' ]);

$sth->bind_param_array(2, [ 'Booth', 'Todd']);

$sth->execute_array( { ArrayTupleStatus => \my @tuple_status } );

and ran it in a test script and it ran with no problems??. Looking at my test the only real difference was I had four rows to bind vs two in the example above so in my test I did this

$container = [{username=>'Bill',address =>'ABC'},

              {username=>'Jane',address =>'DEF'},

++##               {username=>'John',address =>'HIJ'},

++##               {username=>'Joe',address =>'KLm'},

              ];

and then I got all my tests to pass.
Time to have a look at DBI. So I did the usual get the latest version of DBI and install it on a very clean box and then start poking about.
I first had a look at the very limited test for it

ok( $sth->bind_param_array(4, [ qw(a b c) ]), '... bind_param_array should return true');

ok( $sth->execute_array({ ArrayTupleStatus => $tuple_status }), '... execute_array should return true');

found in 15array.t. Doing some debugging directly on the DBI.pm I was eventually able to discover that the bind is per param and not per array. So my code work because I just happen to as have a many rows in my array as I had params.
Going back to my test code I had a look at the end result on the DB which where;

     +-------------+-----------+

     | username    | address   |

     +-------------+-----------+

     | Bill        | Jane      |

     | ABC         | DEF       |

     +-------------+-----------+

and then I took a closer look at the DBI example and the data are the same way around. The first bind is to the 'first_name' field and all the items in that array are 'fist names'.
Eventually after I did a few searches and found one of my old posts I realized there is no bug in DBI only a problem in my code.

I also wondered why the four row array worked in DBD::Oracle and Postgress??
Turns out it didn't I inserted only two records in the same manner as above, I just forgot to turn on the error handling flag and with that on, the code for both DBs dies with the same original error '4 bind values supplied'.
So good news for Tim bad news for me because now I have to rewrite my code a little.
What I need to do in Driver::DBI is take this container

$container = [{username=>'Bill',address =>'ABC'},

              {username=>'Jane',address =>'DEF'},

              {username=>'John',address =>'HIJ'},

               {username=>'Joe',address =>'KLM'},

              ];

and transform it into this this array ref

$params =[

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

          ['ABC','DEF','HIJ','KLM']

          ]

which I then bind to the two '?' in the SQL

INSERT INTO user ( user.address, user.username ) VALUES( ?, ? )

In the end all I had to do was rewrite some of the code in the '_insert' sub a little;

...

if (ref($container) eq "ARRAY"){

      $self->is_exe_array(1);

      my $fields = $container->[0];

      foreach my $key (sort(keys( %{$fields} )) ) {

        my $field = $self->get_element_by_name( $key);

        next

         if(!$field);

        push(@fields,$field);

        push(@field_sql, $self->_element_sql($field));

 ++     $self->add_param([]);

      }

      foreach my $tuple (@{$container}){

--       my @params = (); 

++       my $index = 0;

         foreach my $field (@fields){

           my $param =  Database::Accessor::Param->new({value=> $tuple->{$field->name()}});

++         push(@{$self->params->[$index]},$param);

++         $index++;

         }

--        $self->add_param(\@params);  

       }

       my $params = $self->params();

      

    }

    else {

...

All I did above is first pre-fill the params attribute with an empty array-ref for each field in the container. As before I iterate over the container and then push the tuple param value into proper position in the array.

I did not need to change any of the later code, the end results was my tests ran fine.
Oh well I guess it has been a few years since I last used execute_array and forgot how to use it or maybe I am rushing things too fast?