Re: Query formatting problem

[Ian Harisay]

Hi,

Here is a code snippet that will accomplish your desired task.  This is 
not meant to be generic.  It does have some limitations.  It will 
satisfy the problem presented.  How you integrate it with you code will 
be up to you.

Regards,

Ian

#!/usr/bin/perl

use strict;
use DBI;
my @login = ( "dbi:Oracle:$sid", "$user", "$passwd" );
my $db_attr = {
 RaiseError => 1,
 PrintError => 0,
 AutoCommit => 0
};
## Create an object handle for Feed::Database
my $dbh = DBI->connect( @login, $db_attr ) || die $DBI::errstr;
my $sql = <<EOS;
 SELECT
   foo,
   bar
 FROM
   some_table
 WHERE
   name=?,
   AND value=?,
   AND date=?
EOS
print interpolate($dbh, $sql, 500, 500, '12-Jul-2003'),$/;

$dbh->disconnect;

#
# scalar interpolate( DBI::db dbh, scalar sql, array @_ )
#
sub interpolate {
 my $dbh = shift;
 my $sql = shift;
 foreach (@_){
   my $val = $dbh->quote($_);
   print "replacing \? for $val$/";
   $sql =~ s/\?/$val/;
 }
 return $sql;
}
output produced:

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'