GUIDs returned as gibberish (ODBC/MS SQL)

LaidBackWebSage has asked for the wisdom of the Perl Monks concerning the following question:

All, I am using Perl 5.8.8 on Ubuntu Server 8.04, with DBD::ODBC, and FreeTDS. The database is on a WinNT server, running MS SQL Server 2000. Here is my pseudo-query:

SELECT row_guid FROM table
[download]

What should come back is this:
DAD26152-1C43-11DD-BD0B-0800200C9A66

What does come back is this:
RaÒÚCİ½

Is perl somehow reading this as hex or something?

Do I need to (un)pack somehow?

Any help will be greatly appreciated.

Thanks!

Kevin J

Comment on GUIDs returned as gibberish (ODBC/MS SQL) Select or Download Code

Replies are listed 'Best First'.
Re: GUIDs returned as gibberish (ODBC/MS SQL) by graff (Chancellor) on Oct 27, 2008 at 01:36 UTC
Here is my pseudo-query: SELECT row_guid FROM table What should come back is this: DAD26152-1C43-11DD-BD0B-0800200C9A66 Have you sent the actual SQL query (as implemented in your script) using some other DB client, and made sure that it returns the string that you say you should be getting? Have you looked at the table definition to determine what sort of data field you are querying for? (Does the DB server happen to provide some "special" data type, is the field encrypted, might there be some DB-side function call that should be used in the query string to fetch the value correctly, etc?) I don't think we can say anything useful based on the information originally posted (unless someone else has actually seen something very similar...)	[reply]
Re: GUIDs returned as gibberish (ODBC/MS SQL) by CountZero (Bishop) on Oct 27, 2008 at 06:02 UTC
It looks like an encoding issue. Something in the chain (database data, database server, client, DBD::ODBC, your program) thinks the data is in some multi-byte format 'Unidcode, UTF-16, ...) and rendering it as such. What happens if you read another field? CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply]
Re: GUIDs returned as gibberish (ODBC/MS SQL) by pKai (Priest) on Oct 27, 2008 at 11:15 UTC
A GUID is at core a 128 bit number. The Wikipedia link above has all the information you need to convert between the binary and the text representation. A cpan search gives Data:GUID. But on first glance there is no method for a direct binary -> text conversion.	[reply]
Re^2: GUIDs returned as gibberish (ODBC/MS SQL) by massa (Hermit) on Oct 27, 2008 at 12:55 UTC
`print Data::GUID->best_guess($your_guid)->as_string` [download] should work... []s, HTH, Massa (κς,πμ,πλ)	[reply] [d/l]
Re^3: GUIDs returned as gibberish (ODBC/MS SQL) by LaidBackWebSage (Acolyte) on Oct 27, 2008 at 14:09 UTC
The "Data::GUID" module is what I needed. Many thanks! Kevin J	[reply]
Re: GUIDs returned as gibberish (ODBC/MS SQL) by Krambambuli (Curate) on Oct 27, 2008 at 09:31 UTC
Are you sure the data put into the DB is OK...? The first 8 characters in your expected result string are DA D2 61 52 which - read in reverse order - looks pretty much as some hex encoded values (0x52 = 'R', 0x61 = 'a', ... ) that would fit what you see, but then your expected '-' doesn't fit into the picture, so I'm unsure about what's what where. Krambambuli ---	[reply]
Re: GUIDs returned as gibberish (ODBC/MS SQL) by mje (Curate) on Oct 27, 2008 at 09:19 UTC
If you don't get anywhere resolving this and can put together a small example of your schema, an example row and an example of your perl I'll take a look at it.	[reply]