Thanks for the info and the patch!
Tim.
On Fri, Oct 17, 2003 at 02:07:46PM +0100, Charles Jardine wrote:
> The problem:
> -----------
>
> When Oracle matches placeholder names passed to OCIBindByName()
> with the actual placeholders in the SQL or PL/SQL, it uses
> a case-insensitive comparison. This means that Oracle regards
> the placeholders :a and :A as the same.
>
> On the other hand, DBD::Oracle uses case-sensitive comparison
> when matching placeholder names, and regards :a and :A as
> different.
>
> This has at least two undesirable effects.
>
> 1. If the statement contains two named placeholders which
> differ only in letter_case, they will each contribute
> to the count in $sth->{NUM_OF_PARAMS}, despite the fact
> that this is meant to be a count of _distinct_
> placeholders.
>
> 2. The name passed to $sth->bind_param... must match in
> letter case at least one of the occurences of the
> placeholder in the statement. This is not what an
> OCI programmer would expect. Many of the examples in
> the OCI manual have the names passed to OCIBindByName()
> in upper case, and those in the PL/SQL in lower case.
>
> Below is a tiny perl program which demonstrates this.
>
> The proposed solution:
> ---------------------
>
> I suggest that DBD::Oracle's handling of placeholder name
> comparisons should be changed to be case-insensitive.
>
> Below is a patch to dbdimp.c (version 1.14) which achieves
> this by lower-casing the name both at preparse time and at
> bind time.
>
> ============================
> #!/bin/perl -w
> use strict;
> use DBI;
>
> my $dbh = DBI->connect('dbi:Oracle:', 'foo', 'bah',
> {RaiseError=>1, PrintError=>0, AutoCommit=>0});
> my $sth = $dbh->prepare(
> "SELECT decode(:a, :A, 'same', 'different') from dual");
>
> # The following prints 2, indicating that DBD::Oracle
> # thinks that the above has two distinct placeholders
>
> print $sth->{NUM_OF_PARAMS}, "\n";
>
> $sth->bind_param(':a', 'lower');
> $sth->bind_param(':A', 'upper');
> $sth->execute;
>
> # The following prints 'same', indicating that Oracle
> # can't see the difference between the placeholders
>
> print +($sth->fetch)->[0], "\n";
>
> $sth->finish;
> $dbh->disconnect;
> =============================
> --- dbdimp.c.orig Fri Oct 17 13:08:17 2003
> +++ dbdimp.c Fri Oct 17 13:08:49 2003
> @@ -812,7 +812,7 @@
>
> } else if (isALNUM(*src)) { /* ':foo' */
> while(isALNUM(*src)) /* includes '_' */
> - *dest++ = *src++;
> + *dest++ = toLOWER(*src), src++;
> style = ":foo";
> } else { /* perhaps ':=' PL/SQL construct */
> /* if (src == ':') *dest++ = *src++; XXX? move past '::'? */
> @@ -1321,7 +1321,7 @@
> SV **phs_svp;
> STRLEN name_len;
> char *name = Nullch;
> - char namebuf[30];
> + char namebuf[32]; /* ':' + 30 char name + '\0' */
> phs_t *phs;
>
> /* check if placeholder was passed as a number */
> @@ -1329,7 +1329,13 @@
> if (SvGMAGICAL(ph_namesv)) /* eg if from tainted expression */
> mg_get(ph_namesv);
> if (!SvNIOKp(ph_namesv)) {
> + int i;
> name = SvPV(ph_namesv, name_len);
> + if (name_len>31)
> + croak("Placeholder name too long (%s)", neatsvpv(ph_namesv,0));
> + for (i=0; i<name_len; i++) namebuf[i] = toLOWER(name[i]);
> + namebuf[i] = '\0';
> + name = namebuf;
> }
> if (SvNIOKp(ph_namesv) || (name && isDIGIT(name[0]))) {
> sprintf(namebuf, ":p%d", (int)SvIV(ph_namesv));
>
> ===================================================
>
> --
> Charles Jardine - Computing Service, University of Cambridge
> [EMAIL PROTECTED] Tel: +44 1223 334506, Fax: +44 1223 334679
>
