FreeTDS Development Group

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

> From: ZIGLIO Frediano [mailto:Frediano.Ziglio AT vodafoneomnitel.it]
> Sent: April 29, 2003 5:11 AM
> 
> I work a bit on column flags. Server send information from 
> column to client. I checked and updated these information including:
> - column is a key
> - column is writable
> - column is an identity
> - column is hidden
> - column is nullable

Yay! 

> CREATE TABLE table_name
> (
>       id NUMERIC(10,0) IDENTITY PRIMARY KEY, -- I used 
> NUMERIC cause is more portable
>       b VARCHAR(10) NULL,
>       c INT NOT NULL
> )
> 
> And do a "SELECT c,b FROM table_name FOR BROWSE" 3 columns 
> are returned (TODO: do not show third column to client)
> c: not nullable and writable (nullable not returned from TDS4.2)
> b: nullable and writable (nullable not returned from TDS4.2)
> id: writable, identity, key, hidden (some db do not return 
> key for identity)
> 
> IMHO BCP should issue FOR BROWSE select and test if a column 
> is key and/or identity (these flags are valid in all version 
> of TDS). However I don't know how BCP should handle id/key 
> fields... set identity_insert on ??? do not insert these 
> field assuming db generate it (perhaps we lost foreign key) ??

BCP doesn't care about keys, and many times they're not declared: the old
style "create unique clustered index I on T(A,B)" has the same effect (and
lets A or B be null).  

You're right that freebcp will need to discover Identity columns.  Another
way to do that would be, "select colid, name from syscolumns where 0 <>
status & 0x80 and object_name(id) = '<tablename>'" will do the job on any
server afaik.  Not sure about ASA.  

In double checking this information, I came across this description for
syscolumns.status from Sybase:

Bits 0-2 (values 1, 2, and 4) indicate bit positioning if the column uses
the bit datatype. If the column uses the text/image datatype, bits 0 and 1
indicate replication status as follows:
    * 01 = always replicate
    * 10 = replicate only if changed
    * 00 = never replicate
Bit 3 (value 8) indicates whether NULL values are legal in this column.
Bit 4 (value 16) indicates whether more than one check constraint exists for
the column.
Bits 5 and 6 are used internally.
Bit 7 (value 128) indicates an identity column.
Bit 8 is unused.

Poor Bit 8 must be feeling a bit crowded, don't you think?  

--jkl

http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/refman/@Generic__Boo
kTextView/135819;pt=129523/*?DwebQuery=syscolumns&DwebSearchAll=1#X



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.