> Date: Fri, 30 Mar 2012 09:10:41 +0100
> From: martin.ev...@easysoft.com
> To: john...@pharmacy.arizona.edu
> CC: dbi-users@perl.org
> Subject: Re: Error I've not seen before from oracle DBD
>
> On 30/03/12 09:02, Martin J. Evans wrote:
> > On 29/03/12 22:10, Bruce Johnson wrote:
> >>
> >> On Mar 29, 2012, at 1:58 PM, Bill Ward wrote:
> >>
> >>> Try not using numbers for the placeholder names?
> >>>
> >>
> >> That worked...
> >>
> >> my $sq_res_pend = "insert into reservations_pend (pid, email, cn,
> >> purpose, reserver_affstring) values(:A,:B,:C,:D,:E) returning
> >> reservations_pend_id into :NEWID";
> >>
> >> my $csr_res_pend = $lda->prepare($sq_res_pend) or die
> >> $DBI::errstr; $csr_res_pend->bind_param(':A',$res_pid) or die
> >> $DBI::errstr; $csr_res_pend->bind_param(':B',$res_email) or die
> >> $DBI::errstr; $csr_res_pend->bind_param(':C',$res_name) or die
> >> $DBI::errstr; $csr_res_pend->bind_param(':D',$res_purp) or die
> >> $DBI::errstr; $csr_res_pend->bind_param(':E',$res_affil) or die
> >> $DBI::errstr;
> >> $csr_res_pend->bind_param_inout(":NEWID",\$new_res_id, 25) or die
> >> $DBI::errstr;
> >>
> >> $csr_res_pend->execute();
> >>
> >> Got right through.
> >>
> >> Is this a bug or a rule I'm not aware of?
> >>
> >>
> >
> > It is a rule in so far that, that is what the code says. Placeholders
> > of the form :N (where N is a number) are deemed different from
> > placeholders of the form :A (where the first chr of A is not a
> > number) are deemed different from placeholders of the form ?.
> >
> > Looking at the code (which I did not write) :N is a special case in
> > that you cannot have multiple Ns in the SQL where :A forms can be
> > repeated in the SQL. The ? form is just changed to :N internally in
> > the code.
>
> Correction. :N is a special case because you can omit the ":N" from the
> bind_param call and simply call bind_param(N,...). e.g.,
>
> my $s = $h->prepare(q/insert into mje (a,b) values(:1, :1)/);
> $s->bind_param(1, 1); # you don't use ":1"
>
> The :N form can be repeated as above. I'll try and find some time to update
> the pod.
> Boy that is a very old hunk of code. funny this never came up till now.
> cheersJohn
> > The relevant code is:
> >
> > if (*start == '?') { /* X/Open standard */ sprintf(start,":p%d",
> > ++idx); /* '?' -> ':p1' (etc) */ dest = start+strlen(start); style =
> > "?";
> >
> > } else if (isDIGIT(*src)) { /* ':1' */ idx = atoi(src); *dest++ =
> > 'p'; /* ':1'->':p1' */ if (idx <= 0) croak("Placeholder :%d invalid,
> > placeholders must be >= 1", idx);
> >
> > while(isDIGIT(*src)) *dest++ = *src++; style = ":1";
> >
> > } else if (isALNUM(*src)) { /* ':foo' */ while(isALNUM(*src)) /*
> > includes '_' */ *dest++ = toLOWER(*src), src++; style = ":foo";
> >
> > } else { /* perhaps ':=' PL/SQL construct */ /* if (src == ':')
> > *dest++ = *src++; XXX? move past '::'? */ continue; }
> >
> > *dest = '\0'; /* handy for debugging */ namelen = (dest-start); if
> > (laststyle && style != laststyle) croak("Can't mix placeholder styles
> > (%s/%s)",style,laststyle);
> >
> > The DBI documentation is too vague in this respect to say DBD::Oracle
> > is wrong here. I also know some drivers which support :xxx but then
> > when you call bind_param you don't specify the leading ':' (subject
> > of a dbi-dev posting of mine some years back). There are also drivers
> > which support :xxx but you cannot put that exact :xxx more than once
> > in the SQL (you can with DBD::Oracle).
> >
> > Martin
>
> Martin
> --
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
