freetds AT lists.ibiblio.org
Subject: FreeTDS Development Group
List archive
Re: [freetds] db-lib: support for new MS SQL 2008 data types - part 3
- From: LacaK <lacak AT zoznam.sk>
- To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
- Subject: Re: [freetds] db-lib: support for new MS SQL 2008 data types - part 3
- Date: Mon, 19 May 2014 13:10:18 +0200
Frediano Ziglio wrote / napísal(a):
2014-04-22 7:10 GMT+01:00 LacaK <lacak AT zoznam.sk>:ok
Frediano Ziglio wrote / napísal(a):
2014-04-17 6:26 GMT+01:00 LacaK <lacak AT zoznam.sk>:I agree with this "single type"
Frediano Ziglio wrote / napísal(a):I'll fix it. It's a quite different code path compared to the one you
2014-04-16 12:44 GMT+01:00 LacaK <lacak AT zoznam.sk>:May be, I have never used bcp_* functions ;-)
Hi again,Great! Yesterday I discovered a problem with BCP adding some tests for
Now I am personally happy with patched db-lib in regards of support new
DATE
- TIME data types.
these new types, still to fix.
are working on. But is still related to same data types.
Well.. TDS_DATETIME4 and TDS_DATETIME have same representation of wireBut there are still missing some parts, which may be interesting tocould work
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 ?
- introduce new DBDATETIMEALL struct (==TDS_DATETIMEALL struct) inMmm... well... could be or not. Microsoft for ODBC defined quite
sybdb.h
?
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 thatExactlly!
providing dbdata different from libTDS is far from easy.
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)
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.
Another reason is while wire for all date types are quite easy to putI have no objections, as I wrote I am also now perfectly happy with
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).
TDS_DATETIMEALL
All reasons you mentioned are from me POV valid and logical.
Well... all these looks quite paranoid but external ABI needs to staymay be
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;
time: perhaps would be better to just store the number from wirefor 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:-)) hm, so what will be the final form ?
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?
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.
I think we agree to:ok
- have a single structure
- add _res field and change precision bits (structure above)ok
- have time with fixed precisionok
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.
-
Re: [freetds] db-lib: support for new MS SQL 2008 data types - part 3,
Frediano Ziglio, 05/10/2014
-
Re: [freetds] db-lib: support for new MS SQL 2008 data types - part 3,
LacaK, 05/19/2014
- Re: [freetds] db-lib: support for new MS SQL 2008 data types - part 3, LacaK, 05/19/2014
- Re: [freetds] db-lib: support for new MS SQL 2008 data types - part 3, Frediano Ziglio, 05/19/2014
- <Possible follow-up(s)>
- Re: [freetds] db-lib: support for new MS SQL 2008 data types - part 3, Frediano Ziglio, 05/13/2014
-
Re: [freetds] db-lib: support for new MS SQL 2008 data types - part 3,
LacaK, 05/19/2014
Archive powered by MHonArc 2.6.24.