In a former programming life in a different programming language, placeholders of the ":placeholder" type were bound by reference to variables in the program. With DBI, we generally use bind_param() to bind by value a copy of some variable. But bind_param_inout binds by reference, so I was thinking what if I go ahead and use that anyway, e.g.:
my @cols = qw(foo bar);
my $sql = <<SQL;
SELECT :foo, :bar FROM dual
SQL

my $sth = $dbh->prepare($sql);

my %hsh;
for (@cols) {
  $sth->bind_param_inout( ":$_" => \$hsh{$_}, 0 );
}

# Set constants...
$hsh{foo} = 'abc';

# Set changing values
$hsh{bar} = 123;
$sth->execute();
while (my @arr = $sth->fetchrow_array) {
  print "@arr\n";
}

$hsh{bar} = 456;
$sth->execute();
while (my @arr = $sth->fetchrow_array) {
  print "@arr\n";
}

$dbh->disconnect();

[download]
And it worked (this was for Oracle, thus, the 'FROM dual'). This is, of course, just an example SQL statement, but may also be most useful for others, like INSERT statements...

Replies are listed 'Best First'.
Re: DBI bind_param_inout trick
by mje (Curate) on Sep 03, 2012 at 16:11 UTC

    Just out of interest I tried this with DBD::ODBC after removing the "from dual" and changing the bind_param_inout calls to remove the ':' (which is not supported in DBD::ODBC - see note in DBD::ODBC). The output is worryingly different from DBD::Oracle:

    DBD::Oracle output:
abc 123
abc 456

    [download]
    DBD::ODBC output:
 123
 456

    [download]
[reply]
[d/l]
[select]

      Looks like a bug in DBD::ODBC. Parameters are bound initially in ODBC at bind time and if they change at execute time they have to be rebound ODBC-wise. The following code is what tests whether a parameter needs to be rebound:

      if (SvTYPE(phs->sv) != phs->sv_type /* has the type changed? */
   || (SvOK(phs->sv) && !SvPOK(phs->sv)) /* is there still a string? *
+/
   || SvPVX(phs->sv) != phs->sv_buf /* has the string buffer moved? */
                ) {
                if (!rebind_param(sth, imp_sth, imp_dbh, phs))
                    croak("Can't rebind placeholder %s", phs->name);
               }

      [download]

      Trouble is I didn't write that code which attempts to rebind the parameter ODBC-wise and although I understand the first and last test in the if condition the comment next to "(SvOK(phs->sv) && !SvPOK(phs->sv))" does not seem to agree with the code. I wonder what the original author meant. It also does not cater for a parameter being undef then a string.

[reply]
[d/l]
[reply]
Re: DBI bind_param_inout trick
by locked_user sundialsvc4 (Abbot) on Aug 22, 2012 at 22:21 UTC

    Dunno ... while it might work, it bothers me nevertheless ... and here is why.   Now, we have a dependency, introduced into any particular statement, upon “what statement or statements, whatever they might have been(!), that have come before.”   I-f that prior sequence of events is both “known,” and furthermore, “known to be entirely correct in all cases,” then Life Is Good.™   Otherwise, all bets are off and we have a whale of an (unnecessary...) debugging problem.   No, I don’t feel good about that ... the more I think about it ... not at all.   I feel that we have opened up a can-of-worms that, like all such cans, is better left tightly sealed.   (And I know that you know full well and as a matter of course what I mean, and what I don’t mean.)

      I wasn't all that sure about it either, but it worked, so I shared. And I might use it in a pattern, where, I often insert from some hash in some loop, and I can set some constant values before the loop, and then set the changing values and execute the statement within the loop. I don't expect any more debugging problem there than I've ever had before, but we'll see... ;-)
[reply]

        Here is a pattern that I'm pretty sure would break it: 

        if( ... ) {
    # Over-ride default just for now:
    local $hsh{status} = 'backlog';
    $sth->execute();
    ...
}

        [download]

        Just FYI, as the most mundane thing that occurred to me when trying to think of ways this might break or just surprise someone. delete would also be a no-no (but I found that unlikely to be a 'trap').

        Hash values are actually pretty "stable" regarding references to them, as far as I can imagine. :) (But, yes, I'd only use this trick within a fairly small scope.)

        - tye        

[reply]
[d/l]

Back to Meditations