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

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

You should probably rt this at rt.cpan.org.

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com