Re: [Sqlrelay-discussion] SQLRelay, bind_params and DB2

[Lawrence, G. <gla...@uc...>]

Bad form to reply to oneself... But, I've figured out some more information.

It seems the issue is that the perl driver is changing the SQL I pass in.

In SQLRelay.pm there is this line:

        $statement =~ s/\?/":" . ++$count/eg;

which turns every ? in statement into a :#... this seems really undesirable because a '?' shouldn't be interpreted as a bind parameter... but this code does do that and changes things.

It seems there is this:

        $sth->STORE('NUM_OF_PARAMS', $count);


and then later this:

        $sth->STORE('NUM_OF_PARAMS',$cursor->countBindVariables());


So, i'm wondering if count is old buggy code that should be removed. I'm tempted to pull this stuff out and see if it makes things work. Any thoughts?

Gabe

________________________________________
From: Lawrence, Gabriel
Sent: Monday, March 30, 2009 1:58 PM
To: sql...@li...
Subject: SQLRelay, bind_params and DB2

Howdy,

I'm testing out sql relay with a db2 install. I'm doing about the lamest bit of code possible just to make sure it's all going to work.

Code here:

#!/usr/bin/env perl

use DBI;

my $dbh=DBI->connect("DBI:SQLRelay:host=localhost;port=9000;socket=;"," XXXXXXXXXXXX","XXXXXXXXXXXX");
my $sth=$dbh->prepare("select * from db2actq.zip_codes where state = ?");
$sth->bind_param(1,"CA");

$sth->execute();

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

        foreach $col (@data) {
                print "\"$col\",";
        }
        print "\n";
}

$dbh->disconnect;

And I get an error result:
DBD::SQLRelay::st execute failed: [IBM][CLI Driver][DB2/SUN] SQL0312N  The host variable "1" is used in a dynamic SQL statement, a view definition, or a trigger definition.  SQLSTATE=42618

Which seems obvious enough... but, and documentation for SQL0312N has a simple enough resolution:

Use parameter markers (?) instead of host variables for dynamic SQL statements. Do not use host variables and parameter markers in view or trigger definitions.

Except... that’s what I thought I was doing... Am I missing something?

Thanks,
Gabe

------------------------------------
Gabriel Lawrence
Director, IT Security
UC San Diego