FreeTDS Development Group

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

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