Skip to Content.
Sympa Menu

freetds - [freetds] Re: SQL_C_BINARY type issue

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Jonathan Monroe <monroej_freetds AT actualtechnologies.com>
  • To: freetds AT lists.ibiblio.org
  • Subject: [freetds] Re: SQL_C_BINARY type issue
  • Date: Mon, 23 Feb 2004 10:47:07 -0600

> 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




Archive powered by MHonArc 2.6.24.

Top of Page