FreeTDS Development Group

[Frediano Ziglio <freddy77 AT gmail.com>]

Il giorno ven, 06/05/2011 alle 18.33 -0400, jklowden AT schemamania.org ha
scritto:
> Some notes on the new DATE type.  
> 
> http://msdn.microsoft.com/en-us/library/bb677301.aspx
> 
> Type          Token   Size
> ---------------       ------  --------
> DATE          0x28     3 bytes
> TIME          0x29     6 bytes
> DATETIME2     0x2a     9 bytes
> DATETIMEOFFSET        0x2b    11 bytes
> 
> Sample data:
> 
> 1> select * from d
> 2> go
>  name                 smdate
>  -------------------- --------------------
>  a                    1960-01-01
>  a                    1960-02-01
>  a                    1960-03-01
>  a                    1960-04-01
>  a                    1960-06-01
>  a                    1960-10-01
>  a                    1960-11-01
>  a                    1960-12-01
>  
> 1> select cast(smdate as varbinary) from d
> 2> go
> 
>  -----------------------------------------
>  0xf5ea0a
>  0x14eb0a
>  0x31eb0a
>  0x50eb0a
>  0x8deb0a
>  0x07ec0a
>  0x26ec0a
>  0x44ec0a
> 
> These values consistent with the windump+pcap capture below.  Notable: 
> 
> 1.  tds.html is out of date versus our current knowledge. Cf. the size of
> variable-length data in a D1 packet.  
> 
> 2.  Is there a new 2-byte field after the flags in an 0x81 packet? 
> 
> 3.  Size of DATE is always given as 3, apparently, even though the 0x81
> provides no size, implying it's fixed-length.  
> 
> 4.  Even with TDSVER=7.2, bsqlodbc receives DATE from the server as 
> varchar(20).
>  I don't know yet how to convince the server to send DATE tokens.  
> 
> I haven't decoded the values yet.  ISTR discussion, or maybe code, but 
> neither
> we nor jTDS documents the type afaik.  
> 
> Dump follows.  It's an extract of a windump capture of a BCP.EXE session
> uploading 8 rows.  
> 
> --jkl
> 
> 
> 0x0000:  4500 00ab 7870 4000 8006 0000 0a51 259b  E...xp@......Q%.
> 0x0010:  0a52 5092 f5d7 09c4 793a a33a b071 8e11  .RP.....y:.:.q..
> 0x0020:  5018 3fe8 8b6d 0000 0701 0083 0000 0100  P.?..m..........
> 0x0030:  8102 0000 0000 000a 00a7 1400 0904 0001  ................
> 0x0040:  0004 6e00 6100 6d00 6500 0000 0000 0800  ..n.a.m.e.......
> 0x0050:  2806 7300 6d00 6400 6100 7400 6500 d101  (.s.m.d.a.t.e...
> 0x0060:  0061 03f5 ea0a d101 0061 0314 eb0a d101  .a.......a......
> 0x0070:  0061 0331 eb0a d101 0061 0350 eb0a d101  .a.1.....a.P....
> 0x0080:  0061 038d eb0a d101 0061 0307 ec0a d101  .a.......a......
> 0x0090:  0061 0326 ec0a d101 0061 0344 ec0a fd00  .a.&.....a.D....
> 0x00a0:  0000 0000 0000 0000 0000 00              ...........
> 
> Analysis:
> 
> 0x0000:  4500 00ab 7870 4000 8006 0000 0a51 259b  E...xp@......Q%.
> 0x0010:  0a52 5092 f5d7 09c4 793a a33a b071 8e11  .RP.....y:.:.q..
> 0x0020:  5018 3fe8 8b6d 0000 0701 0083 0000 0100  P.?..m..........
> 0x0030:  
> 
> 81    packet: tds result
> 02 00 2 columns
> 
> 00 00 usertype
> 00 00 flags
> 0a 00 ???
> a7    XSYBVARCHAR
> 14 00         size: 20 bytes
> 09 04 00 01 00        collation
> 04    namelen: 4 UCS-2 characters
> 6e00 6100 6d00 6500 "name"
> 
> 00 00 usertype
> 00 00         flags
> 08 00 ???
> 28    new type: TDSMSDATE
>       (no size)
>       (no collation)
> 06    namelen: 6 UCS-2 characters
> 7300 6d00 6400 6100 7400 6500         "smdate"
> 
> d1    packet: data row
> 0100  size: 1 single-byte character
> 61    "a"
> 03    size: 3 bytes, N.B. NOT NULL! 
> f5 ea 0a 
> 
> d1    packet: data row
> 01    size: 1 single-byte character
> 61    "a"
> 03    size: 3 bytes
> 14 eb 0a 
> 
> d1    packet: data row
> 0100  size: 1 single-byte character
> 61    "a"
> 03    size: 3 bytes
> 31 eb 0a 
> 
> d1    packet: data row
> 01    size: 1 single-byte character
> 61    "a"
> 03    size: 3 bytes
> 50 eb 0a 
> 
> d1    packet: data row
> 0100  size: 1 single-byte character
> 61    "a"
> 03    size: 3 bytes
> 8d eb 0a 
> 
> d1    packet: data row
> 01    size: 1 single-byte character
> 61    "a"
> 03    size: 3 bytes
> 07 ec 0a 
> 
> d1    packet: data row
> 0100  size: 1 single-byte character
> 61    "a"
> 03    size: 3 bytes
> 26 ec 0a 
> 
> d1    packet: data row
> 01    size: 1 single-byte character
> 61    "a"
> 03    size: 3 bytes
> 44 ec 0a 
> 
> fd    packet: Result Set Done
> 0000  flags
> 0000  officially unknown? 
> 00 00 00 00
> 00 00 00 00   row count: 0
> 

Working on 7.3 protocol ? Good.

I'll post some patches. DATE is correctly fixed but it's also
nullable... some thing like UNIQUE.

Frediano