[freetds] db-lib: support for new MS SQL 2008 data types - part 3

LacaK lacak at zoznam.sk
Mon May 19 07:10:18 EDT 2014


Frediano Ziglio  wrote / napísal(a):
> 2014-04-22 7:10 GMT+01:00 LacaK <lacak at zoznam.sk>:
>   
>> Frediano Ziglio  wrote / napísal(a):
>>     
>>> 2014-04-17 6:26 GMT+01:00 LacaK <lacak at zoznam.sk>:
>>>
>>>       
>>>> Frediano Ziglio  wrote / napísal(a):
>>>>
>>>>
>>>>         
>>>>> 2014-04-16 12:44 GMT+01:00 LacaK <lacak at zoznam.sk>:
>>>>>
>>>>>
>>>>>           
>>>>>> Hi again,
>>>>>> Now I am personally happy with patched db-lib in regards of support new
>>>>>> DATE
>>>>>> - TIME data types.
>>>>>>
>>>>>>
>>>>>>             
>>>>> Great! Yesterday I discovered a problem with BCP adding some tests for
>>>>> these new types, still to fix.
>>>>>
>>>>>
>>>>>
>>>>>           
>>>> May be, I have never used bcp_* functions ;-)
>>>>
>>>>
>>>>         
>>> I'll fix it. It's a quite different code path compared to the one you
>>> are working on. But is still related to same data types.
>>>
>>>
>>>       
>>>>         
>>>>>> But there are still missing some parts, which may be interesting to
>>>>>> somebody
>>>>>> else.
>>>>>> I meant dbbind() family of API with corresponding *BIND constants and
>>>>>> binary
>>>>>> structure used to store this types.
>>>>>>
>>>>>> How to handle binding of new date, time types ?
>>>>>>
>>>>>> - introduce new DBDATETIMEALLBIND (or DBDATETIME2BIND) constant in
>>>>>> sybdb.h ?
>>>>>>
>>>>>>
>>>>>>             
>>>>> could work
>>>>>
>>>>>
>>>>>
>>>>>           
>>>>>> - introduce new DBDATETIMEALL struct (==TDS_DATETIMEALL struct) in
>>>>>> sybdb.h
>>>>>> ?
>>>>>>
>>>>>>
>>>>>>             
>>>>> Mmm... well... could be or not. Microsoft for ODBC defined quite
>>>>> different structures (one more similar to DBDATEREC). TDS_DATETIMEALL
>>>>> is neither from TDS protocol neither intended to be presented to
>>>>> clients. It's a mix of TDS protocols, numeric, old dates (values are
>>>>> the same as dtdays).
>>>>>
>>>>>           
>>>>         
>>>>>  On the other end I could understand that
>>>>> providing dbdata different from libTDS is far from easy.
>>>>>
>>>>>
>>>>>
>>>>>           
>>>> Exactlly!
>>>> It is main reason why I am still speaking about TDS_DATETIMEALL ;-)
>>>> And as I already wrote because of similarity of:
>>>> SQL Server       libTDS                          DB-Lib
>>>> ------------------------------------------------------------
>>>> datetime        -> TDS_DATETIME   == DBDATETIME
>>>> smalldatetime ->TDS_DATETIME4 == DBDATETIME4
>>>>
>>>> I will be happy also with SQL_TIMESTAMP_STRUCT (or other struct), to be
>>>> public structure for these new date time data types, but IMO then this
>>>> struct must be used also internaly by libTDS to store values in record
>>>> buffer. Because if libTDS will continue use TDS_DATETIMEALL then it will
>>>> significantly complicate things on db-lib level. (as there will be
>>>> required
>>>> conversion in many places)
>>>>
>>>>
>>>>         
>>> Well.. TDS_DATETIME4 and TDS_DATETIME have same representation of wire
>>> bytes (unless bit endian is different) and are documented in dblib.
>>> TDS_DATETIMEALL is neither wire neither documented (so no ABI).
>>> Unfortunately dbdata wants a binary representation of each data.
>>> ctlib... I don't remember. ODBC either wants a bind or data get read
>>> into user provided buffers (SQLGetData). Actually ODBC have separate
>>> types for each MS type. The reason I added this libTDS type is that is
>>> easier during the conversion to have a single type to work with.
>>>
>>>       
>> I agree with this "single type"
>>
>>
>>     
>>> Another reason is while wire for all date types are quite easy to put
>>> directly into a structure these new types are quite different. The
>>> size of date is 3 bytes so computers cannot handle directly (you need
>>> to stick the 3 bytes into a single 32 bit integer) while seconds and
>>> fraction size are from 3 to 5 bytes (same problem). This is the reason
>>> for the two time and date fields. Obviously to store 5 bytes we need
>>> at least a 8 byte integer. Somebody could say that an 8 bytes integer
>>> is enough (3+5 = 8) and it's true but all datetime structure keeps
>>> date and time separate and mostly of the time this would lead to just
>>> some extra multiplication/division. Another thing about date. The zero
>>> from the wire represent a date like 1-1-0... now, gregorian calendar
>>> (the one we use) was introduced in 1592 so before they have different
>>> calendar (month days and months order changed). So this zero is quite
>>> artificial. This is why I preferred to set zero for this structure to
>>> 1-1-1900. About seconds wire send the number with precision so
>>> 00:00:01 is 1 for TIME(0) and is 100 for TIME(2). Actually the
>>> structure always set this number as precision was 7. About bit fields.
>>> These mainly are reduntant as they came directly from the type. They
>>> are not on the data wire (precision is a field in the metadata), this
>>> is similar to scale/precision for numeric data (which are in metadata
>>> while we copy in libTDS data).
>>>
>>>       
>> I have no objections, as I wrote I am also now perfectly happy with
>> TDS_DATETIMEALL
>> All reasons you mentioned are from me POV valid and logical.
>>
>>
>>     
>>> Well... all these looks quite paranoid but external ABI needs to stay
>>> so is better to decide what to stick into the dbdata structure!
>>> date: perhaps would be better to just store the number from wire
>>> (converted to 32 bit) without bias;
>>>
>>>       
>> may be
>>
>>
>>     
>>> time: perhaps would be better to just store the number from wire
>>>
>>>       
>> for me is better solution have time "normalized" to fixed precision -
>> TIME(7)
>> in other cases I will must evaluate on each access "time_prec" to obtain
>> information if f.e. "1" means 1 second or 1 millisecond or so.
>>
>>
>>     
>>> time_spec: use 3 bit instead of 4 ? We just need a range from 0 to 7.
>>>
>>>       
>> may be
>>
>>
>>     
>>> Another idea could be to use a single byte instead and separate all
>>> other flags. As compiler usually reserve bits from the bottom and as
>>> this bitfield is the first is much easier for the cpu to extract this
>>> number. Personally I would keep the bitfield reducing to 3 bits.
>>> has_time, has_date and has_offset: they are fine. The only change I
>>> would insert a TDS_USMALLINT _res:10 before. In such was all the
>>> single bits will occupy the top position leaving space for extensions.
>>> Order of the fields are optimized to reduce structure size.
>>>
>>> Do you think these changes are reasonable?
>>>       
>> :-)) hm, so what will be the final form ?
>> typedef struct
>> {
>>    TDS_UINT8   time;
>>    TDS_INT      date;
>>    TDS_SMALLINT offset;
>>    TDS_USMALLINT _res:10;    // <-- NEW (so total count of bits will be 16)
>> ?
>>    TDS_USMALLINT time_prec:3; // <-- CHANGED ?
>>    TDS_USMALLINT has_time:1;
>>    TDS_USMALLINT has_date:1;
>>    TDS_USMALLINT has_offset:1;
>> } TDS_DATETIMEALL;
>>
>>     
>
> Quite similar:
>
> typedef struct
> {
>    TDS_UINT8   time;
>    TDS_INT      date;
>    TDS_SMALLINT offset;
>    TDS_USMALLINT time_prec:3;
>    TDS_USMALLINT _res:10;
>    TDS_USMALLINT has_time:1;
>    TDS_USMALLINT has_date:1;
>    TDS_USMALLINT has_offset:1;
> } TDS_DATETIMEALL;
>
> I think I'll go with this. time with fixed precision is ok for me.
>   
ok

> I think we agree to:
> - have a single structure
>   
ok

> - add _res field and change precision bits (structure above)
>   
ok

> - have time with fixed precision
>   
ok

> I'm not quite sure about date offset.
>   
what do you think here ?

May be that it is not related, but I noticed that FreeTDS converts 
"datetimeoffset" to "datetime2" bit different than does SQL Server.
For example we have any table "t" with "datetimeoffset" column named 
"dto", with value : '2014-05-19 12:50:00 +01:00'

When I do in SQL Server:
  select dto, cast(dto as datetime2) as dt2 from t;

I receive:
  dto                                            dt2
  ------------------------------------------------------
  2014-05-19 12:50:00 +01:00   2014-05-19 12:50:00

Which corresponds to: http://msdn.microsoft.com/en-us/library/bb630289.aspx
Where is stated: "The date and time are copied to the datetime2 value, 
and the time zone is truncated."

But when I am trying use dbconvert() which calls tds_convert() for 
srctype=SYBMSDATETIMEOFFSET to desttype=SYBMSDATETIME2 then I receive 
UTC datetime (shifted by time zone offset)

So converting (casting) in SQL Server is different from that used in 
FreeTDS ... is it as expected ?

Thanks
-Laco.



More information about the FreeTDS mailing list