big5 characters does not show properly at the new upgrade (version 7.3.3)

Поиск
Список
Период
Сортировка
От Gene Leung
Тема big5 characters does not show properly at the new upgrade (version 7.3.3)
Дата
Msg-id 3EF2674A.DA642126@aurasound.com.hk
обсуждение исходный текст
Список pgsql-general
To all postgresql users,

I've just installed the new version of postgresql 7.3.3 (using the
source and compile it myself).  I find out there is a problem with the
big5 characters.  I have a perl script and it is used to extract data
from some xbase file to the postgresql.  It works under the version
7.3.1.  At the first place, I thought it would be different version DBI
and DBD-PG installed between servers.  So I change the connection host
of the script and run it directly at the old server.  It came out with
the same results that all the big5 characters did not show properly.
Inside my perl script, the "set client_encoding to 'big5'" has been
added for the sake of  double-byte character of Chinese Language.  Below
is the perl script that  works under version 7.3.1.  Any help would be
appreciated.  Both servers database was created with the 'EUC_TW'
encoding.

Best Regards
Gene Leung

#------------------------ Perl Script ----------------------------
#!/usr/bin/perl

use POSIX qw(strftime);
use DBI;
use strict;

require 'Function.pl';

                        
#------------- DBF variable -----------------
my ($Tmpsth, $dbh, $sth1, $sth2);
my (@ary);
my ($dsn);
my ($CreDT, $CreSiteID, $CreUsrID);

#------------- SQL variable -----------------
my ($SQL_dbh, $SQL_sth1, $SQL_sth2, $SQL_sth3);
my ($SQL_dsn, $SQL_user_name, $SQL_password);

my ($LogFile, $ErrFile, $RowAffectedCount);
my ($ConfigFile) = 'Config.ini';
my ($TableName1, $TableName2, $TableName3 );
my $TEMP;
my ($ary_ref);
my ($T0, $T1, $T2, $T3, $T4, $T5, $T6, $T7, $T8, $T9, $T10, $T11);
my $MasFile = '/tmp/MyMas.in';
my $RemFile = '/tmp/MyRemarks.in';
my $DetailFile = '/tmp/MyDetail.in';
my ($OutFile1) = '/tmp/dbf1.out';
my ($OutFile2) = '/tmp/dbf2.out';
my ($OutFile3) = '/tmp/dbf3.out';
my ($InFile)  = '/tmp/dbf.in';
my ($TmpAddrID1, $TmpAddrID2);
my @TmpTextFile;
my $IsMemo;
my @Result;
my $Line;
$TableName1 = 'CUST';
$TableName2 = 'ADDRESS CUST';
$TableName3 = 'CUST_REMARKS';


#open (err_fh, ">>BMCerror.log");
prc_StampStart();

($dsn, $CreDT, $CreSiteID, $CreUsrID, $SQL_dsn, $SQL_user_name,
$SQL_password, $LogFile, $ErrFile) = prc_GetDBF2SQLConfig($ConfigFile);
open (log_fh, ">>$LogFile");
print log_fh "FoxCUST START\n";

$SQL_dbh = DBI->connect($SQL_dsn, $SQL_user_name, $SQL_password,
{PrintError => 0, RaiseError => 0});

$Tmpsth = $SQL_dbh->prepare(qq{ Set Client_Encoding to 'big5' });
$Tmpsth->execute;

#-------------------------------------------------------- Processing
bmc_mas ---------------------------------------------
$SQL_sth1 = $SQL_dbh->prepare(qq{
                COPY CUST(CustID,
                                CustName,
                                Nature,
                                CurID,
                                Grade,
                                Payment,
                                PayTerm,
                                ShipTerm,
                                CreditLimit,
                                CreditBalance,
                                Contact,
                                AddrID,
                                BillContact,
                                BillAddrID,
                                AprvUsrID,
                                AprvDate,
                                Salesman,
                                CreDT,
                                CreSiteID,
                                CreUsrID,
                                ModDT,
                                ModSiteID,
                                ModUsrID)
         FROM '/tmp/dbf1.out'

                                     });

$SQL_sth2 = $SQL_dbh->prepare(qq{
                COPY ADDRESS(AddrID,
                                         TelNo,
                                         FaxNo,
                                         TelexNo,
                                         Addr1,
                                         Addr2,
                                         Addr3,
                                         Addr4,
                                         CountryID,
                                         CreDT,
                                         CreSiteID,
                                         CreUsrID,
                                         ModDT,
                                         ModSiteID,
                                         ModUsrID)
         FROM '/tmp/dbf2.out'

                                     });

$SQL_sth3 = $SQL_dbh->prepare(qq{
                COPY CUST_REMARKS(CustID,
                                CreDT,
                                CreSiteID,
                                CreUsrID,
                                ModDT,
                                ModSiteID,
                                ModUsrID,
                                Remarks)
         FROM '/tmp/dbf3.out'

                                     });
open (IN_fh, ">$InFile");
close (IN_fh);

$IsMemo = 'F';
$Line   = '';

open (IN_fh, ">>$InFile");
print IN_fh `dbfdump -u 'nn'  -fs '||||' -fields

custno,custnm,nature,cur,grade,paymt,pterm,shpterm,limit,crbal,contact,b2contact,aprvby,aprvdate,salesman,vdate,cpcode,usernm,tele,fax,telex,add1,add2,add3,add4,b2tele,b2fax,b2telex,b2add1,b2add2,b2add3,b2add4,remarks
/mnt/sql/relstock/CUST.DBF `
    or prc_BailOut($ErrFile, $TableName1, "Copy Error", $DBI::err,
($DBI::errstr)) ;
print log_fh "dbfdump error code = $?\n";
close (log_fh);

print IN_fh "||||\n";
close (IN_fh);

open (IN_fh, "<$InFile");
open (OUT1_fh, ">$OutFile1");
open (OUT2_fh, ">$OutFile2");
open (OUT3_fh, ">$OutFile3");


$RowAffectedCount = 0;
while (<IN_fh>)
{
    chomp;
  $_ =~ s/^\ //g;
    if ((length($_) != 0))
    {
      if ((/\|\|\|\|/)&&($IsMemo eq 'F'))
        {
            $IsMemo = 'T';
            $Line   = "$_";
        }else{
            if ((/\|\|\|\|/)&&($IsMemo eq 'T'))
            {
                $IsMemo = 'T';
                @Result = split(/\|\|\|\|/, $Line);


                if ((@Result[2] eq 'nn')||(@Result[2] eq ''))
                {
                    @Result[2] = '';
                }
                if ((@Result[13] eq 'nn')||(@Result[13] eq ''))
                {
                    @Result[13] = @Result[15];
                }
                if ((@Result[14] eq 'nn')||(@Result[14] eq ''))
                {
                    @Result[14] = @Result[17];
                }
                if ((@Result[15] eq 'nn')||(@Result[15] eq ''))
                {
                    @Result[15] = $CreDT;
                }
                if ((@Result[16] eq 'nn')||(@Result[16] eq ''))
                {
                    @Result[16] = $CreSiteID;
                }
                if ((@Result[17] eq 'nn')||(@Result[17] eq ''))
                {
                    @Result[17] = $CreUsrID;
                }

                @Result[14] = substr(@Result[14], 0, 10);

                if ((@Result[0] ne ''))
                {
                  $RowAffectedCount = $RowAffectedCount + 1;
                  $TmpAddrID1 = "C\-@Result[0]\-1";
                  $TmpAddrID2 = "C\-@Result[0]\-2";

                    print OUT1_fh

"@Result[0]\t@Result[1]\t@Result[2]\t@Result[3]\t@Result[4]\t@Result[5]\t@Result[6]\t@Result[7]\t@Result[8]\t@Result[9]\t@Result[10]\t$TmpAddrID1\t@Result[11]\t$TmpAddrID2\t@Result[12]\t@Result[13]\t@Result[14]\t@Result[15]\t@Result[16]\t@Result[17]\t@Result[15]\t@Result[16]\t@Result[17]\n";

                    print OUT2_fh

"$TmpAddrID1\t@Result[18]\t@Result[19]\t@Result[20]\t@Result[21]\t@Result[22]\t@Result[23]\t@Result[24]\tNONE\t@Result[15]\t@Result[16]\t@Result[17]\t@Result[15]\t@Result[16]\t@Result[17]\n";
                    print OUT2_fh

"$TmpAddrID2\t@Result[25]\t@Result[26]\t@Result[27]\t@Result[28]\t@Result[29]\t@Result[30]\t@Result[31]\tNONE\t@Result[15]\t@Result[16]\t@Result[17]\t@Result[15]\t@Result[16]\t@Result[17]\n";
                    if (@Result[32] ne 'nn')
                    {
                        print OUT3_fh
"@Result[0]\t@Result[15]\t@Result[16]\t@Result[17]\t@Result[15]\t@Result[16]\t@Result[17]\t@Result[32]\n";
                    }
                }
                $Line   = "$_";
            }else {
              $Line     = "$Line$_\\n";
            }
        }
    }
}

close (IN_fh);
#unlink ($InFile);
close (OUT1_fh);
close (OUT2_fh);
close (OUT3_fh);

$SQL_sth1->execute()
    or prc_BailOut($ErrFile, $TableName1, "Copy Error", $DBI::err,
($DBI::errstr)) ;
$SQL_sth1->finish();

$SQL_sth2->execute()
    or prc_BailOut($ErrFile, $TableName2, "Copy Error", $DBI::err,
($DBI::errstr)) ;
$SQL_sth2->finish();

$SQL_sth3->execute()
    or prc_BailOut($ErrFile, $TableName3, "Copy Error", $DBI::err,
($DBI::errstr)) ;
$SQL_sth3->finish();

#unlink ($OutFile1);
#unlink ($OutFile2);
#unlink ($OutFile3);

prc_StampEnd();
prc_PrintLog($LogFile, $TableName1, $RowAffectedCount);

$SQL_dbh->disconnect();

exit(0);

В списке pgsql-general по дате отправления:

Предыдущее
От: "Matthew T. O'Connor"
Дата:
Сообщение: Re: How to process mail using pgSQL?
Следующее
От: Jonathan Ellis
Дата:
Сообщение: Tuning question: WAL disk a bottleneck?