|
Sybase::BCP - Simple front end to the Sybase BCP API |
config()describe()
Sybase::BCP - Simple front end to the Sybase BCP API
use Sybase::BCP;
$bcp = new Sybase::BCP ...;
$bcp->config(...);
$bcp->run;
The Sybase::BCP module serves as a simplified front end for Sybase's Bulk Copy library. It is sub-classed from the the Sybase::DBlib manpage module, so all the features of the Sybase::DBlib module are available in addition to the specific Sybase::BCP methods.
So how does it work?
Let's say we want to copy the contents of a file name 'foo.bcp' into the table 'mydb.dbo.bar'. The fields in the file are separated by a '|'.
#!/usr/local/bin/perl
use Sybase::BCP;
$bcp = new Sybase::BCP $user, $passwd;
$bcp->config(INPUT => 'foo.bcp',
OUTPUT => 'mydb.dbo.bar',
SEPARATOR => '|');
$bcp->run;
That's it!
Of course, there are several things you can do to cater for non-standard input files (see Configuration Parameters, below).
config([parameters])
The general form for configuration is to pass (parameter => value) pairs
via the config() or describe() methods. Some parameters take slightly more
complex arguments (see REORDER).
config()For bcp IN INPUT can also be a reference to a perl subroutine that returns the array to be inserted via bcp_sendrow().
ctime(3) format), or the numbers 0-12,
100-112, corresponding to the conversion formats defined in table 2-4 of
the SQL Server Reference Manual.
BCP detects datetime targets by looking up the target table structure in the Sybase system tables.
$bcp->config(...
REORDER => { 1 => 2,
3 => 1,
2 => 'foobar',
12 => 4},
...);
In this example, field 1 of the input file goes in column 2 of the table, field 3 goes in column 1, field 2 goes in the column named foobar, and field 12 goes in column 4. Fields 4-11, and anything beyond 12 is skipped. As you can see you can use the column name instead of its position. The default is to not do any reordering.
$bcp->config(...
CALLBACK => \&row_cb,
...);
sub row_cb {
my $row_ref = shift;
# Skip rows where the first field starts with FOO:
return undef if $$row_ref[0] =~ /^FOO/;
1;
}
describe()
$dbh->describe(2, {CALLBACK, \&col_cb});
sub col_cb {
my $data = shift;
# Convert to lower case...
$data =~ tr/A-Z/a-z/;
}
#!/usr/local/bin/perl
use Sybase::BCP;
require 'sybutil.pl';
$bcp = new Sybase::BCP sa, undef, TROLL;
$bcp->config(INPUT => '../../Sybperl/xab',
OUTPUT => 'excalibur.dbo.t3',
BATCH_SIZE => 200,
FIELDS => 4,
REORDER => {1 => 'account',
3 => 'date',
2 => 'seq_no',
11 => 'broker'},
SEPARATOR => '|');
$bcp->run;
The current implementation seems to run about 2.5 to 3 times slower than plain bcp.
Michael Peppler <mpeppler@mbay.net>. Contact the sybperl mailing
list mailto:sybperl-l@trln.lib.unc.edu if you have any questions.
|
Sybase::BCP - Simple front end to the Sybase BCP API |