FreeTDS Development Group

[Frediano Ziglio <freddyz77 AT tin.it>]

Il mar, 2004-04-13 alle 14:49, Thompson, Bill D (London) ha scritto:
> 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.
> 

I don't understand why we should need a trace here... first thing that
bcp does is check sql table definition. At this point bcp knows source
columns and destination columns. We know that we have some issue:
- source column is not in destination. Discard?
- there is no corresponding column in source. If destination is nullable
we can fill with null (just insert column name in INSERT BULK statement
and pass NULL at every row) or with default (just do not put column name
in INSERT BULK statement). What happen is column is not nullable and has
no default? Fail?
- destination is not writable:
 - computed. Do not import
 - identity. Omit or use IDENTITY_INSERT and insert it
 - timestamp. I don't think is possible to bypass and insert this column
so omit from INSERT BULK, server will fill data.

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

Confirm! timestamp is equivalent to a binary type, only usertype changes
(and it's always 0x50).

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

freddy77