FreeTDS Development Group

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

Hi James,

> What would be helpful [he says innocently] would be for someone to do some
> traces to establish the interaction, and develop a test for identity
> insert/generate.  

> It's not clear to me yet that freebcp has to generate Identity values; it
> might be we should send a NULL (or otherwise coerce the server into
> generating them).  This needs some experimentation.  As for explicit
> insertion, it would seem we should "set identity insert on", but here
again
> empiricism is our friend.  

Agreed, a network sniff would be very useful. I'll see what I can do 
  
> Bill, where do you stand on all this?  Is bcp.c doing what you need?  IIRC
> you don't have any pressing need for blk-lib and no plan to implement bcp
> outside db-lib.  Is that right?

As of now I'm very happy with bcp within dblib. It does all I want.
Once I'm finished with Cursors (that's why I've been so quiet of late. Well
- that and a few production implementations), I reckon the last big gap in
ct-lib is the blk library, and I'd look to do that next. But I'm not ready
for that yet...

I've steered well clear of the latest snapshot since you began all the
charset stuff. Is it safe to back in the water yet ?
 
Best wishes, 

Bill 


> -----Original Message-----
> From: Lowden, James K [SMTP:LowdenJK AT bernstein.com]
> Sent: Monday, April 28, 2003 7:54 PM
> To:   'FreeTDS Development Group'
> Subject:      RE: [freetds] IDENTITY column problem with bcp
> 
> > From: Anwar, Mohammed [mailto:mohammed.anwar AT intel.com]
> > Sent: April 28, 2003 2:16 PM
> > 
> > James,
> >     It would help me a lot if we could get this in.
> >     The workaround (creating a temp table and then doing a 
> > select into the final table)        is going to slow down the data 
> > insertion.  
> >     
> > Thanks
> > Anwar
> 
> Acknowledged, and thanks for your message.  But please don't wait for me.
> 
> 
> I'm making a huge tear through the bcp code to support character set
> conversion.  The good news is _bcp_get_term_data() and the accompanying
> structures are gone.  Not that that matters to you, directly, but the
> resulting logic is less memory intensive and more charset-agnostic.
> Unfortunately, the bcp data structures are developing some ungainlyness,
> pending a switch to TDSCOLINFO.  I'm not sure how much more I want to push
> the existing bcp.c before moving it wholesale (including more rewriting)
> into libtds.  Partly that depends on what "push" means: what exactly does
> the code have to do differently?  
> 
> What would be helpful [he says innocently] would be for someone to do some
> traces to establish the interaction, and develop a test for identity
> insert/generate.  
> 
> It's not clear to me yet that freebcp has to generate Identity values; it
> might be we should send a NULL (or otherwise coerce the server into
> generating them).  This needs some experimentation.  As for explicit
> insertion, it would seem we should "set identity insert on", but here
> again
> empiricism is our friend.  
> 
> Bill, where do you stand on all this?  Is bcp.c doing what you need?  IIRC
> you don't have any pressing need for blk-lib and no plan to implement bcp
> outside db-lib.  Is that right?  
> 
> Working alone, I wouldn't expect to address Identity columns until post
> 0.62, pending UTF-8 happiness.  
> 
> Regards, 
> 
> --jkl
> 
> 
> > -----Original Message-----
> > From: Lowden, James K [mailto:LowdenJK AT bernstein.com]
> > Sent: Wednesday, April 23, 2003 7:23 AM
> > 
> > Bill, 
> > 
> > I'm working on bcp.c as part of the UTF-8 effort, including 
> > how columns are
> > described.  If you or anyone else can identify how the 
> > protocol signals the
> > server whether to honor or generate values for an Identity 
> > column, I could
> > probably add the logic.  
> > 
> > Current cvs will extract nchar columns and write UTF-8 data 
> > files if "client
> > charset" is UTF-8 in freetds.conf.  Uploading fails to convert [calls
> > tds_put_n()]; it simply sends the data verbatim.  As far as I 
> > can tell,
> > freebcp never could manage a round trip of nchar data: it 
> > would convert the
> > data to ASCII on extraction, but not "unconvert" them back to UCS-2 on
> > insertion.  
> > 
> > --jkl
> > 
> > > -----Original Message-----
> > > From: Thompson, Bill D (London) [mailto:ThompBil AT exchange.uk.ml.com]
> > > Sent: April 23, 2003 5:33 AM
> > > 
> > > Anwar,
> > > 
> > > Alas, I don't *think* that freebcp will support what you are 
> > > trying to do
> > > currently.
> > > Having had a look around the Sybase and Microsoft 
> > > documentation for their
> > > bcp implementations, it looks like we would have to enhance 
> > > freebcp to cater
> > > for bulk insert into tables with identity columns.
> > > 
> > > For the time being, all I can suggest is that you perform 
> > > your action in two
> > > stages:
> > > 
> > > 1) insert the data into a version of the table that does 
> > not have the
> > > identity column
> > > 2) copy the data over to the table that has the identity 
> > > column using a
> > > standard INSERT...SELECT statement, and let the server 
> > > allocate the identity
> > > column values for you.
> > > 
> > > Bill
> > > 
> > > > -----Original Message-----
> > > > From:   Anwar, Mohammed [SMTP:mohammed.anwar AT intel.com]
> > > > Sent:   22 April 2003 18:44
> > > > 
> > > >         I am trying to bulk insert values into a table with an
> Identity
> > > > column.
> > > >         
> > > >         If I do not include the column in my data I receive an error
> > > > indicating that I am trying to insert a NULL value into a 
> > > column that does
> > > > not accept null.
> > > > 
> > > >         If I insert a value then that value is actually 
> > > inserted into the
> > > > column (rather than created).
> > > >         I read in some post on the web to use 0 and that the server
> will
> > > > automatically insert the identity value.
> > > >         This does not work either.
> > > > 
> > > >         I have tried bcp both from a file and using the bind
> variables.
> > > >         
> > > >         I am using SQL Server 2000, the scripts are running on
> linux.
> > > >         The freetds.conf file indicates tds version = 7.0
> > > > 
> > > >         The table creation SQL is as follows: 
> > > >         CREATE TABLE [dbo].[TableName] (
> > > >                 [id] [bigint] IDENTITY (1, 1) NOT NULL ,
> > > >                 [signature] [bigint] NOT NULL ,
> > > >                 [name] [varchar] (128) COLLATE Latin1_General_BIN
> NULL
> > > >         ) ON [PRIMARY]
> > > > 
> > > >         I would like to insert the data from variables or a file
> without
> > > > specifying the value for the id column.
> > > >         Is there any way to specify to bcp that the id column is an
> > > > identity.
> > > > 
> > > >         The id field is also the primary key which I create 
> > > after the data
> > > > has been inserted. So I need for it to be NOT NULL.
> > > >         
> > > >         I would really appreciate some help on this.
> > > > 
> > > > Thanks
> > > > Anwar
> 
> 
> 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.
> 
> 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds