Re: Query formatting problem

[Ian Harisay]

I did this example with a Oracle connection. I am using the quote method 
from my database handle. The quoting will be specific to your database. 
I just tried it with MySQL. No problem there either. What db are you 
using? What errors is it throwing?

Steven Lembark wrote:


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