PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Michael Peppler <mpeppler at peppler dot org>
Subject: Bulk-Loading routines in DBD::Sybase
Date: Jul 13 2004 8:26AM

I've just uploaded DBD::Sybase 1.04.2 to
http://www.peppler.org/downloads/

This is a test version that includes support for using the BLK
(bulk-load) API from DBI's prepare/execute calls.

Sample code:

  my $dbh = DBI->connect('dbi:Sybase:server=MY_SERVER;bulkLogin=1',
$user, $pwd);
  my $sth = $dbh->prepare('insert the_table values(?, ?, ?, ?, ?)",
                          syb_bcp_attribs => { identity_flag => 0,
                                               identity_column => 0 }});
  while() {
    chomp;
    my @row = split(/\|/, $_);   # assume a pipe-delimited file...
    $sth->execute(@row);
  }
  $dbh->commit;
  print "Sent ", $sth->rows, " to the server\n";
  $sth->finish;

The number of placeholders *MUST* be the same as the number of columns
in the target table. The *order* of the columns must be the same as the
physical order of the columns on the server (i.e. any column list in the
INSERT statement will be ignored).
The "identity_flag" attribute should be set to 1 if your source data
includes values to be inserted to an IDENTITY column (i.e. similar to
the -E flag for bcp).
The "identity_column" attribute should be set to the column number of
the identity column of the target table if it exists, and if you want
Sybase to populate this column.
AutoCommit is ignored for this operation (it is always off). Calling
$dbh->commit will commit any non-committed rows, and will fail if any
row in the batch fails (i.e. duplicate insert error).
Calling $dbh->rollback will cancel the current batch, *and* will cancel
the BCP operation (i.e. no further rows can be inserted).
$sth->rows returns the number of rows committed for the latest call to
$dbh->commit().
$sth->finish will rollback any non-committed rows.
$sth->execute() will fail if there are any data conversion errors.

The new t/xblk.t script has a few examples and tests for various
conditions.

Bugs: the t/xblk.t script segfaults when using the 10.0.x OpenClient
libs (for linux), apparently a problem with the cs_diag() call. I don't
have any 11.1.x libs around to test with, so I don't know how extensive
this problem might be.

I haven't yet run any performance comparisons between this and a normal
bcp load.

Please give this a try/test if you are interested in using the BLK api,
or if you have situations where you need to load large amounts of data
fairly quickly.

Michael
-- 
Michael Peppler                              Data Migrations, Inc.
mpeppler@peppler.org                       http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short
or long term contract positions - http://www.peppler.org/resume.html