[freetds] dbcoltype char vs. datetimeoffset

[Chris Kings-Lynne]

Hi Guys,

The dbcoltype() function returns code 47 for both varchar and datetimeoffset fields...is there any way to distinguish these - as they really are _rather_ different...

Regards,

Chris

Chris Kings-Lynne
Development Manager
[navitasemail]
Navitas Limited
Level 2, Kirin Centre
15 Ogilvie Road
Mount Pleasant WA 6153

P: [phone]
M: 0409 294 078 | W: www.navitas.com<http://www.navitas.com/>
E: Chris.Kings-Lynne at navitas.com<mailto:Chris.Kings-Lynne at navitas.com>

**** When replying to this message for the first time, leave the entire message intact - just hit reply, and add your comments at the top. ****

------------- DISCLAIMER -------------
This email contains confidential information intended for the use of the addressee named above. If you are not the intended recipient you are hereby notified that any use, dissemination, distribution or reproduction of this email or the contained information is strictly prohibited by law.

If you have received this email in error, please delete and destroy all copies and notify Navitas Limited by telephone on [phone] or by replying to the sender. Except as required by law, Navitas Limited does not represent, warrant and/or guarantee that the integrity of this email has been maintained nor that the communication is free of errors, virus, interception or interference. Any views expressed in this message are those of the sender. You may not rely on this message as advice to act unless subsequently confirmed by fax or letter signed by an authorised representative of Navitas Limited.

P Consider the Environment before printing this email

-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 1605 bytes
Desc: image001.jpg
Url : Loading Image...

[James K. Lowden]

On Mon, 23 Sep 2013 02:58:44 +0000

Post by Chris Kings-Lynne
The dbcoltype() function returns code 47 for both varchar and
datetimeoffset fields...is there any way to distinguish these - as
they really are _rather_ different...

Less different than you might think, I'm afraid.

If you connect to a server that supports datetimeoffset with TDS 7.2,
you're using a protocol for which no datetimeoffset datatype is
defined. On your behalf, the server provides the data as a string,
which you see as VARCHAR. That's what's known as backwards
compatibility.

There are good reasons to update db-lib to support the new
datatypes, which after all are not so new anymore. What is lacking
someone with time and interest.

--jkl

[Chris Kings-Lynne]

I'm sure I'm connecting with 8.0 - would this work if I were?

Chris

-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of James K. Lowden
Sent: Monday, 23 September 2013 12:11 PM
To: freetds at lists.ibiblio.org
Subject: Re: [freetds] dbcoltype char vs. datetimeoffset

On Mon, 23 Sep 2013 02:58:44 +0000

Post by Chris Kings-Lynne
The dbcoltype() function returns code 47 for both varchar and
datetimeoffset fields...is there any way to distinguish these - as
they really are _rather_ different...

Less different than you might think, I'm afraid.

If you connect to a server that supports datetimeoffset with TDS 7.2, you're using a protocol for which no datetimeoffset datatype is defined. On your behalf, the server provides the data as a string, which you see as VARCHAR. That's what's known as backwards compatibility.

There are good reasons to update db-lib to support the new datatypes, which after all are not so new anymore. What is lacking someone with time and interest.

--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
--
Message protected by MailGuard: e-mail anti-virus, anti-spam and content filtering.http://www.mailguard.com.au/mg
Click here to report this message as spam:
https://login.mailguard.com.au/report/1I1Z6dKNJV/4izNNeqIVYlx9v2DGlT29B/0

[Chris Kings-Lynne]

Ah. I see that version 8.0 and 7.2 are mapped to 7.2 in PHP dblib. I imagine that's due to the MS vs. Sybase version naming confusion?

So basically I have to fix FreeTDS to recognise the new type...any hints on where to start?

Chris

-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of Chris Kings-Lynne
Sent: Monday, 23 September 2013 2:52 PM
To: FreeTDS Development Group
Subject: Re: [freetds] dbcoltype char vs. datetimeoffset

I'm sure I'm connecting with 8.0 - would this work if I were?

Chris

-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of James K. Lowden
Sent: Monday, 23 September 2013 12:11 PM
To: freetds at lists.ibiblio.org
Subject: Re: [freetds] dbcoltype char vs. datetimeoffset

On Mon, 23 Sep 2013 02:58:44 +0000

Post by Chris Kings-Lynne
The dbcoltype() function returns code 47 for both varchar and
datetimeoffset fields...is there any way to distinguish these - as
they really are _rather_ different...

Less different than you might think, I'm afraid.

If you connect to a server that supports datetimeoffset with TDS 7.2, you're using a protocol for which no datetimeoffset datatype is defined. On your behalf, the server provides the data as a string, which you see as VARCHAR. That's what's known as backwards compatibility.

There are good reasons to update db-lib to support the new datatypes, which after all are not so new anymore. What is lacking someone with time and interest.

--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
--
Message protected by MailGuard: e-mail anti-virus, anti-spam and content filtering.http://www.mailguard.com.au/mg
Click here to report this message as spam:
https://login.mailguard.com.au/report/1I1Z6dKNJV/4izNNeqIVYlx9v2DGlT29B/0

[Frediano Ziglio]

Post by Chris Kings-Lynne
Ah. I see that version 8.0 and 7.2 are mapped to 7.2 in PHP dblib. I imagine that's due to the MS vs. Sybase version naming confusion?

No, it's MS against MS version confusion, they changed numbering. To
not having server use varchar you have to use version 7.3 (supported
only by git versions).

Post by Chris Kings-Lynne
So basically I have to fix FreeTDS to recognise the new type...any hints on where to start?

Well... dblib itself does not support these types (and probably never
will) so we have to extend it on FreeTDS.

Post by Chris Kings-Lynne
Chris

Frediano

Post by Chris Kings-Lynne
-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of Chris Kings-Lynne
Sent: Monday, 23 September 2013 2:52 PM
To: FreeTDS Development Group
Subject: Re: [freetds] dbcoltype char vs. datetimeoffset
I'm sure I'm connecting with 8.0 - would this work if I were?
Chris
-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of James K. Lowden
Sent: Monday, 23 September 2013 12:11 PM
To: freetds at lists.ibiblio.org
Subject: Re: [freetds] dbcoltype char vs. datetimeoffset
On Mon, 23 Sep 2013 02:58:44 +0000

Post by Chris Kings-Lynne
The dbcoltype() function returns code 47 for both varchar and
datetimeoffset fields...is there any way to distinguish these - as
they really are _rather_ different...

Less different than you might think, I'm afraid.
If you connect to a server that supports datetimeoffset with TDS 7.2, you're using a protocol for which no datetimeoffset datatype is defined. On your behalf, the server provides the data as a string, which you see as VARCHAR. That's what's known as backwards compatibility.
There are good reasons to update db-lib to support the new datatypes, which after all are not so new anymore. What is lacking someone with time and interest.
--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
--
Message protected by MailGuard: e-mail anti-virus, anti-spam and content filtering.http://www.mailguard.com.au/mg
https://login.mailguard.com.au/report/1I1Z6dKNJV/4izNNeqIVYlx9v2DGlT29B/0
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

[James K. Lowden]

On Mon, 23 Sep 2013 07:20:29 +0000

Post by Chris Kings-Lynne
Ah. I see that version 8.0 and 7.2 are mapped to 7.2 in PHP dblib. I
imagine that's due to the MS vs. Sybase version naming confusion?

Upon a time, Microsoft did not document the TDS protocol and you could
scarcely find mention of it on their website. I think FreeTDS called
it TDS 7.0 because at the time SQL Server was at version 7.0. When SQL
Server 2000 came out, we could hardly call it TDS 2000, so it became
TDS 8.0, why not?

After the EU insisted on documentation for proprietary protocols,
Microsoft retrodocumented TDS, providing the 7.0, 7.1, etc. names.
FreeTDS latterly adopted them and confused pretty much everyone who
wasn't reading the fine print. ;-)

Post by Chris Kings-Lynne
So basically I have to fix FreeTDS to recognise the new type...any hints on where to start?

There isn't that much to do, and if you're willing to undertake it I'm
happy to answer any questions on-list or off. You'll find some
of my earlier commentary and ideas in the email archive from the past
year.

libtds already supports TDS 7.3, which is when the new date types were
defined. Things to keep distinct in your mind as you're looking through
the code:

1. Datatypes defined on the server have designated tokens -- a small
integer -- in the protocol. That's what it means to say a varchar is
type 47. Symbolic constants for these in db-lib have the form SYBxxx.
They are sent by the server as metadata for arriving columns or
output parameters, and are used by db-lib (rather casually) to describe
program variables in e.g. dbrpcparam and bcp_bind.

2. C datatypes have symbolic constants of the form DBxxx. For
example, DBINT is defined as a C int on most machines.

3. db-lib has another symbolic constant to describe the format to
be used for the bound buffer. These all have names that end in "BIND",
as in NTBSTRINGBIND. I doubt you'll have to extend that.

4. Conversions are all done in src/tds/convert.c. It's a little
confusing at first, but it's reasonable and regular, like the people
who wrote it.

5. ODBC already has most of the bits you need because it already
supports these types (in the git repository master version). Some may
have to be moved down to libtds to be shared between client libraries.
Others will have to be copied into db-lib and renamed in the db-lib
style. Anything in the bcp library will be brand new because ODBC
doesn't support bcp.

There's a little two-liner in there somewhere to prevent db-lib from
connecting with TDS 7.3. You'll want to remove that.

For navigating the code, cscope is your friend.

I recommend extending a unit test during development, because a small,
simple program is quicker to re-run and debug.

You don't have to do everything; it's fine to do the parts that meet
your immediate needs. A complete job, for the record, would be

1. string binding for dbbind, dbrpcbind, and bcp_bind
2. tokens for dbcoltype and dbcolinfo
3. typename strings for dbprtype
4. binary structs for dbbind, dbdata, dbrpcbind, and bcp_bind
5. an extension for dbdatecrack
6. documentation
7. accolades

HTH.

--jkl