On Thu, Jun 12, 2003 at 11:55:05AM -0500, Steven Lembark wrote:
> Using the placeholders makes my life simpler since the
> name and value are taken from the same variable but
> DBI handles the stringy/numeric issues for itself. The
> problem starts when admin's have to check why something
> failed at 3am and don't know that the '?' are replaced
> as '500' followed by a naked 500 (for $a) and then the
> date in quotes.
>
> What I need is something like:
> my $a = 500;
> my $date = '11-Jul-1999';
> $string = $sth->interpolated( $sql, $a, $a, $date )
>
> gives me back:
> name = "500"
> and
> value = 500
> and
> date = "11-Jul-1999"
>
> The main issue is being able to walk the bind param. list
> and check if the columns are numeric (naked copy of $a + 0
> inserted) or not (quoted copy of $a).
It's not quite that simple.
> The alternative is having to sprintf every query I use
> for each combination of values and $dbh->do() them for
> large datasets in case any one of them fails (ugh!).
>
> Looking throught he DBI-1.38 pod, the Catalog Methods
> don't have anything quite like this since there is no
> way to query what DBI thinks of the bound parameters
There is the $sth->{ParamValues} attribute.
I could add an $sth->{ParamTypes} attribute. Patches welcome!
[Would be a hash with the same keys as ParamValues. The hash values
would be either an integer SQL_* type value, like SQL_VARCHAR, or
a ref to a hash that included a TYPE => SQL_* type value pair, plus
anything else that was passed at the third arg to bind_param()]
> An ideal would be some sort of $dbh->blah that returned
> the stringified version of whatever query was run last:
>
> die join "\n",
> 'Bad news, boss:',
> $dbh->errstr,
> $dbh->last_query
> ;
>
> If there is someplace w/in the SQL modules that has this
> please warn me, so far wandering through CPAN hasn't
> gotten me anywhere.
It's not done because it's far from trivial to "do it right".
"The Plan" is that the (undocumented) preparse method can be asked
to return the query reformatted into a sprintf string, into which
you can then fairly easily plug the bind values - but you'd have to
look after any quoting for each.
An extra method could then be added to do that for you and then the
driver could override that if it needed to.
Tim.
