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

From: Gene Leung <gene(dot)leung(at)aurasound(dot)com(dot)hk>
To: pgsql-general(at)postgresql(dot)org
Subject: big5 characters does not show properly at the new upgrade (version 7.3.3)
Date: 2003-06-20 06:34:45
Message-ID: 3EF2AB05.F2383CDA@aurasound.com.hk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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\-(at)Result[0]\-1";
$TmpAddrID2 = "C\-(at)Result[0]\-2";

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

print OUT2_fh
"$TmpAddrID1\t(at)Result[18]\t@Result[19]\t(at)Result[20]\t@Result[21]\t(at)Result[22]\t@Result[23]\t(at)Result[24]\tNONE\t@Result[15]\t(at)Result[16]\t@Result[17]\t(at)Result[15]\t@Result[16]\t(at)Result[17]\n";
print OUT2_fh
"$TmpAddrID2\t(at)Result[25]\t@Result[26]\t(at)Result[27]\t@Result[28]\t(at)Result[29]\t@Result[30]\t(at)Result[31]\tNONE\t@Result[15]\t(at)Result[16]\t@Result[17]\t(at)Result[15]\t@Result[16]\t(at)Result[17]\n";
if (@Result[32] ne 'nn')
{
print OUT3_fh
"@Result[0]\t(at)Result[15]\t@Result[16]\t(at)Result[17]\t@Result[15]\t(at)Result[16]\t@Result[17]\t(at)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);

Browse pgsql-general by date

  From Date Subject
Next Message Nigel J. Andrews 2003-06-20 06:43:27 Re: Getting one row for each subquery row...?
Previous Message Ivar 2003-06-20 06:26:11 Re: Is there any way to make post to newsgroups faster ?