On Thu, 12 Jun 2003 11:55:05 -0500 Steven Lembark <[EMAIL PROTECTED]> wrote:
> I'm trying to find a way of using DBI's internal knowlege
> of how bind var's are managed to format a working query
> for error messages.
It varies from one DBD driver to another. You don't mention which DBD
you are using, so I'm using DBD::Oracle as an example.
> A truncated example (multiple sub-queries removed) is:
>
> select
> foo,
> bar
>
> from
> some_table
>
> where
> name = ?
> and
> value = ?
> and
> date = ?
>
>
> run as:
>
> $sth->execute( $a, $a, $today );
>
> Yes, $a is used twice, in one case it is compared to a
> number, the other it used as a string.
That is fine since you have 3 placeholders.
> 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 $string = $sth->interpolated( $sql, @bindlist );
>
> which called as:
>
> my $a = 500;
>
> my $date = '11-Jul-1999';
>
> $string = $sth->interpolated( $sql, $a, $a, $date )
>
> gives me back:
>
> select
> foo,
> bar
>
> from
> some_table
>
> where
> name = "500"
> and
> value = 500
> and
> date = "11-Jul-1999"
>
In Oracle, value and date are reserved words. Name might be too.
For DBD::Oracle you really get something like:
SELECT foo, bar
FROM some_table
WHERE name = :1
AND value = :2
AND date = :3
I normally convert that to a SQL*Plus script for testing. That way
I am still using bind variables and don't miss problems caused by
them. It also makes changing the values easier.
>From memory and not tested:
VARIABLE p1 VARCHAR2
VARIABLE p2 NUMBER
VARIABLE p3 DATE
BEGIN
:p1 := "500";
:p2 := 500;
:p3 := TO_DATE( '1999-07-11', 'YYYY-MM-DD' );
END;
/
SELECT foo, bar
FROM some_table
WHERE name_c = :p1
AND value_c = :p2
AND date_c = :p3
/
> 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).
>
> 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!).
If you don't know what the data types you are comparing to are, you
have bigger problems than the query format.
--
Mac :})
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
