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

LacaK lacak at zoznam.sk
Tue Apr 22 02:10:32 EDT 2014

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 
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 - 
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;

>  Once decided we could
> define a new DBDATETIMEALL and check for future ABI changes.
>>>> - add support to copy_data_to_host_var() function in dblib.c ?
>>> Ehmm... what do you mean?
>> In db-lib is function copy_data_to_host_var() which is called when next
>> record is fetched to store fetched column values to local program variables
>> for bind columns.
>> If you look at this function there are performed type conversions very
>> simlar to dbconvert().
>> So we must add there also cases for SYBMSDATE...SYBMSDATETIMEOFFSET (like we
>> did for dbconvert())
> Looks like extending dbconvert. Looks like a lot of duplicated code.
> The base key is that dbconvert should convert between dblib type to
> dblib type while copy_data_to_host_var should be conversion from
> libTDS to dblib. The fact that dbdata returns mainly libTDS make the
> functions very similar. Well... we could on the other way instead of
> returning TDS_DATETIMEALL return different part of the structure for
> different type, so for MSTIME we return the time, for MSDATE return
> just date, MSDATETIME return first two fields and for MSDATETIMEOFFSET
> return first 3 fields. There would still be the problem of getting
> precision for the type.

Yes it is possible, but it complicates things.
Instead of one "struct" we will need define 4 new structs for each new 
So user can declare appropriate variables of this new types in his 
program, which will receive values of bind columns.

>>>> If you give me direction I can prepare patch ...
>>>> (if it is good idea add support for BINDing ?)
>>> Not good, excellent! And much easier as you don't have to store a
>>> buffer but you can provide what do you want to the client.
>> Yes
>> So only think we must agree on is : what "struct" we will use for new date
>> time data types ?
>> If it will be TDS_DATETIMEALL then it makes things easy and compact.
>> If it must be something else then my proposal is use same struct in libTDS
>> instead of TDS_DATETIMEALL.
> That is change TDS_DATETIMEALL or use as is !
Exactlly! ;-)
 From me point of view and for me needs is existing TDS_DATETIMEALL good 
(as it is understandable and easy for use).
But if you think, that it will be good change it, then I will of course 
accept you decision.


More information about the FreeTDS mailing list