FreeTDS Development Group

[Frediano Ziglio <freddy77 AT gmail.com>]

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

> On Thu, 2 May 2013 06:34:40 +0100
> Frediano Ziglio <freddy77 AT gmail.com> wrote:
> > 2013/5/1 James K. Lowden <jklowden AT freetds.org>
> > > 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.
>
>
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!


> 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!


> 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.


> We would like to
>
> 1.  add functions to libtds to convert these types to/from varchar
>

tds_convert already does it


> 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.


> 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?
>
>
Sure that we want to define them in libTDS ?


> 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.


> Regards,
>
> --jkl
>

bye
  Frediano