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 3:30PM

I have rewritten my bcp-in operation now using the Experimental
Bulk-Load Functionality feature of DBD::Sybase instead of Sybase::BLK. I
think I won't touch Sybase::BLK anymore, although it would have been
optimal if Sybase::BLK were perfectly compatible with Sybase::BCP .

This is my new code, I'll post it here ... maybe someone else might get
inspired from this in the future, searching the list archive .

        # bcp-in to sybase: much faster load than insert-into statements

        # first perform some necessary commands such as

        # set identity insert table_name on

        my $idflag = 0;

        $idflag = 1 if ( exists $contbl1{$tg} or exists $contbl2{$tg} );
# or ... or ...


        #$dbh_target->begin_work;    #  optional

        my $sth_target = $dbh_target->prepare( "insert $tg values(" .
join( ", ", map { $_ = "?" } @fieldnames ) . ")",

            { syb_bcp_attribs => { identity_flag => $idflag,
identity_column => $idflag } } );

            # !!! assume position of identity-column is always 1 FIXME !!!!

        for ( my $i = 0 ; $i < @rows ; ++$i ) {
            # @rows is an array of arrays
            $sth_target->execute( @{ $rows[$i] } );

            if ( $i > BATCHSIZE && $i % BATCHSIZE == 0) {

                # BATCHSIZE defined at start of script with "use constant"

                print "$tg: Sent ", $sth_target->rows, " rows to the




        print "$tg: Tried to send ",  scalar @rows, " rows to the server\n";




My data conversion problems mentioned in the previous emails have
disappeared... after connecting to the "true" source database which
contains cleaner data.

For each table with an identity column, I still get only-one of these
messages. I don't know if it is really an error:

DBD::Sybase::st finish failed: Server message number=4820 severity=16
state=5 line=1 server=SYBASE_DC5 text=Illegal identity value specified
for identity column in table 'yyyyy' through BCP.

 The identity insert seems to work just fine. Maybe I have to look more
closely for some variant of an off-by-one-error, and really make use of
the cslib_handler  error handler function.

Thanks for helping

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