Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Knut Behrends <knb at gfz-potsdam dot de>
Subject: Re: problems porting Sybase::BCP to Sybase::BLK
Date: Nov 17 2006 10:48AM wrote:
> 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.

I have passed in many different expressions such as, for instance,
q(^$), but Sybase::BLK *always* complains about the $null_pattern
variable being undefined although it isn't undefined, $null_pattern
should be the string "undef" or '^$'etc

> Are NULL values in the input stream empty strings (i.e. undef) or
> something else?

In the Input stream there is the following
Dumped with DBI::trace(5)

fetch num_fields=8
fetch col#0 ORGANISATION_ID datalen=4 displ=12
fetch col#1 ORGANISATION_NAME datalen=45 displ=151
fetch col#2 ORGANISATION_LOCATION datalen=9 displ=51
fetch col#3 ORGANISATION_COUNTRY datalen=26 displ=51
fetch col#4 ORGANISATION_URL datalen=8 displ=151
fetch col#5 ORGANISATION_ACRONYM datalen=-1 displ=16
fetch col#6 ORGANISATION_CATEGORY datalen=8 displ=16
fetch col#7 ORGANISATION_DESCRIPTION datalen=-1 displ=51
2   <- fetch= [ '7193' 'University of Karlsruhe, Geological Institute'
'Karlsruhe' 'Baden-Wurttemberg, Germany' 'some_url' undef 'academic'
undef ] row21

This data comes from a MSSQL server fetched with DBD::ODBC on a windows

> 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.

Meanwhile I have found out how to do this myself, I am using

my $DSN_target  =

by the way, is it bulkLogin=1 or bcpLogin = 1 , or any of them? Both
seem to work.

and by the way again,

I think the documentation of this topic "Experimental Bulk-Load
Functionality"  in the perldoc for  DBD::Sybase can be improved :

my $dbh = DBI->connect(’dbi:Sybase:server=MY_SERVER;bulkLogin=1’, $user,

$dbh->begin_work;  # optional.
my $sth = $dbh->prepare("insert the_table values(?, ?, ?, ?, ?)",
  {syb_bcp_attribs => { identity_flag => 0,  identity_column => 0 }}});

There should be two closing parens,  }} instead of }}}

The connection string in the perldoc examples should include the
charset=iso_1 at least once .

The error handler example also can be improved.

You set the handler like this:

is wrong, it should be

as can be seen in the xblk.t file

There is a $msg  variable in the cslib_handler which is not defined,
shouldn't  it be $errmsg instead?

          sub cslib_handler {
            my ($layer, $origin, $severity, $errno, $errmsg, $osmsg,
$blkmsg) =

            print "Layer: $layer, Origin: $origin, Severity: $severity,
Error: $errno\n";
            print $msg;
            print $osmsg if($osmsg);
            print $blkmsg if $blkmsg;

            return 1 if($errno == 36)

            return 0;

I have also used the new "Experimental Bulk-Load Functionality" of
DBD::Sybase, with some success. It works fine with the first 10 rows of
each table, although with the full tables it creates error messages and
then it crashes perl.exe .

DBD::Sybase::st execute failed: cs_convert: cslib user api layer: common
library error: The conversion/operation was stopped due to a syntax
error in the source field. at line 215.

Maybe I can find out myself what the data conversion issue is here. Date
values, blob lengths or others.


> Michael