replacing ? for '500'
replacing ? for '500'
replacing ? for '12-Jul-2003'
SELECT
foo,
bar
FROM
some_table
WHERE
name='500',
AND value='500',
AND date='12-Jul-2003'
Sorry, but this fails in our database. See, the first use of 500 is in a varchar, the second in an integer. That's the problem: I cannot blindly replace literals in this. The issue is finding a way to replace literal '?' with properly [un]quoted values that are appropriate for the underlying database.
With DBI and bind param's I don't have to worry about quoting. If value is an int then the 500 gets dealt with internally. As Tim pointed out earlier, this is not trivial.
One thing that might work:
$sth->quotish;
or
$dbh->quotish( $sql_with_placeholders );
returns an array of true/false values, one per '?' in the SQL string. If the array entry is true then the bound value will be handled as a stringy value and the literal '?' will have to be replaced with a quoted copy of the bind parameter; false means unquoted.
This would be enough for generating a quotified string with something like:
sub quotificate
{
my $sth = shift or croak "missing statement handle";
my $sql = shift or croak "missing sql statement";@_ or carp "Odd, no bind parameters...";
# no attempt is made here to validate that the
# bind parameters passed on @_ are valid for
# the types (e.g., "hello, world!" in an int
# field. since the point of this is generating
# SQL that matches the original bind parameters. if( my @quotz = $sth->quotish )
{
for( 0..$#quotz )
{
# $a is either a quoted copy of the bind
# parameter or the original.
# could also use shift here if anyone's
# squeamish about $_[$_] :-)my $a = $quotz[$_] ? qq{"$_[$_]"} : $_[$_];
$sth =~ s/\?/$a/;
}
}
else
{
# no bind parm's in the original, nothing
# more to do with it. log_message "No bind parameters to replace";
} # caller gets back the original statement handle
# with '?' hacked into properly quoted strings --
# which may be the same string if there were no
# '?' in it. $sth
}
Question: where in the guts of DBI is the mapping of bind parameters to quotish behavior done?
-- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 888 359 3508
