FreeTDS Development Group

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

James,

As you know the Sybase & SQL protocols are COMPLETELY different.

For SQL, I take my clues from the manual page for their bcp. they have a -E
flag which states:

"-E
Specifies that the values for an identity column are present in the file
being imported. 
If -E is not given, the identity values for this column in the data file
being imported are ignored, 
and SQL Server 2000 automatically assigns unique values based on the seed
and increment values specified during table creation. 
If the data file does not contain values for the identity column in the
table or view, use a format file to specify that the identity column in the
table or view should be skipped when importing data; 
SQL Server 2000 automatically assigns unique values for the column. For more
information, see DBCC CHECKIDENT"
>From this, I suspect what happens is if there is an identity column in the
table (we can know this from the initial describing select we do from the
table in bcp_init()), then the bcp program uses the functions IDENT_SEED and
IDENT_INCR to work out values and manually assigns the values into the
"rows" it sends to the server.
It wouldn't be *too* difficult to do - though I haven't a clue what to do
for Sybase....

Bill
 

> -----Original Message-----
> From: Lowden, James K [SMTP:LowdenJK AT bernstein.com]
> Sent: 23 April 2003 15:23
> To:   'FreeTDS Development Group'
> Subject:      RE: [freetds] IDENTITY column problem with bcp
> 
> 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
> > To: 'FreeTDS Development Group'
> > Subject: RE: [freetds] IDENTITY column problem with bcp
> > 
> > 
> > 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
> > > To:       freetds AT lists.ibiblio.org
> > > Subject:  [freetds] IDENTITY column problem with bcp
> > > 
> > > Hi,
> > >   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
> > > _______________________________________________
> > > FreeTDS mailing list
> > > FreeTDS AT lists.ibiblio.org
> > > http://lists.ibiblio.org/mailman/listinfo/freetds
> > 
> > _______________________________________________
> > FreeTDS mailing list
> > FreeTDS AT lists.ibiblio.org
> > http://lists.ibiblio.org/mailman/listinfo/freetds
> > 
> 
> 
> 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