John,
In case it helps, here is a version of my test script that seems to
correctly handle utf-8 chars for bind_param_inout ...
#!/usr/bin/perl -w
use strict;
use warnings;
use DBI qw();
use DBD::Oracle qw();
use Encode;
print "Using DBI $DBI::VERSION and DBD::Oracle $DBD::Oracle::VERSION\n";
my $uidpwd = 'usr/p...@db';
my $dbh = DBI->connect(
'dbi:Oracle:',
$uidpwd,
'',
{RaiseError => 1, PrintError => 0});
my $sth = $dbh->prepare(q(
select chr(14844588)
from dual
));
$sth->execute;
my ($sym1, $sym2, $sym3);
$sth->bind_columns(\($sym1));
$sth->fetch;
print "Sym (1) = $sym1\n";
$sth = $dbh->prepare(q(
BEGIN
:ret := chr(14844588);
END;
));
$sth->bind_param_inout(':ret', \$sym2, 10);
$sth->execute;
print "Sym (2 before) = $sym2\n";
Encode::_utf8_on($sym2);
unless (Encode::is_utf8($sym2, 1)) {
Encode::_utf8_off($sym2);
$sym2 = Encode::encode('UTF-8', $sym2);
}
print "Sym (2 after) = $sym2\n";
$dbh->disconnect;
stbald...@au-stb-mobile:~/dev$ ./utf8.plx
Using DBI 1.605 and DBD::Oracle 1.23
Sym (1) = €
Sym (2 before) = â¬
Sym (2 after) = €
So, we have a workaround of sorts, but I'd rather not go through all our
code and add the manual encoding stuff.
Thanks for your help,
Steve
On Sun, 2009-03-29 at 12:17 +1100, Steve Baldwin wrote:
> John,
>
> I installed this version and it didn't seem to make any difference ...
>
> stbald...@au-stb-mobile:~/dev$ ./utf8.plx
> Using DBI 1.605 and DBD::Oracle 1.23
> Sym (1) = €
> Sym (2) = €
>
> I see the same behaviour whether I connect to an 11g or 9i database. I
> have the 11g oracle client.
>
> Here's a level 3 trace ...
>
> -> prepare for DBD::Oracle::db (DBI::db=HASH(0x82ece1c)~0x82ecdb0 '
> select chr(14844588)
> from dual
> ') thr#8153008
> dbd_st_prepare'd sql SELECT (pl1, auto_lob1, check_sql1)
> dbd_describe SELECT (EXPLICIT, lb 80)...
> Described col 1: dbtype 1(VARCHAR), scale 0, prec 3, nullok 1, name
> CHR(14844588)
> : dbsize 3, char_used 1, char_size 1, csid 873, csform 1,
> disize 3
> fbh 1: 'CHR(14844588)' NULLable, otype 1-> 5, dbsize 3/4, p3.s0
> row cache OCI_ATTR_PREFETCH_ROWS 1042, OCI_ATTR_PREFETCH_MEMORY 0
> rs_array_init: rs_array_on=0, rs_array_size=1
> calling OCIAttrSet OCI_ATTR_CHARSET_FORM with csform=1
> dbd_describe'd 1 columns (row bytes: 3 max, 1 est avg, cache: 1042)
> <- prepare= DBI::st=HASH(0x82ecf78) at utf8.plx line 13
> -> execute for DBD::Oracle::st (DBI::st=HASH(0x82ecf78)~0x82eceac)
> thr#8153008
> dbd_st_execute SELECT (out0, lob0)...
> Statement Execute Mode is 0 (DEFAULT)
> dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0)
> <- execute= '0E0' at utf8.plx line 17
> -> bind_columns in DBD::_::st for DBD::Oracle::st
> (DBI::st=HASH(0x82ecf78)~0x82eceac SCALAR(0x81ff2bc)) thr#8153008
> .. FETCH DBI::st=HASH(0x82eceac) 'NUM_OF_FIELDS' = 1 (cached)
> 1 <- FETCH= 1 at DBI.pm line 1828 via at utf8.plx line 19
> 1 <- bind_col= 1 at DBI.pm line 1839 via at utf8.plx line 19
> <- bind_columns= 1 at utf8.plx line 19
> -> fetch for DBD::Oracle::st (DBI::st=HASH(0x82ecf78)~0x82eceac)
> thr#8153008
> dbd_st_fetch 1 fields...
> dbd_st_fetched 1 fields with status of 0(SUCCESS)
> <- fetch= [ "€" ] row1 at utf8.plx line 20
> Sym (1) = €
> -> prepare for DBD::Oracle::db (DBI::db=HASH(0x82ece1c)~0x82ecdb0 '
> BEGIN
> :ret := chr(14844588);
> END;
> ') thr#8153008
> dbd_preparse scanned 1 distinct placeholders
> dbd_st_prepare'd sql BEGIN (pl1, auto_lob1, check_sql1)
> dbd_describe skipped for BEGIN
> <- prepare= DBI::st=HASH(0x824973c) at utf8.plx line 22
> <> DESTROY(DBI::st=HASH(0x82ecf78)) ignored for outer handle (inner
> DBI::st=HASH(0x82eceac) has ref cnt 1)
> -> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x82eceac)~INNER)
> thr#8153008
> <- DESTROY= undef at utf8.plx line 27 via at utf8.plx line 27
> -> bind_param_inout for DBD::Oracle::st
> (DBI::st=HASH(0x824973c)~0x82ed05c ':ret' SCALAR(0x81ff2ec) 10)
> thr#8153008
> dbd_bind_ph(): bind :ret <== undef (type 0 (DEFAULT (varchar)), inout
> 0x81ff2ec, maxlen 10)
> dbd_rebind_ph_char() (1): bind :ret <== undef (NULL, size 0/0/10, ptype
> 4(VARCHAR), otype 1 , inout)
> dbd_rebind_ph_char() (2): bind :ret <== '' (size 0/32, otype 1(VARCHAR),
> indp -1, at_exec 1)
> bind :ret as ftype 1 (VARCHAR)
> dbd_rebind_ph(): bind :ret <== undef (inout, not-utf8, csid 873->0->873,
> ftype 1 (VARCHAR), csform 0->0, maxlen 32, maxdata_size 0)
> <- bind_param_inout= 1 at utf8.plx line 27
> -> execute for DBD::Oracle::st (DBI::st=HASH(0x824973c)~0x82ed05c)
> thr#8153008
> dbd_st_execute BEGIN (out1, lob0)...
> with :ret = '' (len 0(0)/32, indp -1, otype 1, ptype 6)
> Statement Execute Mode is 32 (COMMIT_ON_SUCCESS)
> in ':ret' [0,0]: len 0, ind -1, value=undef
> out ':ret' [0,0]: alen 36, piece 0
> dbd_st_execute BEGIN returned (SUCCESS, rpc1, fn34, out1)
> dbd_st_execute(): Analyzing inout parameter ':ret of type=1'
> out :ret = '�.�' (arcode 0, ind 0, len 3)
> <- execute= 1 at utf8.plx line 28
> Sym (2) = €
>
> Let me know if you would like me to run any other tests.
>
> Thanks,
>
> Steve
>
> On Fri, 2009-03-27 at 06:38 -0400, [email protected] wrote:
> > I believe this has been has been fixed in the latest trunk version of
> > DBD::Oracle
> >
> >
> > I ran the test below and came up with the same values. If you can test
> > this version of DBD::Oracle
> >
> > http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-123-RC4.tar
> >
> > and see if it works for you
> >
> > cheers
> > John Scoles
> >
> >
> > > DBI version : 1.605
> > > DBD::Oracle version : 1.22
> > > Database : 11.1.0.7 (don't think this matters)
> > > DB Charset : AL32UTF8 (this definitely matters)
> > > NLS_LANG : AMERICAN_AMERICA.AL32UTF8
> > >
> > > Consider the following script ...
> > >
> > > #!/usr/bin/perl -w
> > >
> > > use strict;
> > > use warnings;
> > > use DBI qw();
> > > use DBD::Oracle qw();
> > >
> > > my $uidpwd = 'usr/p...@xxx';
> > > my $dbh = DBI->connect(
> > > 'dbi:Oracle:',
> > > $uidpwd,
> > > '',
> > > {RaiseError => 1, PrintError => 0},
> > > );
> > >
> > > my $sth = $dbh->prepare(q(
> > > select chr(14844588)
> > > from dual
> > > ));
> > > $sth->execute;
> > > my ($sym1, $sym2, $sym3);
> > > $sth->bind_columns(\($sym1));
> > > $sth->fetch;
> > > print "Sym (1) = $sym1\n";
> > > $sth = $dbh->prepare(q(
> > > BEGIN
> > > :ret := chr(14844588);
> > > END;
> > > ));
> > > $sth->bind_param_inout(':ret', \$sym2, 10);
> > > $sth->execute;
> > > print "Sym (2) = $sym2\n";
> > > $dbh->disconnect;
> > >
> > > If I run the script, I would expect to see the same results from the 2
> > > print statements. However, this is what I see ...
> > >
> > > stbald...@au-stb-mobile:~/dev$ ./utf8.plx
> > > Sym (1) = €
> > > Sym (2) = €
> > >
> > > Anyone have any clues as to what is happening?
> > >
> > > Thanks,
> > >
> > > Steve
> > >
> > >
> > > 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.
> > >
> > >
> >
> >
>
>
> 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.
>
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.
