ActiveState!

ActivePerl Documentation
Table of Contents

(Usage Statistics)
(about this ver)


* Getting Started
    * Welcome To ActivePerl
    * Release Notes
    * Readme
    * ActivePerl Change Log
* Install Notes
    * Linux
    * Solaris
    * Windows
* ActivePerl Components
    * Overview
    * PPM
    * Windows Specifics
       * OLE Browser
       * PerlScript
       * Perl for ISAPI
       * PerlEZ
* ActivePerl FAQ
    * Introduction
    * Availability & Install
    * Using PPM
    * Docs & Support
    * Windows Specifics
       * Perl for ISAPI
       * Windows 9X/NT/2000
       * Quirks
       * Web Server Config
       * Web programming
       * Programming
       * Modules & Samples
       * Embedding & Extending
       * Using OLE with Perl
* Windows Scripting
    * Active Server Pages
    * Windows Script Host
    * Windows Script Components

Core Perl Documentation


* perl
* perlfaq
* perltoc
* perlbook

* perlsyn
* perldata
* perlop
* perlreftut
* perldsc
* perllol

* perllexwarn
* perldebug

* perlrun
* perlfunc
* perlopentut
* perlvar
* perlsub
* perlmod
* perlpod

* perlstyle
* perlmodlib
* perlmodinstall
* perltrap
* perlport
* perlsec

* perlref
* perlre
* perlform
* perllocale
* perlunicode

* perlboot
* perltoot
* perltootc
* perlobj
* perlbot
* perltie

* perlipc
* perlnumber
* perlfork
* perlthrtut

* perldiag
* perlfaq1
* perlfaq2
* perlfaq3
* perlfaq4
* perlfaq5
* perlfaq6
* perlfaq7
* perlfaq8
* perlfaq9

* perlcompile

* perlembed
* perlxstut
* perlxs
* perlguts
* perlcall
* perlfilter
* perldbmfilter
* perlapi
* perlintern
* perlapio
* perltodo
* perlhack

* perlhist
* perldelta
* perl5005delta
* perl5004delta

* perlamiga
* perlcygwin
* perldos
* perlhpux
* perlmachten
* perlos2
* perlos390
* perlvms
* perlwin32

Pragmas


* attributes
* attrs
* autouse
* base
* blib
* bytes
* charnames
* constant
* diagnostics
* fields
* filetest
* integer
* less
* lib
* locale
* lwpcook
* open
* ops
* overload
* perllocal
* re
* sigtrap
* strict
* subs
* utf8
* vars
* warnings

Libraries


* ActivePerl
    * DocTools
        * TOC
            * RDF
* AnyDBM_File
* Archive
    * Tar
* AutoLoader
* AutoSplit
* B
    * Asmdata
    * Assembler
    * Bblock
    * Bytecode
    * C
    * CC
    * Debug
    * Deparse
    * Disassembler
    * Lint
    * Showlex
    * Stackobj
    * Terse
    * Xref
* Benchmark
* Bundle
    * LWP
* ByteLoader
* Carp
    * Heavy
* CGI
    * Apache
    * Carp
    * Cookie
    * Fast
    * Pretty
    * Push
    * Switch
* Class
    * Struct
* Compress
    * Zlib
* Config
* CPAN
    * FirstTime
    * Nox
* Cwd
* Data
    * Dumper
* DB
* Devel
    * DProf
    * Peek
    * SelfStubber
* Digest
    * HMAC
    * HMAC_MD5
    * HMAC_SHA1
    * MD2
    * MD5
    * SHA1
* DirHandle
* Dumpvalue
* DynaLoader
* English
* Env
* Errno
* Exporter
    * Heavy
* ExtUtils
    * Command
    * Embed
    * Install
    * Installed
    * Liblist
    * MakeMaker
    * Manifest
    * Miniperl
    * Mkbootstrap
    * Mksymlists
    * MM_Cygwin
    * MM_OS2
    * MM_Unix
    * MM_VMS
    * MM_Win32
    * Packlist
    * testlib
* Fatal
* Fcntl
* File
    * Basename
    * CheckTree
    * Compare
    * Copy
    * CounterFile
    * DosGlob
    * Find
    * Glob
    * Listing
    * Path
    * Spec
        * Functions
        * Mac
        * OS2
        * Unix
        * VMS
        * Win32
    * stat
* FileCache
* FileHandle
* FindBin
* Font
    * AFM
* Getopt
    * Long
    * Std
* HTML
    * AsSubs
    * Element
    * Entities
    * Filter
    * Form
    * FormatPS
    * Formatter
    * FormatText
    * HeadParser
    * LinkExtor
    * Parse
    * Parser
    * TokeParser
    * TreeBuilder
* HTTP
    * Cookies
    * Daemon
    * Date
    * Headers
        * Util
    * Message
    * Negotiate
    * Request
        * Common
    * Response
    * Status
* I18N
    * Collate
* IO
    * Dir
    * File
    * Handle
    * Pipe
    * Poll
    * Seekable
    * Select
    * Socket
        * INET
        * UNIX
* IPC
    * Msg
    * Open2
    * Open3
    * Semaphore
    * SysV
* LWP
    * Debug
    * MediaTypes
    * MemberMixin
    * Protocol
    * RobotUA
    * Simple
    * UserAgent
* Math
    * BigFloat
    * BigInt
    * Complex
    * Trig
* MD5
* MIME
    * Base64
    * QuotedPrint
* NDBM_File
* Net
    * Cmd
    * Config
    * Domain
    * DummyInetd
    * FTP
    * hostent
    * libnetFAQ
    * netent
    * Netrc
    * NNTP
    * PH
    * Ping
    * POP3
    * protoent
    * servent
    * SMTP
    * SNPP
    * Time
* O
* ODBM_File
* Opcode
* Pod
    * Checker
    * Find
    * Html
    * InputObjects
    * Man
    * Parser
    * ParseUtils
    * Plainer
    * Select
    * Text
        * Color
        * Termcap
    * Usage
* POSIX
* PPM
    * SOAPClient
    * SOAPServer
* Safe
* SDBM_File
* Search
    * Dict
* SelectSaver
* SelfLoader
* SHA
* Shell
* SOAP
    * Defs
    * Envelope
    * EnvelopeMaker
    * GenericHashSerializer
    * GenericInputStream
    * GenericScalarSerializer
    * Lite
    * OutputStream
    * Packager
    * Parser
    * Transport
        * HTTP
            * Apache
            * CGI
            * Client
            * Server
        * LOCAL
        * MAILTO
        * POP3
        * TCP
    * TypeMapper
* Socket
* Symbol
* Sys
    * Hostname
    * Syslog
* Term
    * ANSIColor
    * Cap
    * Complete
    * ReadLine
* Test
    * Harness
* Text
    * Abbrev
    * ParseWords
    * Soundex
    * Tabs
    * Wrap
* Thread
    * Queue
    * Semaphore
    * Signal
    * Specific
* Tie
    * Array
    * Handle
    * Hash
    * RefHash
    * Scalar
    * SubstrHash
* Time
    * gmtime
    * Local
    * localtime
    * tm
* UDDI
    * Lite
* UNIVERSAL
* URI
    * data
    * Escape
    * file
    * Heuristic
    * ldap
    * URL
    * WithBase
* User
    * grent
    * pwent
* Win32
    * AuthenticateUser
    * ChangeNotify
    * Clipboard
    * Console
    * Event
    * EventLog
    * File
    * FileSecurity
    * Internet
    * IPC
    * Mutex
    * NetAdmin
    * NetResource
    * ODBC
    * OLE
        * Const
        * Enum
        * NEWS
        * NLS
        * TPJ
        * Variant
    * PerfLib
    * Pipe
    * Process
    * Registry
    * Semaphore
    * Service
    * Sound
    * TieRegistry
* Win32API
    * File
    * Net
    * Registry
* WWW
    * RobotRules
        * AnyDBM_File
* XML
    * Element
    * Parser
        * Expat
    * PPD
    * PPMConfig
    * ValidatingElement
* XSLoader

 Sybase::BCP - Simple front end to the Sybase BCP API


NAME

Sybase::BCP - Simple front end to the Sybase BCP API


SUPPORTED PLATFORMS

  • Windows
This module is not included with the standard ActivePerl distribution. It is available as a separate download using PPM.

SYNOPSIS

    use Sybase::BCP;
    $bcp = new Sybase::BCP ...;
    $bcp->config(...);
    $bcp->run;


DESCRIPTION

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

Features

The following methods are available:

$bcp = new Sybase::BCP [$user [, $password [, $server [, $appname]]]]
Allocate a new BCP handle. Opens a new connection to Sybase via the Sybase::DBlib module, and enables BCP IN on this handle.

$bcp->config([parameters])
Sets up the Bulk Copy operation. See Configuration Parameters below for details.

$bcp->describe($colid, {parameters})
Adds a specific configuration element for column $colid. Columns are numbered starting at 1, as is standard in the Sybase APIs.

$bcp->run
Perform the BCP operation, returns the actual number of rows sent to the server.

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

Paramaters for config()

DIRECTION
The direction in which the bulkcopy operation is done. Can be 'IN' or 'OUT'. Default: 'IN' (Note: 'OUT' is not implemented yet.)

INPUT
Where BCP should take it's input from. It's a filename for bcp IN, it's a table name for bcp OUT.

For bcp IN INPUT can also be a reference to a perl subroutine that returns the array to be inserted via bcp_sendrow().

OUTPUT
Where BCP should place it's output. It's a table name for bcp IN, a filename for bcp OUT.

ERRORS
The file where invalid rows should be recorded. Default: bcp.err.

SEPARATOR
The pattern that separates fields in the input file, or that should be used to separate fields in the output file. Default: TAB.

RECORD_SEPARATOR
The pattern that separates records (rows) in the input file. Sybase:BCP will set a local copy of $\ to this value before reading the file. Default: NEWLINE.

FIELDS
Number of fields in the input file for bcp IN operations. Default: Number of fields found in the first line. This parameter is ignored for bcp OUT.

BATCH_SIZE
Number of rows to be batched together before committing to the server for bcp IN operations. Defaults to 100. If there is a risk that retries could be requiered due to failed batches (e.g. duplicat rows/keys errors) then you should not use a large batch size: one failed row in a batch requires the entire batch to be resent.

NULL
A pattern to be used to detect NULL values in the input file. Defaults to a zero length string.

DATE
The default format for DATE fields in the input file. The parameter should be a symbolic value representing the format. Currently, the following values are recognized: CTIME (the Unix 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.

REORDER
The ordering of the fields in the input file does not correspond to the order of columns in the table, or there are columns that you wish to skip. The REORDER parameter takes a hash that describes the reordering operation:
    $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.

CALLBACK
The callback subroutine is called for each row (after any reordering), and allows the user to do global processing on the row, or vetoing it's processing. Example:
    $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;
    }

CONDITION
A where clause to be used in bcp OUT operations. Not implemented.

Parameters for describe()

CALLBACK
Specify a callback for this column. The field value is passed as the first parameter, and the callback should return the value that it wants BCP to use. Example:
    $dbh->describe(2, {CALLBACK, \&col_cb});
    sub col_cb {
        my $data = shift;
        # Convert to lower case...
        $data =~ tr/A-Z/a-z/;
    }

SKIP
If this is defined then this field is skipped. This is useful if only one or two fields need to be skipped and you don't want to define a big REORDER hash to handle the skipping.


EXAMPLES

    #!/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;


BUGS

The current implementation seems to run about 2.5 to 3 times slower than plain bcp.


AUTHOR

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