Chip 2000 May

home *** CD-ROM | disk | FTP | other *** search

/ Chip 2000 May / Chip_2000-05_cd1.bin / zkuste / Perl / ActivePerl-5.6.0.613.msi / 䆊䌷䈹䈙䏵-䞅䞆䞀㡆䞃䄦䠥 / _ed7369b4731ecc9237887534e1c5e08d < prev next >

Wrap

Text File | 2000-03-23 | 11.5 KB | 296 lines

<HTML> <HEAD> <TITLE>Sybase::BCP - Simple front end to the Sybase BCP API</TITLE> <LINK REL="stylesheet" HREF="../../../Active.css" TYPE="text/css"> <LINK REV="made" HREF="mailto:"> </HEAD> <BODY> <TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0 WIDTH=100%> <TR><TD CLASS=block VALIGN=MIDDLE WIDTH=100% BGCOLOR="#cccccc"> Sybase::BCP - Simple front end to the Sybase BCP API </TD></TR> </TABLE> <A NAME="__index__"></A>  <UL> <LI><A HREF="#name">NAME</A></LI><LI><A HREF="#supportedplatforms">SUPPORTED PLATFORMS</A></LI> <LI><A HREF="#synopsis">SYNOPSIS</A></LI> <LI><A HREF="#description">DESCRIPTION</A></LI> <UL> <LI><A HREF="#features">Features</A></LI> <LI><A HREF="#the following methods are available:">The following methods are available:</A></LI> <LI><A HREF="#configuration parameters">Configuration Parameters</A></LI> <LI><A HREF="#paramaters for config()">Paramaters for <CODE>config()</CODE></A></LI> <LI><A HREF="#parameters for describe()">Parameters for <CODE>describe()</CODE></A></LI> </UL> <LI><A HREF="#examples">EXAMPLES</A></LI> <LI><A HREF="#bugs">BUGS</A></LI> <LI><A HREF="#author">AUTHOR</A></LI> </UL>  <HR> <H1><A NAME="name">NAME</A></H1> Sybase::BCP - Simple front end to the Sybase BCP API <HR> <H1><A NAME="supportedplatforms">SUPPORTED PLATFORMS</A></H1> <UL> <LI>Windows</LI> </UL> <HR> <H1><A NAME="synopsis">SYNOPSIS</A></H1> <PRE> use Sybase::BCP;</PRE> <PRE> $bcp = new Sybase::BCP ...; $bcp->config(...); $bcp->run;</PRE> <HR> <H1><A NAME="description">DESCRIPTION</A></H1> The Sybase::BCP module serves as a simplified front end for Sybase's Bulk Copy library. It is sub-classed from the <A HREF="../../../site/lib/Sybase/DBlib.html">the Sybase::DBlib manpage</A> 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 '|'. <PRE> #!/usr/local/bin/perl</PRE> <PRE> use Sybase::BCP;</PRE> <PRE> $bcp = new Sybase::BCP $user, $passwd; $bcp->config(INPUT => 'foo.bcp', OUTPUT => 'mydb.dbo.bar', SEPARATOR => '|'); $bcp->run;</PRE> That's it! Of course, there are several things you can do to cater for non-standard input files (see Configuration Parameters, below). <H2><A NAME="features">Features</A></H2> <UL> <LI><A NAME="item_Automatic_conversions_from_non%2Dstandard_date_for">Automatic conversions from non-standard date formats.</A> <LI><A NAME="item_Automatic_retries_of_failed_batches%2E">Automatic retries of failed batches.</A> If there are errors in the input file, or if there are duplicat rows that are rejected, the invalid rows are stored in an error log file, and the batch is retried, so that only the failed rows are not uploaded. <LI><A NAME="item_Handles_column_reordering_and%2For_skipping_of_unn">Handles column reordering and/or skipping of unneeded data.</A> <LI><A NAME="item_Row_or_column_based_callbacks%2E">Row or column based callbacks.</A> Allows vetoing of rows, or arbitrary processing of data on input. </UL> <H2><A NAME="the following methods are available:">The following methods are available:</A></H2> <DL> <DT><A NAME="item_%24bcp_%3D_new_Sybase%3A%3ABCP_%5B%24user_%5B%2C_%">$bcp = new Sybase::BCP [$user [, $password [, $server [, $appname]]]]</A> <DD> Allocate a new BCP handle. Opens a new connection to Sybase via the Sybase::DBlib module, and enables BCP IN on this handle. <DT><A NAME="item_config">$bcp-><CODE>config([parameters])</CODE></A> <DD> Sets up the Bulk Copy operation. See Configuration Parameters below for details. <DT><A NAME="item_describe">$bcp->describe($colid, {parameters})</A> <DD> Adds a specific configuration element for column $colid. Columns are numbered starting at 1, as is standard in the Sybase APIs. <DT><A NAME="item_run">$bcp->run</A> <DD> Perform the BCP operation, returns the actual number of rows sent to the server. </DL> <H2><A NAME="configuration parameters">Configuration Parameters</A></H2> The general form for configuration is to pass (parameter => value) pairs via the <A HREF="#item_config"><CODE>config()</CODE></A> or <A HREF="#item_describe"><CODE>describe()</CODE></A> methods. Some parameters take slightly more complex arguments (see REORDER). <H2><A NAME="paramaters for config()">Paramaters for <A HREF="#item_config"><CODE>config()</CODE></A></A></H2> <DL> <DT><A NAME="item_DIRECTION">DIRECTION</A> <DD> The direction in which the bulkcopy operation is done. Can be 'IN' or 'OUT'. Default: 'IN' (Note: 'OUT' is not implemented yet.) <DT><A NAME="item_INPUT">INPUT</A> <DD> 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(). <DT><A NAME="item_OUTPUT">OUTPUT</A> <DD> Where BCP should place it's output. It's a table name for bcp IN, a filename for bcp OUT. <DT><A NAME="item_ERRORS">ERRORS</A> <DD> The file where invalid rows should be recorded. Default: bcp.err. <DT><A NAME="item_SEPARATOR">SEPARATOR</A> <DD> The pattern that separates fields in the input file, or that should be used to separate fields in the output file. Default: TAB. <DT><A NAME="item_RECORD_SEPARATOR">RECORD_SEPARATOR</A> <DD> 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. <DT><A NAME="item_FIELDS">FIELDS</A> <DD> 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. <DT><A NAME="item_BATCH_SIZE">BATCH_SIZE</A> <DD> 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. <DT><A NAME="item_NULL">NULL</A> <DD> A pattern to be used to detect NULL values in the input file. Defaults to a zero length string. <DT><A NAME="item_DATE">DATE</A> <DD> 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 <CODE>ctime(3)</CODE> 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. <DT><A NAME="item_REORDER">REORDER</A> <DD> 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: <PRE> $bcp->config(... REORDER => { 1 => 2, 3 => 1, 2 => 'foobar', 12 => 4}, ...);</PRE> 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. <DT><A NAME="item_CALLBACK">CALLBACK</A> <DD> 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: <PRE> $bcp->config(... CALLBACK => \&row_cb, ...);</PRE> <PRE> sub row_cb { my $row_ref = shift;</PRE> <PRE> # Skip rows where the first field starts with FOO: return undef if $$row_ref[0] =~ /^FOO/;</PRE> <PRE> 1; }</PRE> <DT><A NAME="item_CONDITION">CONDITION</A> <DD> A where clause to be used in bcp OUT operations. Not implemented. </DL> <H2><A NAME="parameters for describe()">Parameters for <A HREF="#item_describe"><CODE>describe()</CODE></A></A></H2> <DL> <DT>CALLBACK <DD> 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: <PRE> $dbh->describe(2, {CALLBACK, \&col_cb});</PRE> <PRE> sub col_cb { my $data = shift;</PRE> <PRE> # Convert to lower case... $data =~ tr/A-Z/a-z/; }</PRE> <DT><A NAME="item_SKIP">SKIP</A> <DD> 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. </DL> <HR> <H1><A NAME="examples">EXAMPLES</A></H1> <PRE> #!/usr/local/bin/perl </PRE> <PRE> use Sybase::BCP; require 'sybutil.pl';</PRE> <PRE> $bcp = new Sybase::BCP sa, undef, TROLL;</PRE> <PRE> $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;</PRE> <HR> <H1><A NAME="bugs">BUGS</A></H1> The current implementation seems to run about 2.5 to 3 times slower than plain bcp. <HR> <H1><A NAME="author">AUTHOR</A></H1> Michael Peppler <<A HREF="mailto:mpeppler@mbay.net">mpeppler@mbay.net</A>>. Contact the sybperl mailing list <CODE>mailto:sybperl-l@trln.lib.unc.edu</CODE> if you have any questions. <TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0 WIDTH=100%> <TR><TD CLASS=block VALIGN=MIDDLE WIDTH=100% BGCOLOR="#cccccc"> Sybase::BCP - Simple front end to the Sybase BCP API </TD></TR> </TABLE> </BODY> </HTML>