problem with DBD::DB2 using bind and LOWER

[Jeff Nucciarone]

I am running into a problem with DBD::DB2 when I am trying to bind a
parameter that is in a LOWER() or LCASE() function.

I get an error SQL0418N on the prepare:

DBD::DB2::db prepare failed: [IBM][CLI Driver][DB2/LINUX] SQL0418N  A
statement contains a use of a parameter marker that is not
valid.  SQLSTATE=42610

The source line looks something like this:

 SELECT foo FROM bar WHERE (lower(foo) LIKE lower(?));

However this will work:

 SELECT foo FROM bar WHERE (lower(foo) LIKE ?);                                        
        

It appears that bind parameters are not allowed to appear in functions?

Thanks,

  --Jeff

----[begin included file]----
#!/usr/bin/perl
#

#
use DBI;
use Env qw(DB2INSTANCE LD_LIBRARY_PATH);
$LD_LIBRARY_PATH="/usr/IBMdb2/V7.1/lib";
#

my $chrom;

my (@bind_vars);
    $bind_vars[0] = "%MIL1 protein%";

# create query string - uncomment the one you wish to test

# The following works

$sql = "SELECT chrom, start_pt, stop_pt FROM genes WHERE  (lower(chrom) LIKE ?)";

# This one does not

#$sql = "SELECT chrom, start_pt, stop_pt FROM genes WHERE  (lower(chrom) LIKE 
lower(?))";

# Get database passwd from cmd line

system "stty -echo";
print "Password: ";
chomp($word = <STDIN>);
print "\n";
system "stty echo";

#connect to software database

my $dbh=DBI->connect("DBI:DB2:gala",'gala',$word)
 or die "Cannot connect to DB2 database $DBI::errstr\n";

print "Conection successful, about to try query\n";


      #separate out needed bind_vars
      my(@n) = ($sql =~ m/\?/g);  #number of ? in sql
      my(@binds) = splice(@bind_vars, 0, ($#n + 1));

      eval {
         $sth = $dbh->prepare($sql);
         my(@foo) = @{ $sth->{NAME} };
         $sth->execute(@binds);
      };

while ((@result)=$sth->fetchrow_array()){

        #get values obtained from database into variables
        
        $chrom=$result[0];
        print "Value read was $chrom\n";
}
$sth->finish();

$dbh->disconnect;

print "Disconnect successful\n";
----[end included file]----