sybperl-l Archive

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

        #DBI->trace(5);

        #$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
server\n";

                $dbh_target->commit;

            }



        }

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

        $dbh_target->commit;

        $sth_target->finish;

        #DBI->trace(0);



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

Knut
> 
> 
> 
> 
> 
> 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 scr.pl line 215.
> 
> Maybe I can find out myself what the data conversion issue is here. Date
> values, blob lengths or others.
> 
> 
> Knut