difficulties with utf-8 characters using DBD::Oracle, where works using DBD::Pg (PostgreSQL)

From: Susan Cassidy (cassidy_at_systransoft.com)
Date: 10/15/04

  • Next message: Susan Cassidy: "RE: difficulties with utf-8 characters using DBD::Oracle, where works using DBD::Pg (PostgreSQL)" 
    To: <dbi-users@perl.org>
Date: Thu, 14 Oct 2004 16:06:41 -0700

    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="style***" 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)=@_;

      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)=@_;

      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

  • Next message: Susan Cassidy: "RE: difficulties with utf-8 characters using DBD::Oracle, where works using DBD::Pg (PostgreSQL)"
    • Quantcast