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
>
