FreeTDS Development Group

["Thompson, Bill D (London)" <bill_d_thompson AT ml.com>]

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/>

==============================================================================