Re: Error I've not seen before from oracle DBD

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.

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 J. Evans
Easysoft Limited
http://www.easysoft.com