Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

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

by snafu (Chaplain)
on Aug 12, 2002 at 19:23 UTC ( #189594=perlquestion: print w/replies, xml ) Need Help??

snafu has asked for the wisdom of the Perl Monks concerning the following question:

I am dynamically creating an Oracle SQL statement based from $ARGV and then I create an excel spreadsheet from the return of the data from the statement.

Now, in creating this dynamic SQL statement I create the statement and attempt to plug in the values for the unknowns in the statement via the DBI::execute() method (it is a method, right? :) I always get the terminology wrong, so if I did, please forgive me) from the arguments off the command line. *This* is where I get my error. See below...

You will notice in the Perl code below that I am trying to execute that statement with the following:

Preparing statement: select clli, traffic_date, count(distinct(traffic_hour)) from gvc_traffic30 where traffic_date in (?) and clli in (?,?) and traffic_hour in (?) and traffic_type = 'N' group by clli, traffic_date, traffic_hour running with args : 01-Jul-2002 switch1 switch2 10
Which is generated from:
traffic_est_report.pl -c switch1,switch2 -d 01-Jul-2002 -H 10
I get the error:

execute called with 1 bind variables when 4 are needed at traffic_est_ +report.pl line 467.
Now, I know why I am getting this error; because I am trying to call execute with the args as an array vs listing the args out manually. I tried to call the function with a join() attempting to join @args with commas effectively turning the args into a scalar separated by commas but I couldn't fool the execute command this way.

So, I ask my fellow monks this simple question...What do you suggest I do? I will never know exactly what the end user wants, so there really are no definites. The dynamic-ness of this is quite nice. This issue is the only stumbling block I have.

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(@args); while ( my @arr = $STH->fetchrow_array() ) { print @arr,"\n"; push(@results,@arr); } $STH->finish(); print "@results\n"; return(1); }

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

Replies are listed 'Best First'.
Re: DBI execute() args in array format (or similar) possible?
by dws (Chancellor) on Aug 12, 2002 at 19:37 UTC
    If you're going to build up bind parameter values by pushing them onto an array, you have to be extra careful to push the right number. Your query has 4 bind markers. You must supply 4 values. If there are 4 values in the array, DBI won't complain that it's seeing one.

    Check the size of @args. I'll bet you have an upstream problem with command-line argument processing.

      This is correct. In fact, the size of args is the right number of elements. Look at the output I placed. @args has 4 elements which are suppose to be the elements I am trying to send to execute(). However, it was pointed out to me (and of course this was just an oversight of mine) that execute sees only one bind...the array. Of course, like I said, I tried to trick execute with a join(',',@args) but that didn't work For the record, I did try the join() before trying to send the array by itself since it made sense to me that execute() needed separate args. I just tried sending the list by itself as a measure of troubleshooting, just in case.

      I am ultimately going to have to find a different way to pass these statements to DBI. I am reading the article suggested by aufrank and taking screamingeagle's advice on some things as well.

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

        Look at the output I placed. @args has 4 elements which are suppose to be the elements I am trying to send to execute().

        Are you sure? The only diagnostic I see is printing "@args\n", which stringifies the array, making it impossible to tell whether you have 4 elements or 1. That's why I suggested that you check the array size.

        For the record, I did try the join() before trying to send the array by itself since it made sense to me that execute() needed separate args.

        Are you sure there aren't lingering traces of this join() in some other place in the code? Lingering traces of prior code experiments are a frequent source of bugs (mine, at least).

Re: DBI execute() args in array format (or similar) possible?
by aufrank (Pilgrim) on Aug 12, 2002 at 19:52 UTC

    Jim--
    I'm starting to feel like a broken record, because most of the SoPWs I answer are about DBI, and my advice usually consists of either:

  • read the docs (which I assume you've done :)
  • consider using $dbh->quote() and $dbh->quote_identifier(), which it appears you haven't.
  • The whole reason I discovered these two methods was in trying to prepare dynamic sql statements, much like you're doing here. I would suggest that you try to apply $dbh->quote_identifier() to the names of any tables or columns, and $dbh->quote() to any values you're using. Try to do the join after these methods have been applied, and you might be successful.

    One consideration is that you seem to be using placeholders. While this is encouraged and can greatly speed up your queries, I've found that it's sometimes difficult to generate queries that are dynamic both at the level of the structure of the query (table names and columns) and at the specific values in the query (using ? as a placeholder for values). While the docs say that specifying a value as a parameter to $dbh->execute() will automatically quote it appropriately, I just haven't had much luck with my attempts. If you continue to have problems, consider rewriting the queries to use explicit values instead of placeholders. Not optimal, but if it works it might give you a place to start debugging from (and a functioning application! :)

    No post of mine regarding DBI would be complete without referring to chromatic's DBI is OK, which was my most valuable resource in moving out of the beginner stages of using DBI. He uses clever quoting and map combinations to dynamically generate queries, and also talks about using placeholders. You should definitely check it out if you haven't.

    good luck with your project, feel free to reply with any questions
    --au

      Um, I have never had any problems with placeholders quoting things correctly. If you think the quoting has bugs, please report them with examples to the DBI list. I don't think they do, and placeholders are definitely preferred.
      While the docs say that specifying a value as a parameter to $dbh->execute() will automatically quote it appropriately, I just haven't had much luck with my attempts.

      Which DBD are you using, and can you provide an example of where execute() fails to quote appropriately?

        apparently I was very wrong... any problems I had previously were simply a result of my own insuffiencies. I had always assumed this to be the case, but I mentioned my problems because the only solution I came to when I couldn't get placeholders to work was to take them out and thought the original poster might come to a similar place.

        The problems I was referring to were with using DBI and the ODBC DBD to perform UPDATE statements on MS Access DBs using placeholders. I just wrote up a test statement, though, and had no problems. Sorry to pass bogus information, perhaps this code can help the original poster anyway...

        my @updates = map {$dbh->quote_identifier($_) . ' = (?)'} ('call 1 com +ment', 'call 2 comment', 'call 3 comment', 'call 4 comment'); my $name_field = $dbh->quote_identifier('last name'); my $update_string = join ", ", @updates; my $sql = qq{UPDATE $table SET $update_string WHERE $name_field = (?)} +; print "$sql\n"; my $sth = $dbh->prepare_cached($sql); $sth->execute('foo','bar','baz','qux','smith');

        I was thoroughly wrong, but maybe the code will end up helping where my previous post couldn't :)
        --au

      read the docs (which I assume you've done :)

      Yes sir...sure have. Although, admittedly, a lot of that is a bit over my head and unfortunately, as many of us do, I don't have the time to truly study everything in the docs :(.

      consider using $dbh->quote() and $dbh->quote_identifier(), which it appears you haven't

      right again! :)

      Your suggestions have been quite helpful.

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

Re: DBI execute() args in array format (or similar) possible?
by perrin (Chancellor) on Aug 12, 2002 at 20:01 UTC
    Now, I know why I am getting this error; because I am trying to call execute with the args as an array vs listing the args out manually.

    No, I think you're getting this error because there is only one thing in your @args array. Your calling syntax looks correct.

    By the way, it's preferable to say this:

    my ($statement, $clli_ref_args, $hour_ref_args, $date_ref_args) = @_;
    Or if you prefer to use multiple calls to shift, don't put the scalar variable name in list context the way you have it now.
Re: DBI execute() args in array format (or similar) possible?
by snafu (Chaplain) on Aug 12, 2002 at 22:02 UTC
    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...

      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.

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

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

Re: DBI execute() args in array format (or similar) possible?
by chromatic (Archbishop) on Aug 12, 2002 at 22:16 UTC
    I tried to call the function with a join() attempting to join @args with commas effectively turning the args into a scalar separated by commas but I couldn't fool the execute command this way.

    I've been thinking about this for the past five minutes (waiting on another database), and I cannot find any way where this makes sense to me. If you need four arguments, why would you expect that gyrating to pass only one argument would work? (My best guess is that you see a similarity between "1, 2, 3" and (1, 2, 3) where, semantically, none exists.)

    I'm willing to bet that a judicious use of split would solve your problem. I'll even give you two better diagnostics to prove that @args doesn't contain what you think it contains:

    local $" = ')('; print "Results contains: ", scalar( @args ), " elements:\n"; print "(@args)\n";

    Update: Replaced @results with @args, as it should have been from the start.

      I'm not really concerned about @results right now. Im only concerned with what I send $STH->execute()

      Perhaps we are having a miscommunication?

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

Re: DBI execute() args in array format (or similar) possible?
by screamingeagle (Curate) on Aug 12, 2002 at 19:49 UTC
    In your specific case, instead of using placeholders, you could try building the SQL SELECT stmt based on the number of arguments present. For example,
    $sql = "select clli, traffic_date, count(distinct(traffic_hour)) from gvc_traffic30 where 1=1 "; $sql .= " and traffic_Date in ($date_ref_args) " if ( $date_ref_args ) +; ...etc...
    The "1=1" part enables you to add a dynamic number of "And " clauses without any problems...
    hth

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://189594]
Approved by dws
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (3)
As of 2023-02-03 00:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    I prefer not to run the latest version of Perl because:







    Results (24 votes). Check out past polls.

    Notices?