[freetds] bcp timestamp trace

Lowden, James K LowdenJK at bernstein.com
Tue Apr 13 11:43:17 EDT 2004


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





More information about the FreeTDS mailing list