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.

> 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