An Array of Baby Moose

Its a little harder day here in the Moose-Pen.

Carrying on with adding to the '32_params.t' case I though today I would tackle one of the harder parts of prams and that is passing an array of params or in my case containers into a query statement.

So in my test I could have this;

$container = [{first_name=>'Bill',last_name =>'Bloggings'},
{first_name=>'Jane',last_name =>'Doe'},
{first_name=>'John',last_name =>'Doe'},
{first_name=>'Joe',last_name =>'Blow'},

and what I wold expect to happen on a create would be that all a new recoded would be added for each. In DBI has the 'execute_array' which works with 'bind_param_array' to effect an SQL action on such a set of tuples. DBI works on the above by either the default implimetaion of simple iteration over each other DBDs such ad DBD::Oracle have built in API for such actions.

As MongoDB (and other Key-pair Dbs) has a similar function 'updateMany' adding support for this is well with-in the scope of my project.

Next the test;

$expected = [['Bill','Bloggings'],
$da->create( $utils->connect(),$container);
ok($da->result()->query() eq "INSERT INTO people ( people.first_name, people.last_name ) VALUES( ?, ? )","Array create SQL correct");
for (my $index = 0; $index < $da->result()->param_count; $index++){
"Array create tuple $index are correct"

a simple check of the generated SQL and then iteration over my params to make sure they are all correct;

On my first run I get;

Not a HASH reference at GitHub\database-accessor-driver-dbi\lib/Database/Accessor/Driver/ line 474.

and looking at the offending line I see

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

so I will have to add in a if case before that to handle the situation for an array bind on a create like this;

if (ref($container) eq "ARRAY"){
      my $fields = $container->[0];
      foreach my $key (sort(keys( %{$fields} )) ) {
        my $field = $self->get_element_by_name( $key);

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

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

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

else {
foreach my $key ( sort(keys( %{$container} )) ) {

In the above you will notice I added a new attribute 'is_exe_array' which is just;

has is_exe_array => (
is => 'rw',
isa => 'Bool',
default => 0,

which for now is local to Driver ::DBI. I will use this later on as I have two new paths for the code 'bind_param_array' and then 'execute_array'.

Next I want to know which fields I am binding wich I get off the first row of the container, and then using that I set up the fields array, the same way I would for a normal bind. Next I will need to compile the 'tuples' with another simple iteration and then just fall back into the regular code stream.

The second part happens in the 'exexcute' sub where I use that 'is_exe_array' flag here

if ($self->is_exe_array()){
my $params = $self->params();
foreach my $tuple (@{$params}){
my @tuple = map({$_->value} @{$tuple} );
else {
$result->params([map({$_->value} @{$self->params} )]);

to set up the results params to be a set tuples and then a little further along I use the flag again;

foreach my $index (1..$self->param_count()){
if ($self->is_exe_array()){
else {

in the bind. Finally I use the flag one last time here;

else {
my $rows_effected;
if ($self->is_exe_array()){
my @tuple_status;
$sth->execute_array( { ArrayTupleStatus => \@tuple_status } );
$rows_effected = scalar(@tuple_status);
else {
$rows_effected = $sth->execute();

when I do the execute_array and then return via the 'set' attribute on the 'result' class the status of the various tuples;

And If at this point I run my tests I get;

ok 4 - delete params in correct order
ok 5 - Array create tuple 0 are correct
ok 6 - Array create tuple 1 are correct
ok 7 - Array create tuple 2 are correct
ok 8 - Array create tuple 3 are correct

all great and good but I have the 'da_compose_only' flag set so I never actually get to the execute part of my code. So really I am only testing half of what I should.

Oh well something for tomorrow's post.


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