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 = "[EMAIL PROTECTED]";
$TmpAddrID2 = "[EMAIL PROTECTED]";
print OUT1_fh
"@[EMAIL PROTECTED]@[EMAIL PROTECTED]@[EMAIL PROTECTED]@[EMAIL PROTECTED]@[EMAIL
PROTECTED]@[EMAIL PROTECTED]@[EMAIL PROTECTED]@[EMAIL PROTECTED]@[EMAIL
PROTECTED]@[EMAIL PROTECTED]@Result[17]\n";
print OUT2_fh
"[EMAIL PROTECTED]@[EMAIL PROTECTED]@[EMAIL PROTECTED]@[EMAIL PROTECTED]@[EMAIL
PROTECTED]@[EMAIL PROTECTED]@[EMAIL PROTECTED]";
print OUT2_fh
"[EMAIL PROTECTED]@[EMAIL PROTECTED]@[EMAIL PROTECTED]@[EMAIL PROTECTED]@[EMAIL
PROTECTED]@[EMAIL PROTECTED]@[EMAIL PROTECTED]";
if (@Result[32] ne 'nn')
{
print OUT3_fh
"@[EMAIL PROTECTED]@[EMAIL PROTECTED]@[EMAIL PROTECTED]@[EMAIL PROTECTED]";
}
}
$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);
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
