sybperl-l Archive

From: michael dot peppler at bnpparibas dot com
Subject: Re: problems porting Sybase::BCP to Sybase::BLK
Date: Nov 17 2006 8:12AM

Various issues...

First,

You need to include the HAS_IDENTITY => 1 in the same $blk->config() call 
as the rest of the configuration to enable IDENTITY_INSERT for the table. 
So I'd do something like

my $identity_flag = ( $contbl1{$tg} ? 1 : 0 );

$bcp->config( INPUT => sub { $_ = shift @rows || last; return @{$_} },
ERRORS => ('G:\_svncode\scripts\trunk\blk.err.' . $tg. "." . time()) ,
OUTPUT => "$mydb.dbo.$tg", TAB_INFO => \@fieldnames, NULL => qq{undef},
HAS_IDENTITY => $identity_flag );

Second, I don't understand why you pass qq(undef) for the NULL pattern. 
Are NULL values in the input stream empty strings (i.e. undef) or 
something else?

I'm not sure why the SET CHAR_CONVERT option doesn't work - although it 
may be that you need to set this at the BLK API level, in which case you 
should probably set the client charset when you create the connection.

Michael






Internet
knb@gfz-potsdam.de@peppler.org - 16.11.2006 17:14
 
Sent by:        owner-sybperl-l@peppler.org
To:     sybperl-l
cc: 
Subject:        problems porting Sybase::BCP to Sybase::BLK


I am porting a script that uses Sybase::BCP to bcp-in some short tables
into a ASE 12.5
The transfer is from MSSQL-2000 to Sybase ASE. I am not pumping in text
files.

The old script works automagically well except for long strings of
length > 255 chars. I think this is a problem of BCP-in based on dblib.

Now I want to revise the script and port it to Sybase::BLK

What must I do to get a command similar to dbcmd() to work with Sybase 
BLK?

Any help is appreciated.

Best regards, Knut

#####
##### OLD - works reasonably well
#####
my $bcp = new Sybase::BCP $user, $pwd, $server;
$bcp->dbcmd("set char_convert iso_1 ") ;
$bcp->dbcmd("set identity_insert $mydb..$tg on") if (exists
$contbl1{$tg}  );
$bcp->config(INPUT => sub { $_ = shift @dat || last; return @{$_}} ,
OUTPUT => "$mydb.dbo.$tg", FIELDS => scalar(@FieldNames) );
$bcp->run() ;
$bcp->dbcmd("set identity_insert $mydb..$tg off") if (exists
$contbl1{$tg} );


#####
##### NEW
#####
my $bcp = new Sybase::BLK $user, $pwd, $server;
## DOES NOT WORK
#$bcp->ct_sql("set char_convert iso_1 ");
# DOES NOT WORK
$bcp->ct_sql("set identity_insert $mydb..$tg on") if ( exists
$contbl1{$tg}  );
#$bcp->config(HAS_INDENTITY => 0);

# WORKS OK for tables without identity columns
$bcp->config( INPUT => sub { $_ = shift @rows || last; return @{$_} },
ERRORS => ('G:\_svncode\scripts\trunk\blk.err.' . $tg. "." . time()) ,
OUTPUT => "$mydb.dbo.$tg", TAB_INFO => \@fieldnames, NULL => qq{undef} );
$bcp->run();
$bcp->ct_sql("set identity_insert $mydb..$tg off") if ( exists
$contbl1{$tg} );


#####
##### ERROR messages - how can I get rid of these?
#####

Use of uninitialized value in pattern match (m//) at
C:\Perl\site\lib/Sybase/BLK.pm line 545.
Use of uninitialized value in length at C:\Perl\site\lib/Sybase/BLK.pm
line 545.

#### This refers to these lines in Sybase/BLK.pm
# Check for nulls:
if(defined($null_pattern) && length($null_pattern) > 0 &&
$data[$i] =~ /$null_pattern/)
{
$data[$i] = undef;
} elsif(length($data[$i]) == 0) {
# default NULL handling.
$data[$i] = undef;
}


#### it is different from Sybase::BCP, why is there no \Q ... \E  anymore?
if(defined($null_pattern) && length($null_pattern) > 0 &&
$data[$i] =~ /\Q$null_pattern\E/)
{
$data[$i] = undef;
}



$null_pattern should be initialised, as I am Passing in something.


This message and any attachments (the "message") is
intended solely for the addressees and is confidential. 
If you receive this message in error, please delete it and 
immediately notify the sender. Any use not in accord with 
its purpose, any dissemination or disclosure, either whole 
or partial, is prohibited except formal approval. The internet
can not guarantee the integrity of this message. 
BNP PARIBAS (and its subsidiaries) shall (will) not 
therefore be liable for the message if modified. 

                ---------------------------------------------

Ce message et toutes les pieces jointes (ci-apres le 
"message") sont etablis a l'intention exclusive de ses 
destinataires et sont confidentiels. Si vous recevez ce 
message par erreur, merci de le detruire et d'en avertir 
immediatement l'expediteur. Toute utilisation de ce 
message non conforme a sa destination, toute diffusion 
ou toute publication, totale ou partielle, est interdite, sauf 
autorisation expresse. L'internet ne permettant pas 
d'assurer l'integrite de ce message, BNP PARIBAS (et ses
filiales) decline(nt) toute responsabilite au titre de ce 
message, dans l'hypothese ou il aurait ete modifie.