Re: Status of support for Time type

On Thu, 2 May 2013 06:34:40 +0100
Frediano Ziglio <[email protected]> wrote:
> 2013/5/1 James K. Lowden <[email protected]>
> > 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?  

> > struct tagDATE_STRUCT {
> >    SQLSMALLINT year;
> >    SQLUSMALLINT month;
> >    SQLUSMALLINT day;
> > } DATE_STRUCT;
> >
> > struct tagTIME_STRUCT {
> >    SQLUSMALLINT hour;
> >    SQLUSMALLINT minute;
> >    SQLUSMALLINT second;
> > } TIME_STRUCT
...
> > 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