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

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)});
[download]

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);
}
[download]

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...

Comment on Re: DBI execute() args in array format (or similar) possible? Select or Download Code

Replies are listed 'Best First'.
Re: Re: DBI execute() args in array format (or similar) possible? by dws (Chancellor) on Aug 12, 2002 at 22:37 UTC
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.	[reply] [d/l] [select]
Re: Re: Re: DBI execute() args in array format (or similar) possible? by snafu (Chaplain) on Aug 13, 2002 at 03:53 UTC
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)});` [download] 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...	[reply] [d/l] [select]
Re: Re: Re: Re: DBI execute() args in array format (or similar) possible? by dws (Chancellor) on Aug 13, 2002 at 04:36 UTC
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.	[reply] [d/l] [select]
Re: Re: Re: Re: Re: DBI execute() args in array format (or similar) possible? by snafu (Chaplain) on Aug 14, 2002 at 17:08 UTC
Re: Re: DBI execute() args in array format (or similar) possible? by perrin (Chancellor) on Aug 12, 2002 at 22:26 UTC
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?	[reply]
Re: Re: Re: DBI execute() args in array format (or similar) possible? by snafu (Chaplain) on Aug 12, 2002 at 22:33 UTC
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` [download] 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...	[reply] [d/l] [select]


"be consistent"
	PerlMonks