FreeTDS Development Group

[Jonathan Monroe <monroej_freetds AT actualtechnologies.com>]

> > On  Sun, 22 Feb 2004 at 10:03:02 +0100, Frediano Ziglio wrote:
> >
> > >>>
> > >>> On Feb 18, 2004, at 2:51 PM, Jonathan Monroe wrote:
> > >>>
> > >>>> I think I have found an issue with the way the SQL_C_BINARY
> > >>> data type
> > >>>> is handled in the ODBC code.
> > >>> [snip]
> > >>>
> > >>> It just occurred to me that the patch I provided for
> > >>> convert_tds2sql.c
> > >>> only fixed the problem for varchar columns - other column
> > types will
> > >>> still have some sort of conversion performed.  The better way of
> > >>> handling it is probably to treat SQL_C_BINARY as a special
> > >>> case and not
> > >>> call tds_convert() at all.
> > >>>
> > >>> So, the new patch would look like this:
> > >>>

[snip]

> > >>>
> > >>> Anybody see any problems with doing it this way?
> > >>>
> > >>> Jonathan Monroe
> > >>>
> > >>
> > >> You spot a real problem. I don't know if is correct to
> > handle varchar
> > >> as
> > >> a binary string (ie: '012345') however I don't know the
> > real behaviour
> > >> for other types. For instance how MS ODBC return a datetime?? Same
> > >> format of wire (as FreeTDS does now) or converting to
> > TIMESTAMP before
> > >> ?? Some tests are needed to handle this type...
> > >>
> > >
> > > SQL_C_BINARY behavior is correct and reflect MS ODBC one. I
> > don't know
> > > why but all library (dblib, CTLib and ODBC) have the same behavior.
> > > I'm doing some tests for DATETIME and others type.
> >
> > When you say SQL_C_BINARY behavior is correct, I assume you
> > mean after
> > incorporating the patch.
> >
>
> No, without patch. I checked varchar and behavior (threat as binary
> string) is correct. Well, MS ODBC is not 100% reference, however we try
> to reflect it's behavior (when not broken). In this case behavior is 
> the
> same of dblib so I think it's for storic reasons...

If I have a varchar(1) field conataining a "1" (0x31), a SQLBindCol of 
type SQL_C_BINARY using unpatched FreeTDS will return a value of 0x01 
instead of 0x31.  MS ODBC will return 0x31 and the patch will return 
0x31. The unpatched mapping of SQL_C_BINARY to SYBBINARY causes the 
ASCII "1" to be converted to a decimal 1 in tds_convert_char() in 
convert.c.

> > I did some testing of the non-blob data types in conjunction with
> > SQL_C_BINARY, using the patch above.  It looks like it really only
> > solves the problem for the following SQL types:  char, varchar, text,
> > bit, timestamp, and tinyint.  All the other numeric and multibyte
> > character types have byte ordering and other issues when compared to
> > the MS ODBC driver.  Some of this may be particular to big endian
> > machines such as mine - I haven't dug into the next layer of code to
> > see how endian-ness is handled.
> >
> > If we assume MS ODBC is the reference, here are the returned byte
> > orders for each type for my test case:
> >
> > bigint:  actually returns 35 bytes! (MS ODBC correctly
> > returns 8 bytes)
>
> Ok. I'll write a test and fix it.
>
> > datetime:   3 2 1 0 7 6 5 4
> > decimal:  correct length is 9, MS ODBC returns 19 bytes, FreeTDS
> > returns 35 bytes
>
> Well, FreeTDS support also ASA that require more precision. However I
> think we should translate to ODBC numeric. It's correct what MS ODBC do
> (extending wire).
>
> > float:  7 6 5 4 3 2 1 0
> > int:  3 2 1 0
> > money:  3 2 1 0 7 6 5 4
> > nchar, ntext, nvarchar:  only the first byte of each character is
> > returned (I assume this is an artifact of Latin-1 iconv
> > conversion, but
> > MS ODBC gives you access to the raw double byte characters)
>
> Mm.... here the behavior is different. We convert characters as we get
> from wire so the behavior it's exactly the same of varchar/char/text.

IMHO the purpose of doing a SQL_C_BINARY bind is to get access to the 
raw bytes of the field - otherwise you would just bind using 
SQL_C_CHAR.  I realize that much thought has preceded this conversation 
and MS compatibility isn't always a desired goal.  But to me, 
SQL_C_BINARY implies raw bits, while SQL_C_CHAR implies (possibly 
transformed) text. In this case, it seems that a SQL_C_BINARY bind 
should return both bytes of each character in an nvarchar field.

> > numeric:  same as decimal
> > real:  3 2 1 0
> > smalldatetime:  1 0 3 2
> > smallint:  1 0
> > smallmoney:  3 2 1 0
> >
>
> Keep in mind that endianism change the order...

Yeah.  Again, it seems like I should be able to get the raw bytes as 
they are stored in the database, but I'll concede that the correct 
behavior is probably undefined.

> > I still have to write a test case for binary, image,
> > uniqueidentifier,
> > and varbinary.
> >
> > Jonathan Monroe
> >
>
> I'll extend genparams.c test to catch these conversions (and perhaps
> change name to data.c)

I'm more than happy to help with the work here. I've spent considerable 
time crawling around in the code and I think I understand how you and 
Bill do things. Doing the test cases might be a good (harmless) way for 
me to contribute some code, don't you think?

Jonathan Monroe