FreeTDS Development Group

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

Hey Jim,

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

AFAICR, I took the format file processing and layout from Sybase rather than
MS.
You're right - it's not easy to find...here it is...

http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/util/@Generic__BookV
iew

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. If you have a table with an identity column and a
normal "-c" format data file that includes a field for the identity column,
you can use the "-E" option command line option on MS's bcp to tell it to
take the identity column values from the file. If you omit tye "-E" either
bcp or the server will generate the identity values in the normal way. It
would be interesting to see how these options affect the INSERT BULK
statement.

2) columns with default values. You can use the "-k" option, which
"Specifies that empty columns should retain a null value during the bulk
copy operation, rather than have any default values for the columns
inserted."  I'd be interested to know how MS bcp handles default values
(specifically how it works out what the default value of a column should
be...and how it forces a null into the column)

Seeing how these work out may give us a deeper understanding of how to deal
with all these issues (timestamps, identity columns and default values)

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

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.

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

Bill
 

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

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