Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

BCP in Sybase::DBLib

by prafulltc (Acolyte)
on Oct 20, 2011 at 11:35 UTC ( #932628=perlquestion: print w/replies, xml ) Need Help??
prafulltc has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks ,

We have to replace Sybase DBLib by DBI module.
I am stuck at bcp functions of sybase::DBLib.
As I have understod that BCP in dblib is done row by row, means not in bulk as done by bcp/freebcp.
So my question is that Whether I should use bcp utility i.e. frebcp using system command or
just open the file , read it and write insert query for every row of file.

Please suggest


Replies are listed 'Best First'.
Re: BCP in Sybase::DBLib
by jfroebe (Parson) on Oct 20, 2011 at 14:36 UTC

    You may want to use DBD::Sybase instead. That being said, use the bcp utility as it is much more reliable and faster. Don't be afraid of using non-Perl tools where appropriate. :)

    Jason L. Froebe

    Blog, Tech Blog

Re: BCP in Sybase::DBLib
by runrig (Abbot) on Oct 20, 2011 at 14:58 UTC

    Update: The wrappers mentioned below are released as: DBIx::BulkUtil. Sybase bcp, Oracle sqlldr, and SybaseIQ "LOAD TABLE" are covered.

    Go ahead and use bcp, you can even call it from perl. I have wrappers around bcp and sqlldr. No, sorry, I can't share them. Well, here's a bit of the bcp method:

    sub bcp_in { my $self = shift; my $opts = (ref $_[-1]) ? pop @_ : {}; my ( $table, $file, $dir ) = @_; # bcp_out is just bcp_in called with $dir = 'out' $file ||= "$table.bcp"; $dir ||= 'in'; ...snip my $pid = open(my $fh, "-|"); confess "Can't fork: $!" unless defined $pid; # UTF-8 doesn't work on HP - default is roman8 unless ($pid) { # Enclose in block so we don't get exec warning my $commit_size = $opts->{CommitSize} || 1000; my @max_errors = $opts->{MaxErrors} ? (-m => $opts->{MaxErrors} ) + : (); my @header_opt = ($opts->{Header} && $dir eq 'in') ? (-F => $opts- +>{Header}+1) : (); {exec("bcp", $bcp_table, $dir, $file, -U => $user, -P => $self->{PASSWORD}, # -J => "utf8", -S => $server, "-c", -t => $delimiter, -r => $row_delimiter, -b => $commit_size, @header_opt, @id_opt, @max_errors, )}; warn "Could not exec bcp: $!\n"; exit 1; } my $rows; local ($_, $.); while (<$fh>) { print; $rows = $1 if /^(\d+) rows copied/; } close $fh; confess "BCP error" unless defined $rows; return $rows; }
Re: BCP in Sybase::DBLib
by mpeppler (Vicar) on Nov 03, 2011 at 10:20 UTC
    Keep in mind that DBD::Sybase can use the BLK protocol - see DBD::Sybase


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://932628]
Approved by mertserger
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (4)
As of 2018-04-27 05:29 GMT
Find Nodes?
    Voting Booth?