[freetds] Status of support for Time type

Discussion:

Randy Syring

2013-04-29 18:15:00 UTC

What's the status on FreeTDS being able to support the time data type
(and other new date/time types in 2008)?

Thanks.

*Randy Syring*
Development | Executive Director
Direct: 502.276.0459
Office: 812.285.8766
Level 12 Technologies <https://www.lev12.com/>
/Principled People | Technology that Works/

Frediano Ziglio

2013-04-30 18:18:50 UTC

Permalink

Depends on what do you want. Actually can read and handle protocol 7.3.
ODBC should also quite fine but I'm not sure about all binding types are
working correctly.

Frediano

2013/4/29 Randy Syring <rsyring at gmail.com>

Post by Randy Syring
What's the status on FreeTDS being able to support the time data type
(and other new date/time types in 2008)?
Thanks.
*Randy Syring*
Development | Executive Director
Direct: 502.276.0459
Office: 812.285.8766
Level 12 Technologies <https://www.lev12.com/>
/Principled People | Technology that Works/
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

Randy Syring

2013-04-30 18:46:04 UTC

Permalink

Frediano,

I'm trying to handle this issue:

http://code.google.com/p/pymssql/issues/detail?id=103

The user is asking to be able to pass in a Python time type. I'd only
want to allow this if the underlying server/protocal handles the time
column. That is, I don't want to be accepting a time data type and then
converting to store in a datetime field. I realize it would work, but I
want the developer using the library to make that decision.

So, it sounds like read support is there. But write support is
questionable?

*Randy Syring*
Development | Executive Director
Direct: 502.276.0459
Office: 812.285.8766
Level 12 Technologies <https://www.lev12.com/>
/Principled People | Technology that Works/

Post by Frediano Ziglio
Depends on what do you want. Actually can read and handle protocol 7.3.
ODBC should also quite fine but I'm not sure about all binding types are
working correctly.
Frediano
2013/4/29 Randy Syring <rsyring at gmail.com>

_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

Frediano Ziglio

2013-04-30 19:05:00 UTC

Permalink

Problem here is also that you are using dblib which is not supposed to be
extended!

As Microsoft decided to not update it we should find a way to compatibly
extend dblib in order to put/get time/date!

Frediano

2013/4/30 Randy Syring <rsyring at gmail.com>

Post by Randy Syring
Frediano,
http://code.google.com/p/pymssql/issues/detail?id=103
The user is asking to be able to pass in a Python time type. I'd only
want to allow this if the underlying server/protocal handles the time
column. That is, I don't want to be accepting a time data type and then
converting to store in a datetime field. I realize it would work, but I
want the developer using the library to make that decision.
So, it sounds like read support is there. But write support is
questionable?
*Randy Syring*
Development | Executive Director
Direct: 502.276.0459
Office: 812.285.8766
Level 12 Technologies <https://www.lev12.com/>
/Principled People | Technology that Works/

_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

James K. Lowden

2013-05-01 03:39:01 UTC

Permalink

On Tue, 30 Apr 2013 20:05:00 +0100

Post by Frediano Ziglio
Problem here is also that you are using dblib which is not supposed
to be extended!
As Microsoft decided to not update it we should find a way to
compatibly extend dblib in order to put/get time/date!

It is easily done, if one has the time. What needs extending other
than dbbind() and bcp_bind()?

ISTM dbbind() should support binding a server's DATE or TIME to
DBDATETIME or DBDATETIM4, subject to domain constraints. Although
there are new types, most databases still keep data per the
Gregorian calendar, and most days are still 24 hours. Mapping the new
date & time column types to the existing db-lib structures would allow
existing programs to use the new column types merely by relinking to a
newer FreeTDS.

For completeness -- to support new applications using the extended date
range -- dbbind() would need new structs and new bindings.

For structures I would use those already defined for ODBC. Microsoft
defines these, but I don't see support for them in tds_convert()?

struct tagDATE_STRUCT {
SQLSMALLINT year;
SQLUSMALLINT month;
SQLUSMALLINT day;
} DATE_STRUCT;

struct tagTIME_STRUCT {
SQLUSMALLINT hour;
SQLUSMALLINT minute;
SQLUSMALLINT second;
} TIME_STRUCT

For binding, dbbind() defines SMALLDATETIBIND and DATETIMEBIND. I would
suggest adding DATEBIND and TIMEBIND.

Those are needed only for binary bindings, and only if we adopt new
structs.

Just a small matter of programming.

--jkl

Frediano Ziglio

2013-05-02 05:34:40 UTC

Permalink

2013/5/1 James K. Lowden <jklowden at freetds.org>

Post by James K. Lowden
On Tue, 30 Apr 2013 20:05:00 +0100

No, libTDS does not support ODBC types. Well... there should be perhaps a
way to support more types but actually tds_convert support only "libTDS"
types.

Post by James K. Lowden
struct tagDATE_STRUCT {
SQLSMALLINT year;
SQLUSMALLINT month;
SQLUSMALLINT day;
} DATE_STRUCT;
struct tagTIME_STRUCT {
SQLUSMALLINT hour;
SQLUSMALLINT minute;
SQLUSMALLINT second;
} TIME_STRUCT
For binding, dbbind() defines SMALLDATETIBIND and DATETIMEBIND. I would
suggest adding DATEBIND and TIMEBIND.

If program bind these type is not a big issue. However if program want to
read the type and FreeTDS start returning type that client does not support
client cannot handle them so there should be a way for the client to tell
"I support this type, feel free to return it".

Post by James K. Lowden
Those are needed only for binary bindings, and only if we adopt new
structs.
Just a small matter of programming.

Of course, but when ABI changes are required some design in advance is not
bad :)

Frediano

Post by James K. Lowden
--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

James K. Lowden

2013-05-03 00:48:14 UTC

Permalink

On Thu, 2 May 2013 06:34:40 +0100

Post by Frediano Ziglio
2013/5/1 James K. Lowden <jklowden at freetds.org>

Post by James K. Lowden
For structures I would use those already defined for ODBC.
Microsoft defines these, but I don't see support for them in
tds_convert()?

No, libTDS does not support ODBC types. Well... there should be
perhaps a way to support more types but actually tds_convert support
only "libTDS" types.

Hi Freddy,

OK, I see. Let's make sure we're talking about the same thing.

There really are only three kinds of types:

1. SQL Server types. These are logical. They are defined in terms
of what they can hold; they have no defined on-disk format.

2. TDS types. The wire format.

3. C types. Data types (native and struct) recognized by the C
language. There are many aliases for each C type: one per library.

SQL Server 2008 introduced new SQL Server types. TDS 7.3 clients
receive new TDS types on the wire and provide binary bindings to
existing ODBC 3.0 C types. (Apparently there never was a C type in
ODBC capable of representing a SQL Server [small]datetime? That would
make ODBC BCP ... interesting.)

For TDS 7.2 clients, the server converts any such columns to varchar.

db-lib clients currently cannot send those types via BCP because no
conversion is supported from C char[].

Today, if a db-lib client connects with TDS 7.3, the server may send
a wire type that the library is unable to bind to a C type for the
client. In theory, the client could just call dbdata() and handle the
format himself. But I wouldn't exactly call that a feature.

We would like to

1. add functions to libtds to convert these types to/from varchar
2. define db-lib structures for binary binding
3. define DATEBIND and TIMEBIND for dbbind() for binary binding
4. define similar for bcp_bind

There is obviously some other work parsing and constructing the TDS
packets.

Does that sound like a good problem description?

Post by Frediano Ziglio

...

Post by Frediano Ziglio

Post by James K. Lowden
Just a small matter of programming.

Of course, but when ABI changes are required some design in advance
is not bad :)

Yes, I appreciate that! :-)

Perhaps as a first step, we define these structs in tds.h and sybdb.h?

It looks like most of the bit-twiddling conversion work is already done.
Even convert_tds2sql.c uses tds_datecrack for these types.

I expect bcp to be more work because it can't leverage what you already
did for ODBC.

Regards,

--jkl

Frediano Ziglio

2013-05-04 13:25:55 UTC

Permalink

2013/5/3 James K. Lowden <jklowden at freetds.org>

Post by James K. Lowden
On Thu, 2 May 2013 06:34:40 +0100

Post by Frediano Ziglio
2013/5/1 James K. Lowden <jklowden at freetds.org>

Post by James K. Lowden
For structures I would use those already defined for ODBC.
Microsoft defines these, but I don't see support for them in
tds_convert()?

No, libTDS does not support ODBC types. Well... there should be
perhaps a way to support more types but actually tds_convert support
only "libTDS" types.

Hi Freddy,
OK, I see. Let's make sure we're talking about the same thing.
1. SQL Server types. These are logical. They are defined in terms
of what they can hold; they have no defined on-disk format.
2. TDS types. The wire format.
3. C types. Data types (native and struct) recognized by the C
language. There are many aliases for each C type: one per library.

Well, you have also consider that ODBC have his own SQL type which is db
agnostic. dblib type are more sql server "friendly", usually with dbdata
you get the binary representation from server (as 2 state) however there is
(in ms dblib) exception which is numeric/decimal (if you don't want to
consider blobs). The reason is that numeric/decimal have precision and
scale specifications not in data stream but in data format one. dblib (and
also libTDS) pack data fmt and data streams in a numeric structure that is
not the wire one. The other exception in libTDS is the new TDS_DATETIMEALL.
Why I introduced such as a new type which is not wire? The reason is that
dblib (at least official one) does not support this type and ODBC has it's
own (perhaps ctlib needs some extension too but let ignore it now) so I
didn't have the binary representation problem. Now there are a lot of new
additions to the original smalldatetime/datetime. Beside time, date,
datetime2 and datetimeoffset (ms ones) you have also data and time from
Sybase! To avoid to get mad supporting all these binary types (6 with all
possible conversions) I introduce a new libTDS type which can handle all
types!

Post by James K. Lowden
SQL Server 2008 introduced new SQL Server types. TDS 7.3 clients
receive new TDS types on the wire and provide binary bindings to
existing ODBC 3.0 C types. (Apparently there never was a C type in
ODBC capable of representing a SQL Server [small]datetime? That would
make ODBC BCP ... interesting.)

ODBC BCP is a strange thing, seems some dblib function put into ODBC just
to make BCP works. Functions use dblib constants and types instead of ODBC
ones!

Post by James K. Lowden
For TDS 7.2 clients, the server converts any such columns to varchar.
db-lib clients currently cannot send those types via BCP because no
conversion is supported from C char[].
Today, if a db-lib client connects with TDS 7.3, the server may send
a wire type that the library is unable to bind to a C type for the
client. In theory, the client could just call dbdata() and handle the
format himself. But I wouldn't exactly call that a feature.

I think that for some reason (if I remember change in blobs and variant
which does not convert to single byte automatically) our dblib does not
support tds 7.3 falling down using 7.2.

Post by James K. Lowden
We would like to
1. add functions to libtds to convert these types to/from varchar

tds_convert already does it

Post by James K. Lowden
2. define db-lib structures for binary binding
3. define DATEBIND and TIMEBIND for dbbind() for binary binding
4. define similar for bcp_bind
There is obviously some other work parsing and constructing the TDS
packets.

Already implemented in data.c, you can get and post data in such format
using TDS_DATETIMEALL and proper types.

Post by James K. Lowden
Does that sound like a good problem description?

Post by Frediano Ziglio

...

Post by Frediano Ziglio

Post by James K. Lowden
Just a small matter of programming.

Of course, but when ABI changes are required some design in advance
is not bad :)

Yes, I appreciate that! :-)
Perhaps as a first step, we define these structs in tds.h and sybdb.h?

Sure that we want to define them in libTDS ?

Post by James K. Lowden
It looks like most of the bit-twiddling conversion work is already done.
Even convert_tds2sql.c uses tds_datecrack for these types.
I expect bcp to be more work because it can't leverage what you already
did for ODBC.

ODBC BCP is implemented, actually is only in dblib. Probably require some
work.

Post by James K. Lowden
Regards,
--jkl

bye
Frediano

Eduard Heimann

2013-05-04 16:04:03 UTC

Permalink

Post by Frediano Ziglio
2013/5/3 James K. Lowden <jklowden at freetds.org>
ODBC BCP is implemented, actually is only in dblib. Probably require some
work.

Hi all!
I have tested BCP quite well against ASE 10 and a recent ASE 12.5 - the
results are quite good, but there is a severe bug on bpc-copying into a table
having many columns. The Server goes/fails into a broken state, since the
formula at "src/tds/bulk.c:780" is wrong.

Please help me to integrate my fix into the mainline of freetds. I have no
experience with this kind of distributed development.

James K. Lowden

2013-05-04 16:51:35 UTC

Permalink

On Sat, 04 May 2013 18:04:03 +0200

Post by Eduard Heimann
Please help me to integrate my fix into the mainline of freetds. I
have no experience with this kind of distributed development.

Thank you for reporting the problem and developing a fix. :-)

The most developer-friendly way to supply a patch is with "git diff".
If you're not using git, "diff -u oldfile newfile > file.diff"
works, too.

As a last resort, simply attached your modified file to a message you
post here.

It helps if the file is an *attachment* because email systems often
wrap long lines in the code, something that can be undone only by
hand.

Provided we agree with with your assessment and solution, we'll update
the master git branch. Usually someone gets around to it within a week
or so.

--jkl

Eduard Heimann

2013-05-05 08:08:05 UTC

Permalink

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sat, 04 May 2013 18:04:03 +0200 Eduard Heimann <eheimann at pe-fittinge.de>

Post by Eduard Heimann
Please help me to integrate my fix into the mainline of freetds. I have
no experience with this kind of distributed development.

Thank you for reporting the problem and developing a fix. :-)
The most developer-friendly way to supply a patch is with "git diff". If
you're not using git, "diff -u oldfile newfile > file.diff" works, too.
As a last resort, simply attached your modified file to a message you post
here.
It helps if the file is an *attachment* because email systems often wrap
long lines in the code, something that can be undone only by hand.
Provided we agree with with your assessment and solution, we'll update the
master git branch. Usually someone gets around to it within a week or so.
--jkl _______________________________________________ FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

Description of this fix for freetds:
BCP-copying wide rows (>255 bytes, from file into server-table) results in an
error in bcp_sendrow() and a bogus state in Sybase SQL Server. Tested against
Server 10.2/32bit and Server 12.4/64bit.
This patch is a result of reverse-engineering Sybase's bcp-library on
datastream level.

- --
Eduard Heimann, H?rmannsdorf 9, D-94104 Tittling
Tel: 08504 955249 Fax: 08504 955248
Mail mailto:eheimann at pe-fittinge.de Web: http://www.pe-fittinge.de
Eine Haust?re ohne Schlo? ist wie eMail ohne http://www.GNUPG.org
GPG key ID: 0xB542BD21 http://keyserver.ubuntu.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.19 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iEYEARECAAYFAlGGE2UACgkQUzMNXFvaJEbHPACeI+qOPGQxWNRnCY4/wyN4lOx4
KvIAn06JlDuVwLo+qVK3dpXXMO/s1mLq
=p3R8
-----END PGP SIGNATURE-----
-------------- next part --------------
A non-text attachment was scrubbed...
Name: freetds.diff
Type: text/x-patch
Size: 4251 bytes
Desc: not available
Url : http://lists.ibiblio.org/pipermail/freetds/attachments/20130505/bd2fc81f/attachment.bin

Frediano Ziglio

2013-05-06 09:17:21 UTC

Permalink

I'm trying to merge it. Some hunks scary me a bit (well, the patch was
reversed but it's just an extra option to patch).

--- freetds-0.91.dev.20120624-bcpfix/include/tds.h 2013-01-08
20:46:30.000000000 +0100
+++ freetds-0.91.dev.20120624/include/tds.h 2011-08-12
18:29:36.000000000 +0200
@@ -716,13 +716,13 @@
x==SYBVOID || \
x==SYBUNIQUE)
#define is_nullable_type(x) ( \
- x==SYBBITN || \
+ x==SYBBITN || \
x==SYBINTN || \
x==SYBFLTN || \
x==SYBMONEYN || \
x==SYBDATETIMN || \
x==SYBVARCHAR || \
- /*EVEVEV x==SYBBINARY ||*/ \
+ x==SYBBINARY || \
x==SYBVARBINARY || \
x==SYBTEXT || \
x==SYBNTEXT || \

this could change a lot of things. However I must say it surely must
something wrong with it (char and binary has same representation so both
should be here or not) and actually cause no regressions in our tests. Now
there is a test (t0016) which is quite flexible so I try passing either
nullable char or not nullable char. The result scary me. It seems that not
nullable are handled like fixed type while nullable are handled by variable
(like varchar). The bad things is that I cause my test server to get to
100% cpu consumption.

diff -ur freetds-0.91.dev.20120624-bcpfix/src/tds/bulk.c
freetds-0.91.dev.20120624/src/tds/bulk.c
--- freetds-0.91.dev.20120624-bcpfix/src/tds/bulk.c 2013-05-05
08:42:37.000000000 +0200
+++ freetds-0.91.dev.20120624/src/tds/bulk.c 2011-06-02
21:19:30.000000000 +0200
@@ -584,23 +584,19 @@
if ((get_col_data(bcpinfo, bindcol,
offset)) != TDS_SUCCEED) {
return TDS_FAIL;
}
- /* maybe the same code in bcp_moretext ? */
- tdsdump_log(TDS_DBG_INFO1, "is_blob_type
col=%u datalen=%u data=%x\n",
- i,
bindcol->bcp_column_data->datalen, bindcol->bcp_column_data->data);
- if (bindcol->bcp_column_data->datalen &&
bindcol->bcp_column_data->data) {
- /* unknown but zero */
- tds_put_smallint(tds, 0);
- tds_put_byte(tds, bindcol->column_type);
- tds_put_byte(tds, 0xff - blob_cols);
- /*
- * offset of txptr we stashed during
variable
- * column processing
- */
- tds_put_smallint(tds,
bindcol->column_textpos);
- tds_put_int(tds,
bindcol->bcp_column_data->datalen);
- tds_put_n(tds,
bindcol->bcp_column_data->data, bindcol->bcp_column_data->datalen);
- } /*else server table gets into very
strange&broken state */
+ /* unknown but zero */
+ tds_put_smallint(tds, 0);
+ tds_put_byte(tds, bindcol->column_type);
+ tds_put_byte(tds, 0xff - blob_cols);
+ /*
+ * offset of txptr we stashed during
variable
+ * column processing
+ */
+ tds_put_smallint(tds,
bindcol->column_textpos);
+ tds_put_int(tds,
bindcol->bcp_column_data->datalen);
+ tds_put_n(tds,
bindcol->bcp_column_data->data, bindcol->bcp_column_data->datalen);
blob_cols++;
+
}
}

This add an extra check. But the else case is not handled at all which
could possible cause table corruption, honestly I would prefer bcp to fail
instead of corrupting my db. Why is this test necessary. It shouldn't,
get_col_data should take care of filling the db.

The adjustment array computation is quite complicated. Well, if I remember
code was already changed and code was complicated before. Up to which size
did you tried ?? I think trying till some thousand could help shed some
light. It's worrying that every 256 bytes cause every byte. So a field at
offset 1024 should take 5 byte to state the length.

Frediano

2013/5/5 Eduard Heimann <eheimann at pe-fittinge.de>

Post by Eduard Heimann
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sat, 04 May 2013 18:04:03 +0200 Eduard Heimann <

eheimann at pe-fittinge.de>

Post by Eduard Heimann
Please help me to integrate my fix into the mainline of freetds. I have
no experience with this kind of distributed development.

post

here.
It helps if the file is an *attachment* because email systems often wrap
long lines in the code, something that can be undone only by hand.
Provided we agree with with your assessment and solution, we'll update

the

master git branch. Usually someone gets around to it within a week or

so.

--jkl _______________________________________________ FreeTDS mailing

list

FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

BCP-copying wide rows (>255 bytes, from file into server-table) results in an
error in bcp_sendrow() and a bogus state in Sybase SQL Server. Tested against
Server 10.2/32bit and Server 12.4/64bit.
This patch is a result of reverse-engineering Sybase's bcp-library on
datastream level.
- --
Eduard Heimann, H?rmannsdorf 9, D-94104 Tittling
Tel: 08504 955249 Fax: 08504 955248
Mail mailto:eheimann at pe-fittinge.de Web: http://www.pe-fittinge.de
Eine Haust?re ohne Schlo? ist wie eMail ohne http://www.GNUPG.org
GPG key ID: 0xB542BD21 http://keyserver.ubuntu.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.19 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
iEYEARECAAYFAlGGE2UACgkQUzMNXFvaJEbHPACeI+qOPGQxWNRnCY4/wyN4lOx4
KvIAn06JlDuVwLo+qVK3dpXXMO/s1mLq
=p3R8
-----END PGP SIGNATURE-----
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

Eduard Heimann

2013-05-06 12:05:56 UTC

Permalink

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Post by Frediano Ziglio
I'm trying to merge it. Some hunks scary me a bit (well, the patch was
reversed but it's just an extra option to patch).

Hi, the same patch, but this time not reversed:
I am very sorry.

Eduard

- --
Eduard Heimann, H?rmannsdorf 9, D-94104 Tittling
Tel: 08504 955249 Fax: 08504 955248
Mail mailto:eheimann at pe-fittinge.de Web: http://www.pe-fittinge.de
Eine Haust?re ohne Schlo? ist wie eMail ohne http://www.GNUPG.org
GPG key ID: 0xB542BD21 http://keyserver.ubuntu.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.19 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iEYEARECAAYFAlGHnKQACgkQUzMNXFvaJEbuUwCfSlwP81YMQ1wFvTAUb/9enM9w
mKUAoJtCPQXvdbuTAxwIhmxMc4K7QU4H
=ftys
-----END PGP SIGNATURE-----
-------------- next part --------------
A non-text attachment was scrubbed...
Name: freetds.diff
Type: text/x-patch
Size: 8172 bytes
Desc: not available
Url : http://lists.ibiblio.org/pipermail/freetds/attachments/20130506/745f787b/attachment.bin

Frediano Ziglio

2013-05-06 13:29:55 UTC

Permalink

Was not a problem. Already tested and committed at
http://gitorious.org/freetds/freetds/commit/54415747d8805477b4a452c23b9cc479e4ba0a14.
Added also test for char, binary and large variable data. Backported to
0.91 too, this is a bug and must be ported.

Frediano

2013/5/6 Eduard Heimann <eheimann at pe-fittinge.de>

Post by Eduard Heimann
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Post by Frediano Ziglio
I'm trying to merge it. Some hunks scary me a bit (well, the patch was
reversed but it's just an extra option to patch).

I am very sorry.
Eduard
- --
Eduard Heimann, H?rmannsdorf 9, D-94104 Tittling
Tel: 08504 955249 Fax: 08504 955248
Mail mailto:eheimann at pe-fittinge.de Web: http://www.pe-fittinge.de
Eine Haust?re ohne Schlo? ist wie eMail ohne http://www.GNUPG.org
GPG key ID: 0xB542BD21 http://keyserver.ubuntu.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.19 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
iEYEARECAAYFAlGHnKQACgkQUzMNXFvaJEbuUwCfSlwP81YMQ1wFvTAUb/9enM9w
mKUAoJtCPQXvdbuTAxwIhmxMc4K7QU4H
=ftys
-----END PGP SIGNATURE-----
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

Randy Syring

2013-08-16 14:47:08 UTC

Permalink

Hi Gents,

Can you give me a status update on support for the Time type? After the
exchange below, the email thread takes a pretty technical turn. From
that discussion, it sounds like the Time type is not yet supported in dblib?

Still trying to figure out what to do with this issue:

http://code.google.com/p/pymssql/issues/detail?id=103

Thanks.

*Randy Syring*
Development | Executive Director
Direct: 502.276.0459
Office: 812.285.8766
Level 12 Technologies <https://www.lev12.com/>
/Principled People | Technology that Works/

Post by James K. Lowden
On Tue, 30 Apr 2013 20:05:00 +0100

It is easily done, if one has the time. What needs extending other
than dbbind() and bcp_bind()?
ISTM dbbind() should support binding a server's DATE or TIME to
DBDATETIME or DBDATETIM4, subject to domain constraints. Although
there are new types, most databases still keep data per the
Gregorian calendar, and most days are still 24 hours. Mapping the new
date & time column types to the existing db-lib structures would allow
existing programs to use the new column types merely by relinking to a
newer FreeTDS.
For completeness -- to support new applications using the extended date
range -- dbbind() would need new structs and new bindings.
For structures I would use those already defined for ODBC. Microsoft
defines these, but I don't see support for them in tds_convert()?
struct tagDATE_STRUCT {
SQLSMALLINT year;
SQLUSMALLINT month;
SQLUSMALLINT day;
} DATE_STRUCT;
struct tagTIME_STRUCT {
SQLUSMALLINT hour;
SQLUSMALLINT minute;
SQLUSMALLINT second;
} TIME_STRUCT
For binding, dbbind() defines SMALLDATETIBIND and DATETIMEBIND. I would
suggest adding DATEBIND and TIMEBIND.
Those are needed only for binary bindings, and only if we adopt new
structs.
Just a small matter of programming.
--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds