Re: db-lib: support for new MS SQL 2008 data types - part 3
LacaK <[email protected]>
| Newsgroups | gmane.comp.db.tds.freetds |
|---|---|
| Message-ID | <[email protected]> |
Frediano Ziglio wrote / napísal(a): > 2014-04-22 7:10 GMT+01:00 LacaK <[email protected]>: > >> Frediano Ziglio wrote / napísal(a): >> >>> 2014-04-17 6:26 GMT+01:00 LacaK <[email protected]>: >>> >>> >>>> Frediano Ziglio wrote / napísal(a): >>>> >>>> >>>> >>>>> 2014-04-16 12:44 GMT+01:00 LacaK <[email protected]>: >>>>> >>>>> >>>>> >>>>>> 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. _______________________________________________ FreeTDS mailing list [email protected] http://lists.ibiblio.org/mailman/listinfo/freetds