When I define a proxy table from SQL Anywhere (12) through ODBC to an MS SQL/Server table that contains a uniqueidentifier, the value of the UUID is returned to ISQL with the bytes flipped around. Specifically, the Endianness difference from SQL/Server to SQL Anywhere is ignored.
Various articles around the net describe the different UUID representations, but getting different results from SQL/Server's "SELECT someuuidcolumn FROM sometable" and the same query through an SQL Anywhere proxy table query seems pretty much wrong - e.g., '00013c07-e7f5-4033-bdbf-b34d6391f445' vs '073c0100-f5e7-3340-bdbf-b34d6391f445'. In particular, having a '4' at the start of the third group of digits identifies a UUID as a "V4" GUID and really shouldn't be arbitrarily flipped around, regardless of how the UUID is stored in the database. Can someone say if it's an ODBC issue, but even then, shouldn't SQL Anywhere deal with it? (I believe the problem occurs with SQL Anywhere 16 too, but I haven't tried it myself.)
asked 16 Jul '14, 23:34
From my research it would appear you can see this if your MS GUID is being bound as a binary value.
Whereas Microsoft stores their GUIDs as little-endian binary values, the standard for UUIDs is Most Signifiant Byte first (aka big-endian) or as describe in the RFCs for this "network byte order". See a reference to RFC 4122 for the sections on "4.1.2. Layout and Byte Order", and the many references to the terms "network byte order" and "endian".
SQL Anywhere implements the UUID statndard internally. This should not be a problem and should map correctly when interchanging data with Microsoft databases if the data types being used are proper UUID and not raw binary values.
So this is most likely failing if the proxy table is defined with a raw binary(n) instead of a UUID. You should be able to verify if this is your issue by issuing, in DBISQL, the statement
DESCRIBE TABLE <your proxy="" table="">
and noting if the column in question is of type binary(16, say) or type UUID.
The solution should be as simple as explictly defining the proxy as having the correct type. Let us know if this helps.
answered 18 Jul '14, 09:33
Nick Elson S...
Thank you for reporting this issue. I agree that there is something more that SQL Anywhere can do to properly handle GUIDs from SQL Server. We will investigate further and provide a fix.
answered 18 Jul '14, 14:34