Success at last! I had the dba create an instance with the database character set AL32UTF8 (thanks to the excellent documentation with DBD::Oracle 1.16, which explained the weird behavior of Oracle with plain UTF8), and used NLS_LANG=.UTF8, and DBD::Oracle 1.16 (1.15 did not work 100%, even with the new Oracle instance).
Now, French looks like French, Japanese looks like Japanese, etc. Tim saves my life once again! Thanks, Susan Cassidy > -----Original Message----- > From: Tim Bunce [mailto:[EMAIL PROTECTED] > Sent: Friday, October 15, 2004 2:01 AM > To: Susan Cassidy > Cc: [EMAIL PROTECTED] > Subject: Re: difficulties with utf-8 characters using DBD::Oracle, where > works using DBD::Pg (PostgreSQL) > > Try http://homepage.eircom.net/~timbunce/DBD-Oracle-1.16-rc7- > 20040826.tar.gz > > and read the documentation about unicode carefully. > Let me know how it goes. > > Tim. > > On Thu, Oct 14, 2004 at 04:06:41PM -0700, Susan Cassidy wrote: > > Hello, > > > > > > > > I have a cgi application that works fine using DBD::Pg to insert/select > data > > from a PostgreSQL using UTF-8 (database created as UNICODE). We have > data > > in multiple languages stored, which has been working fine. > > > > > > > > I have modified the application to use either Oracle or PostgreSQL, > > depending on a config file. The PostgreSQL part still works fine - web > page > > shows up correctly (we specify utf-8 encoding in the header), no > problems. > > > > > > > > The Oracle way is problematic. > > > > > > > > >From SQLPLUS, it appears that I can INSERT and SELECT data in French, > for > > example, and it all looks correct. The environment in my Linux window > has > > these variables: > > > > NLS_LANG=.UTF8 -----> this also works with > > NLS_LANG=AMERICAN_AMERICA.UTF8 > > > > ORACLE_SID=STSDEV1 > > > > ORACLE_BASE=/home/oracle > > > > LANG=UTF-8 > > > > ORA_NLS33=/home/oracle/product/9.2.0/ocommon/nls/admin/data > > > > ORACLE_HOME=/home/oracle/product/9.2.0 > > > > > > > > I set ORACLE_HOME, ORACLE_SID, ORA_NLS33, and NLS_LANG environment > variables > > in httpd.conf, and in programs that I run for tests that are not running > as > > web apps. > > > > > > > > If I connect via DBD::Oracle, I get some of the French special > characters to > > come out right, and others do not. I have been told that some (when > > retrieved) are actually encoded in UTF8, and others are Latin1. > > > > > > > > I use the same input data, fetch the same translated data, etc. The > only > > differences that are left seems to be DBD::Oracle, Oracle itself, and > the > > environment settings for Oracle. > > > > > > > > I extracted some basic data, known to be utf8, and inserted it into a > table > > using Oracle SQLLDR. Then, I retrieved it using a sql script, via > sqlplus, > > spooling the output to a file. If I read that file, and output it to a > web > > page, it looks fine. > > > > > > > > If I read the data via DBD::Oracle, it has garbage characters instead of > the > > special characters. > > > > > > > > This seems to point to DBD::Oracle being the cause of the problems. > Perhaps > > some method I need to call that I did not get from the documentation? > > > > > > > > I will append the basic test program below (simple program, instead of > giant > > application - same type of results): > > > > > > > > Any advice gratefully received. I have never had so much trouble with a > DBD > > application, and have used DBD::Oracle before with no trouble. > > > > > > > > Susan Cassidy > > > > > > > > ------------------------------------------------------------------------ > ---- > > ------------------------------------- > > > > > > > > #!/usr/local/bin/perl > > > > > > > > use CGI; > > > > use DBI; > > > > > > > > our $dbh; > > > > our $sth; > > > > > > > > $dbuser="xxx"; > > > > $dbpasswd="yyy"; > > > > $dbserver='devsys'; > > > > $db_sid='TEST1'; > > > > > > > > > > > > #$ENV{NLS_LANG}='AMERICAN_AMERICA.UTF8'; > > > > $ENV{NLS_LANG}='.UTF8'; > > > > $ENV{ORA_NLS33}='/home/oracle/product/9.2.0/ocommon/nls/admin/data'; > > > > $ENV{ORACLE_HOME}='/home/oracle/product/9.2.0'; > > > > > > > > $dbh= DBI->connect("dbi:Oracle:host=$dbserver;sid=$db_sid", $dbuser, > > $dbpasswd, > > > > {PrintError => 0, AutoCommit => 1}) or errexit( "Unable to connect > to > > $dbserver: $DBI::errstr"); > > > > > > > > > > > > my $html_hdr=<<"EOF"; > > > > <html> > > > > <head> > > > > <title>SYSTRAN - UTF8 Test</title> > > > > <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> > > > > <link rel="stylesheet" href="http://www.systransoft.com/Systran.css"; > > type="text/css"> > > > > </head> > > > > <h3>Sample data</h3> > > > > <table cellpadding=0 cellspacing=2 border=1> > > > > EOF > > > > > > > > my $cgi=new CGI; > > > > print $cgi->header( -charset=>'utf-8'); > > > > print $html_hdr; > > > > print <<"EOF"; > > > > <tr bgcolor="silver"> > > > > <td>TU</td> > > > > <td>English</td> > > > > <td>French</td> > > > > </tr> > > > > EOF > > > > > > > > my (@data); > > > > > > > > my ($select_stmt)=<<" EOF"; > > > > SELECT source, target from test_trans > > > > EOF > > > > > > > > execute_db_statement($select_stmt, __LINE__); > > > > while (@data = $sth->fetchrow_array) { > > > > foreach (@data) { $_='' unless defined} > > > > next if ($data[0] eq ''); > > > > print '<tr><td>',(join "</td><td>",@data),"</td></tr>\n"; > > > > } > > > > #check for problems with premature termination > > > > errexit($sth->errstr) if $sth->err; > > > > print <<"EOF"; > > > > </table> > > > > <p> > > > > </body> > > > > </html> > > > > EOF > > > > exit; > > > > > > > > sub errexit { > > > > my (@msg)[EMAIL PROTECTED]; > > > > print @msg,"\n"; > > > > exit 1; > > > > } > > > > > > > > > > > > > > > > sub execute_db_statement { > > > > #this subroutine will prepare and execute a statement for the > database, > > > > # and errexit if it fails either step > > > > my ($statement, $lineno)[EMAIL PROTECTED]; > > > > my ($rc); > > > > #get basic machine info > > > > $sth=$dbh->prepare($statement) || > > > > errexit("bad prepare for stmt $statement at line $lineno, error: > > $DBI::errstr"); > > > > $rc=$sth->execute() || > > > > errexit("can't execute statement:\n$statement\n at line $lineno, ", > > > > "return code $rc: DB error: $DBI::errstr"); > > > > } # end sub execute_db_statement > >
