Re: DBI execute() args in array format (or similar) possible?

[snafu]

Just so everyone knows...I was able to solve my problem with the following code-bit...

I finally able to figure that since all the elements were correct, despite what some said (although, it is a good idea to check and make sure...which I do in other subroutines and I intend to do a more thorough check/match on the number of elements in the array to the num of '?' used in the query) that I should be able to pass that info onto the execute method. Anyhoo, after thinking about it and doing some tinkering, I finally got it to work out right. /me loves eval.

I also took the advice by perrin and fixed my var declarations for the shift statements.

Thanks again, everyone, for your input.

The magic line of code that solved my problem...

    $STH->execute(eval{join(',',@args),return(@args)});

The code (sub routine) altogether now:

sub get_data {
    my $statement     = shift;
    my $clli_ref_args = shift;
    my $hour_ref_args = shift;
    my $date_ref_args = shift;
    my ($start_date, $end_date);
    my (@results,@args);

    push(@args,@$date_ref_args) if ( $date_ref_args );
    push(@args,@$clli_ref_args) if ( $clli_ref_args );
    push(@args,@$hour_ref_args) if ( $hour_ref_args );

    print "Preparing statement: $statement\n";
    print "running with args  : @args\n\n";
    $STH            = $DBH->prepare($statement)
                          or warn("Unable to prep statement.\n");

    $STH->execute(eval{join(',',@args),return(@args)});

    while ( my @arr = $STH->fetchrow_array() ) {
        print @arr,"\n";
        push(@results,@arr);
    }

    $STH->finish();

    print "@results\n";
    return(1);
}

I think I am going to run into some documentation that will assist me more in what it is I was trying to accomplish. Because, I think I read something today that looks like it is doing the same thing I was doing; calling the execute with an array with the data in it that should be executed. However, I haven't finished reading that stuff yet, so I am sticking with what I have until I get more info.

Feel free to continue to correct and offer suggestions. It is quite helpful and may spark a discussion that is useful for all.

_ _ _ _ _ _ _ _ _ _
- Jim
Insert clever comment here...

[perrin]

Whoa! What is that and why are you doing it? This just doesn't make sense to me. You are supposed to pass an array to @args, not a comma-separated string. What's in your @args array?

[snafu]

lol...Ok. The array by itself didn't work. Here is some better output:

$perl * -d 01-Jul-2002 -c uslecatl5e1
Preparing statement:
 select
     clli, traffic_date, count(distinct(traffic_hour))
  from
     gvc_traffic30
 where
    traffic_date in (?)
      and
    clli         in (?)
      and
    traffic_type = 'N'
group by
     clli, traffic_date, traffic_hour

running with args  : 01-Jul-2002 uslecatl5e1

For all those that question...@args is: 
        0 :01-Jul-2002
        1 :uslecatl5e1

It was the only way I could match the number of args that I needed to send to execute with the number of '?'s that I prep'ed $statement with.

_ _ _ _ _ _ _ _ _ _
- Jim
Insert clever comment here...

[dws]

The magic line of code that solved my problem...   $STH->execute(eval{join(',',@args),return(@args)});

Say what? That's equivalent to writing   $STH->execute(@args); The result of the join() is discarded.

I rather think something else is going one.

[snafu]

It can't be the same because the script didn't work with execute(@args) but it does work with

execute(eval{join(',',@args),return(@args)});

*shrug* I don't know why...it just does.

One possible explanation is that the execute method is looking for separate arguments and not a list. Thus, I am taking a list and turning it into arguments.

summary of what I think is happening: The execute() method wants distinct arguments ie
execute(arg1,arg2,arg3) vs execute(qw(arg1 arg2 arg3)) which is the same, of course, as execute(@args). The latter 2 examples don't work possibly because its a list instead of separate and distinct scalars respective to the number of place holders in the prepare() statement.

Does execute() not work this way?

Updated: Fixed syntax mistake with qw().

_ _ _ _ _ _ _ _ _ _
- Jim
Insert clever comment here...

[dws]

Brother snafu, I fear you have stumbled onto a series of incantions that appear to work, but that you still do not understand the underlying problem. Consider:   eval {join(',', @args), return(@args)} has the same affect as   eval {return(@args)} since the join() has no side-effect. In turn, these have the same effect as     eval {@args} and   @args

The execute() method wants distinct arguments ie execute(arg1,arg2,arg3) vs execute(qw(arg1,arg2,arg3)) which is the same, of course, as execute(@args).

if @args hold (1,2,3), then

• execute(1,2,3);
• execute(qw(1 2 3));
• execute(@args)
are equivalent, but
• execute(qw(1,2,3));
is not equivalent, since qw(1,2,3) is an list that contains a single element. It's the whitespace that's significant in qw(), not the commas.