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 | 3EF2AB05.F2383CDA@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 по дате отправления: