Re: help with odd DBI perpare/execute errors

On 03/06/15 17:12, Bruce Johnson wrote:
>
>> On Jun 3, 2015, at 7:19 AM, William Bulley <[email protected]> wrote:
>>
>> According to Bruce  Johnson <[email protected]> on Wed, 06/03/15 at 10:10:
>>>
>>>
>>> Make sure your original $query is delimited by double quotes, not single.
>>
>> I've tried _everything_!!
>>
>> Single quotes.  Double quotes.  q{} and qq{} (using the latter now).
>>
>> But no matter what I try DBI complains about the darn question mark!
>>
>> It is infuriating, I tell you!   :-)
>
> Well, I just tested MY theory (RHEL v6.5, oracle 11.2g,  oracle instant client for 11.2,  perl, v5.10.1 (*) built for x86_64-linux-thread-multi) with:
>
> #!/usr/bin/perl
> use strict;
> use DBI;
> my $dbh= DBI->connect(“dbi:Oracle:host=$host", $user, $pass, {RaiseError =>1});
>
> my $qry1 ='select ? from dual';
> my $qry2 = "select ? from dual";
> my $sth = $dbh->prepare($qry1);
> $sth->execute('foo');
> my ($res)=$sth->fetchrow();
> print "single quote result is $res \n";
> $sth =$dbh->prepare($qry2);
> $sth->execute('bar');
> ($res)=$sth->fetchrow();
> print "double quote result is $res \n";
> exit;
>
> And got:
> # ./qmarktest.pl
> single quote result is foo
> double quote result is bar
>
> So yet another fine theory destroyed by reality…
>
> Possibly some sort of character set mess up? Could your 'question mark' be something else in the script? Maybe a 16-bit vs 8-bit character?  I’ve had some weird issues in the past when I was handed a 16-bit unicode text file of insert statements and tried to run them.
>

Bruce,

Just so you know, I've already told William off list that question mark should not get through to Oracle and so the preparse method in DBD::Oracle is not spotting the ? - perhaps because it is not a ? or it thinks it is in a comment, literal etc. However, I expect, as you do, that one of the chrs in the SQL is not what it seems.

Sorry, as log file was sent to me I answered it and did not cc the list.

Martin