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
