Re: DBD::Oracle not utf8 encoding PL/SQL out values

Thanks a lot Martin.  Created
https://rt.cpan.org/Public/Bug/Display.html?id=74753.

Cheers,

Steve

On Tue, Feb 7, 2012 at 8:28 PM, Martin J. Evans
<[email protected]>wrote:

> On 07/02/12 04:56, Steve Baldwin wrote:
>
>> #!/usr/bin/perl -w
>> use strict;
>> use warnings;
>> use DBI;
>> use Encode;
>>
>> sub main {
>>     my $dbh = DBI->connect(
>>         'dbi:Oracle:',
>>         'usr/pwd@instance',
>>         '',
>>         { PrintError =>  0, AutoCommit =>  0, RaiseError =>  1, },
>>         );
>>     print {*STDERR} 'Connected !', "\n";
>>     $dbh->do(q(alter session set nls_territory = 'GERMANY'));
>>     my $sql =<<'END_SQL';
>> SELECT  ltrim(rtrim(to_char(0, 'L')))
>> FROM    dual
>> END_SQL
>>     my $sth = $dbh->prepare($sql);
>>     $sth->execute;
>>     my ($val);
>>     $sth->bind_columns(\($val));
>>     while ($sth->fetch) {
>>         print {*STDERR} 'val=[', $val, '] len=', length($val), "\n";
>>     }
>>     if (Encode::is_utf8($val)) {
>>         print {*STDERR} ' utf8 is on', "\n";
>>     } else {
>>         print {*STDERR} ' utf8 is off', "\n";
>>     }
>>     $sth->finish;
>>     $sql =<<'END_SQL';
>> declare
>>     l_ret       varchar2(10);
>> begin
>>     select  ltrim(rtrim(to_char(0, 'L')))
>>     into    l_ret
>>     from    dual;
>>     --
>>     :ret := l_ret;
>> end;
>> END_SQL
>>     undef $val;
>>     $sth = $dbh->prepare($sql);
>>     $sth->bind_param_inout(':ret', \$val, 100);
>>     $sth->execute;
>>     print {*STDERR} 'val=[', $val, '] len=', length($val), "\n";
>>     if (Encode::is_utf8($val)) {
>>         print {*STDERR} ' utf8 is on', "\n";
>>     } else {
>>         print {*STDERR} ' utf8 is off', "\n";
>>     }
>>     $dbh->disconnect;
>>     return 0;
>> }
>>
>> exit main();
>>
>
> Fails for me to with the very latest DBD::Oracle from subversion trunk.
>
> Is it a bug? I guess so as DBD::Oracle is supposed to do the right thing.
>
> Try this change:
>
> Index: dbdimp.c
> ==============================**==============================**=======
> --- dbdimp.c    (revision 15130)
> +++ dbdimp.c    (working copy)
> @@ -3197,7 +3197,14 @@
>                if (SvPVX(sv)) {
>                        SvCUR_set(sv, phs->alen);
>                        *SvEND(sv) = '\0';
> -                       SvPOK_only_UTF8(sv);
> +            SvPOK_only_UTF8(sv);
> +            if (CSFORM_IMPLIES_UTF8(SQLCS_**IMPLICIT)) {
> +#ifdef sv_utf8_decode
> +                sv_utf8_decode(sv);
> +#else
> +                SvUTF8_on(sv);
> +#endif
> +            }
>                }
>                else {  /* shouldn't happen */
>                        debug = 2;
>
> It seems to work for me but I don't really understand why DBD::Oracle code
> calls SvPOK_only_UTF8 as I cannot see why UTF8 status of the scalar would
> already be on.
>
> BTW, that code originates from a patch in April 2002 so it is OLD code -
> http://www.mail-archive.com/**[email protected]/msg10046.**html<http://www.mail-archive.com/[email protected]/msg10046.html>
>
> You should probably rt this at rt.cpan.org.
>
> Martin
> --
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
>


---------------------------------------------------------------------------------------
This email is intended solely for the use of the addressee and may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email.
---------------------------------------------------------------------------------------