[freetds] bcp, for browse and flags

Thompson, Bill D (London) ThompBil at exchange.uk.ml.com
Tue Apr 29 11:21:38 EDT 2003


Hi,

Do we hreally have an issue, or have I misunderstood...?

in tds_get_data_info (TDS 5) we have :

   curcol->column_identity = (curcol->column_flags & 0x40) > 1;

in tds7_get_data_info (TDS 7) we have :

   curcol->column_identity = (curcol->column_flags & 0x10) > 0;

so we can get this already from the dummy select issued in bcp_init()....

Bill

> -----Original Message-----
> From:	Lowden, James K [SMTP:LowdenJK at bernstein.com]
> Sent:	29 April 2003 16:00
> To:	'FreeTDS Development Group'
> Subject:	RE: [freetds] bcp, for browse and flags
> 
> > 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__B
> oo
> 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.
> 
> 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds



More information about the FreeTDS mailing list