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: Stephen dot Sprague at msdw dot com
Subject: CPAN Upload: S/SP/SPRAGST/Sybase-Xfer-0.31.tar.gz (fwd)
Date: Feb 17 2001 6:36PM

Version 0.31 of is now available on CPAN. It still  uses
Sybase::DBlib - this I know - so no need to flame me about it.  I'll  be
trying to convert it over to DBD::Sybase within the next  6  months  and
probably applying a little more finesse one the guts to see what  I  can
to do speed it up.

Constructive feedback always appreciated!

As always with this list a nod goes to Michael Peppler and this time for
the idea of batching rows one at time inside Sybase::BCP when  a  server
error is detected. I liked it so much I put it in Sybase::Xfer.

Stephen Sprague

The README follows with the change log at the end. 

 Sybase::Xfer transfers data between two Sybase  servers  with  multiple
 options like specifying a where_clause, a smart auto_delete option  and
 can pump data from a perl subroutine or take a  plain  flat  file.  Has
 option, similiar to default behaviour in Sybase::BCP, to capture failed
 rows in a batch.
 Also comes with a command line wrapper, sybxfer.

 Also comes with a sister module

INSTALLATION (the usual)
   perl Makefile.PL [ LIB= ]
   make test
   make install

   Requires Perl Version 5.005 or beyond

   Requires packages:

   #from perl
      use Sybase::Xfer;
      $h = new Sybase::Xfer( %options );

   #from shell

DESCRIPTION (a little bit from the pod)

  If you're in an environment with multiple servers and you  don't  want
  to use cross-server joins then this module may be worth a  gander.  It
  transfers data from one server to another server row-by-row in memory
  w/o using an intermediate file. 

  To juice things up it can take data from any set of  sql  commands  as
  long as the output of the sql matches the  definition  of  the  target
  table. And it can take data from a  perl  subroutine  if  you're  into

  It also has some smarts to delete rows in the target table before  the
  data is  transferred  by  several  methods.  See  the  -truncate_flag,
  -delete_flag and -auto_delete switches.

  Everything is controlled by switch settings sent has  a  hash  to  the
  module. In essence one describes the from source and the to source and
  the module takes it from there.

  Error handling:

    An attempt was made to build in hooks for robust error reporting via
    perl callbacks. By default, it will print to stderr  the  data,  the
    column names, and their datatypes upon  error.  This  is  especially
    useful when sybase reports attempt to load an oversized row  warning

  Auto delete: 

    More recently the code has been  tweaked  to  handle  the  condition
    where data is bcp'ed into a table but the row already exists and the
    desired result to replace  the  row.  Originally,  the  -delete_flag
    option was meant for this condition. ie. clean out the table via the
    -where_clause before the bcp in was to occur. If this is  action  is
    too drastic, however, by using the -auto_delete option  one  can  be
    more precise and force only those rows about to be  inserted  to  be
    deleted before the bcp in begins. It will bcp the 'key'  information
    to a temp table, run a delete (in a loop so as not to blow  any  log
    space) via a join between the temp table and target table  and  then
    begin the bcp in. It's weird but in the right situation  it  may  be
    exactly what you want. Typically used to manually replicate a table.

   my e-mail: 

Version .31  12-feb-2001
* Fixed sloppy bug left in v.30 where if @ARGV is empty it'd  print  a
  usage statment. Duh! That  holds  for  the  script  sybxfer  but  it
  doesn't hold user written scripts.

Version .30  11-feb-2001

Some promised mods.

* New/modified switches as follows:

        -error_handling 'continue|abort|retry'
           added value 'retry'. Will behave like Sybase::BCP upon  error
           on bcp_batch. In other words, set  the  batchsize  to  1  and
           resend the data. Prints failed  records  to -error_data_file.
           (thanks to Michael Peppler for allowing me to raid his code.)
        -retry_max 'n'
           number of times to  retry  a  failed  bcp_batch.  Default  is

        -retry_deadlock_sleep 'sec'
           number of seconds to  sleep  between  sending  the  bcp_batch
           again iff a deadlock error is detected. Default is 120.

        -callback_err_batch 'coderef'
           implemented it. Now users can modify the rows  in  the  batch
           and request that xfer resend it or not. Default is Xfer's own
           routine   that   implements   the   most   useful   behaviour

        -error_data_file | -edf 
           contains records that  failed  to  transfer  in  addition  to
           source record number and Sybase erorr message for each record
           that failed.
        -from_file | -ff 
           new source. Take the  data  from  a  flat  file.  Must  be  a
           delimited file. Steals a chapter from Sybase's bcp.

        -from_file_delimiter | -ffd  
           used inconjunction with with -from_file. The delimeter can be
           regular expression. (similiar to -t switch in Sybase's bcp)
           if switch -from_table specified then this  switch  appends  a
           'holdlock' to internally generated  select  statement.  

           sets the program_name in master..sysprocesses table. Defaults
           to the script name. 

           modified such that rows are deleted in -batchsize increments
           so the log doesn't get blown.

* changed the final summary message slightly to report:
    1) num rows read from source
    2) num rows resent to target table (only if errors)
    3) num rows failed to be transferred

* if no args given it'll exit gracefully with a message to run perldoc

---------- Forwarded message ----------
Date: Tue, 13 Feb 2001 04:04:34 +0100
From: PAUSE 
To: Stephen J. Sprague ,
Subject: CPAN Upload: S/SP/SPRAGST/Sybase-Xfer-0.31.tar.gz

The uploaded file


has entered CPAN as

  file: $CPAN/authors/id/S/SP/SPRAGST/Sybase-Xfer-0.31.tar.gz
  size: 28518 bytes
   md5: 6b6aef15a600343f1cf803e3eec8fbb3

No action is required on your part
Request entered by: SPRAGST (Stephen J. Sprague)
Request entered on: Tue, 13 Feb 2001 03:03:32 GMT
Request completed:  Tue, 13 Feb 2001 03:04:33 GMT

        Virtually Yours,
        Id: paused,v 1.72 2000/12/26 15:12:53 k Exp k