FreeTDS Development Group

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

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

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.