I forgot to say that I am using : $DBD::Oracle::VERSION = '1.15'; Perl v5.8.5.
Susan > -----Original Message----- > From: Susan Cassidy [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 14, 2004 4:07 PM > To: [EMAIL PROTECTED] > Subject: difficulties with utf-8 characters using DBD::Oracle, where works > using DBD::Pg (PostgreSQL) > > 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
