FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

Thanks, Bill.  

This is really grist for the UG, or a bcp.fmt(5) man page.  

I'll look at this again.  I got stuck trying to interpret the application of 
the format file to the bcp data structures.  I had trouble finding the spot 
where the file-table map is adjusted for column order.  And I'm not sure we 
have the table metadata when we read the format file. 

Having an understanding of the file format will definitely help, regardless.  

--jkl


> -----Original Message-----
> From: Thompson, Bill D (London) [mailto:bill_d_thompson AT ml.com]
> Sent: April 14, 2004 7:59 AM
> To: 'FreeTDS Development Group'
> Subject: RE: [freetds] bcp timestamp trace
> 
> 
> James,
> 
> I've just confirmed that freebcp does support format files, 
> including a
> "server column" value of zero which instructs freebcp to 
> ignore fields in
> the data file when it comes to loading.
> 
> the format file we support is a slightly cut-down version of 
> the current
> Sybase implementation
> 
> 1st line - a version number . 
> This must be present and needs to pass though atof(). It's ignored
> subsequently.
> 
> 2nd line - the number of columns.
> This should be equal to the number of records in the format file, not
> including lines 1 and 2. 
> Each column in the host data file should then be described by 
> one line in
> the format file.
> 
> Lines 3 thru n - each describe a field in the host data file
> Each of these lines consists of elements that should be 
> separated by tabs or
> spaces. 
> These elements are:
> 
> Host file column order. 
> The sequential number of the field in the host data file, which begins
> numbering at 1.
> 
> Host file datatype. 
> Refers to the storage format of the field in the host data 
> file, not the
> datatype of the database table column.
> valid values would be SYBCHAR, SYBINT4, SYBDATETIME etc.
> 
> Prefix length. 
> Describes the prefix length for the column in the host data file. 
> Values are zero (no prefix length in the data file for this 
> column), 1 , 2
> or 4
> 
> Host file data length. 
> refers to the maximum number of bytes to copy for the field. 
> 
> Terminator. A sequence of characters which must be enclosed 
> in (double)
> quotations. 
> If the corresponding field in the data file is terminated by 
> a tabstop, then
> this should be described in the format file as "\t" (newline, "\n")
> 
> Server column number. 
> Represents the column number in the database table into which 
> the host data
> file column is to be loaded. 
> Numbering begins at 1.
> Together with the host file column order, this element maps 
> host data file
> fields to the database table columns.
> A Server column number of zero indicates that this host data 
> file column is
> not loaded into the database table.
> 
> HTH,
> 
> Bill
> 
> 
> 
> 
> 
> 
>   
> 
> > -----Original Message-----
> > From:       Lowden, James K [SMTP:LowdenJK AT bernstein.com]
> > Sent:       13 April 2004 16:43
> > To: FreeTDS Development Group
> > Subject:    RE: [freetds] bcp timestamp trace
> > 
> > > From: Thompson, Bill D (London) [mailto:bill_d_thompson AT ml.com]
> > > Sent: April 13, 2004 8:49 AM
> > >
> > > > And I can't seem to
> > > > find the format of bcp.fmt on Sybase's site.  
> > > 
> > > You're right - it's not easy to find...here it is...
> > > 
> > >
> > 
> http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/util/@
> Generic__Boo
> > kView
> > 
> > Thanks.  I'll have to bookmark that.  
> > 
> > > I'm not sure that our format file processing does more than 
> > > the basics.
> > > 
> > > Given you can capture a trace file for MS's bcp. it would be 
> > > interesting to see how they handle the following:
> > > 
> > > 1) identity columns. 
> > ...
> > > 2) columns with default values. 
> > 
> > Can do.  I'll post them when I get a chance.  BTW, I use 
> windump, which is
> > a port of tcpdump, and I rely on the tcpdump man pages to use it.  
> > 
> > > It looks to me like the "user type" is the only thing we 
> can use to
> > > differentiate these columns.
> > > I agree with your strategy, it fits the known data.
> > 
> > I'm pretty sure the Sybase TDS document mentions this 
> particular use of
> > "user type".  I always thought of it literally, as a 
> user-defined type, as
> > in sp_addtype().  But it seems that in some cases, it's 
> more of a "used
> > as" type, to distinquish between the data format and role.  
> > 
> > > > It seems to me there should be some way to indicate
> > > > that a datafile column does not map to any column in the 
> > > target table,
> > > > but, again, I didn't find a way.  
> > > 
> > > You'd have to define a format file and specify a "server 
> > > column" of zero for
> > > any extra fields you want ignored.
> > > I'm not sure if our format file processing supports this, 
> though...
> > 
> > I don't suppose this is on your todo list?  ;-)
> > 
> > I'll post the traces; I'm sure you're right that some 
> things will become
> > clear.  Unfortunately, I suspect our table-to-file column 
> mapping doesn't
> > support the idea of a table column that will be skipped in 
> the "insert
> > bulk" statement based on its type.  
> > 
> > Welcome back.  
> > 
> > --jkl
> > 
> > 
> > > > -----Original Message-----
> > > > From:   James K. Lowden [SMTP:jklowden AT schemamania.org]
> > > > Sent:   07 April 2004 06:50
> > > > To:     FreeTDS Development Group
> > > > Subject:        Re: [freetds] bcp timestamp trace
> > > > 
> > > > On Mon, 5 Apr, "Lowden, James K" <LowdenJK AT bernstein.com> wrote:
> > > > > 
> > > > > 2.  "insert bulk dbname..tablename (ts int)".  
> > > > > 
> > > > > The DDL for the table:
> > > > > 
> > > > >       CREATE TABLE ts (ts int, cc timestamp)
> > > > > 
> > > > > So (in contradiction to comments I just committed), the 
> > > timestamp column
> > > > > is simply omitted from the query.  
> > > > 
> > > > I spent a couple of hours looking at how to handle this.  
> > > > 
> > > > Basically, we want to pretend the target table has no 
> > > timestamp column,
> > > > and we want to ignore the timestamp data in the file.  
> > > That's very similar
> > > > to what a bcp format file can do, IIRC.  Trouble is, 
> that code is
> > > > uncommented and, well, not as transparent as glass.  And I 
> > > can't seem to
> > > > find the format of bcp.fmt on Sybase's site.  
> > > > 
> > > > The strategy I have in mind: when we read the table 
> > > metadata from the
> > > > server, mark any timestamp column (user data type 0x50) as 
> > > "do not load". 
> > > > When building the bulk insert statement, exclude that 
> column.  When
> > > > reading the datafile, skip it, and do not allocate a field 
> > > in the row
> > > > buffer for the data.  It seems to me there should be some 
> > > way to indicate
> > > > that a datafile column does not map to any column in the 
> > > target table,
> > > > but, again, I didn't find a way.  
> > > > 
> > > > So, Bill, when you read this, I'd be interested in your 
> thoughts.  
> > > > 
> > > > --jkl
> > 
> > -----------------------------------------
> > The information contained in this transmission may contain 
> privileged and
> > confidential information and is intended only for the use 
> of the person(s)
> > named above. If you are not the intended recipient, or an 
> employee or
> > agent responsible for delivering this message to the 
> intended recipient,
> > any review, dissemination, distribution or duplication of this
> > communication is strictly prohibited. If you are not the intended
> > recipient, please contact the sender immediately by reply e-mail and
> > destroy all copies of the original message. Please note 
> that we do not
> > accept account orders and/or instructions by e-mail, and 
> therefore will
> > not be responsible for carrying out such orders and/or instructions.
> > If you, as the intended recipient of this message, the 
> purpose of which is
> > to inform and update our clients, prospects and consultants of
> > developments relating to our services and products, would 
> not like to
> > receive further e-mail correspondence from the sender, 
> please "reply" to
> > the sender indicating your wishes.  In the U.S.: 1345 Avenue of the
> > Americas, New York, NY 10105.
> > 
> > 
> > _______________________________________________
> > FreeTDS mailing list
> > FreeTDS AT lists.ibiblio.org
> > http://lists.ibiblio.org/mailman/listinfo/freetds
> 
> ==============================================================
> ================
> 
> If you are not an intended recipient of this e-mail, please notify
> the sender, delete it and do not read, act upon, print, disclose,
> copy, retain or redistribute it.
> 
> Click here for important additional terms relating to this e-mail.
>      <http://www.ml.com/email_terms/>
> 
> ==============================================================
> ================
> 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
> 
> 

-----------------------------------------
The information contained in this transmission may contain privileged and 
confidential information and is intended only for the use of the person(s) 
named above. If you are not the intended recipient, or an employee or agent 
responsible for delivering this message to the intended recipient, any 
review, dissemination, distribution or duplication of this communication is 
strictly prohibited. If you are not the intended recipient, please contact 
the sender immediately by reply e-mail and destroy all copies of the original 
message. Please note that we do not accept account orders and/or instructions 
by e-mail, and therefore will not be responsible for carrying out such orders 
and/or instructions.
If you, as the intended recipient of this message, the purpose of which is to 
inform and update our clients, prospects and consultants of developments 
relating to our services and products, would not like to receive further 
e-mail correspondence from the sender, please "reply" to the sender 
indicating your wishes.  In the U.S.: 1345 Avenue of the Americas, New York, 
NY 10105.