Re: DBD::Oracle problem with bind_param_inout and UTF-8
- From: stbaldwin@xxxxxxxxxxxxxxxx (Steve Baldwin)
- Date: Mon, 30 Mar 2009 10:39:24 +1100
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/pwd@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;
stbaldwin@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 ...
stbaldwin@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, scoles@xxxxxxxxxxx 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/pwd@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 ...
stbaldwin@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.
.
- References:
- DBD::Oracle problem with bind_param_inout and UTF-8
- From: Steve Baldwin
- Re: DBD::Oracle problem with bind_param_inout and UTF-8
- From: scoles
- Re: DBD::Oracle problem with bind_param_inout and UTF-8
- From: Steve Baldwin
- DBD::Oracle problem with bind_param_inout and UTF-8
- Prev by Date: Re: DBD::Oracle problem with bind_param_inout and UTF-8
- Next by Date: Problem with Oracle ODBC connection
- Previous by thread: Re: DBD::Oracle problem with bind_param_inout and UTF-8
- Next by thread: WG: Re: Mac OS-X, Oracle and Activestate....
- Index(es):
Relevant Pages
|
