Character issue when pulling data out of MSSQL using DBI

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

Replies are listed 'Best First'.
Re: Character issue when pulling data out of MSSQL using DBI by ww (Archbishop) on May 01, 2010 at 03:04 UTC
Yes, there probably is a simple explanation. But you haven't given us much to go on. "Short and simple" are great virtues, but not when they lead you to omit necessary information such as... What database? Where is it -- local or on another machine? How did you get "the field out of the database?" Do you have any idea how the string was encoded when it was entered into the DB? What is the encoding used on your machine? Off the top of my head, and relying only on the extensive information (NOT!) that you provided, an encoding mismatch seems to be one possibility (among many). If you'll read How do I post a question effectively?, On asking for help and I know what I mean. Why don't you?, you'll find some tips on how to pose a question here in a form the Monks can use to help you.	[reply]
Re^2: Character issue when pulling data out of MSSQL using DBI by bwickland (Initiate) on May 01, 2010 at 04:29 UTC
Sorry for the brief description, i just was checking if it wasn't something obvious... I am using ActivePerl 5.8.8 the database is Microsoft SQL Server 2008 running on the same machine. The encoding of the particular field in question is "SQL_Latin1_General_CP1_CI_AS". As far as I am connecting to the database, I am using DBI v1.609 and DBD-ODBC v1.22.	[reply]
Re: Character issue when pulling data out of MSSQL using DBI by ikegami (Patriarch) on May 01, 2010 at 02:59 UTC
For what it's worth, it doesn't appear to be a character encoding issue. It's really weird that "C" remained unchanged while "F" became "`". In fact, I suspect that "7SR2CPH" and "&%$?F" and "*I0" were concatenated, and that the problem occurred before the concatenation. And that's assuming you're looking at the right field and record.	[reply]
Re: Character issue when pulling data out of MSSQL using DBI by graff (Chancellor) on May 01, 2010 at 17:31 UTC
I'm with apl: relying on font glyphs to see and show the data reminds me of the old story about blind men describing an elephant. At the very least, use some non-perl method to store the database field value in question into a plain-text file (without interpretation of any kind), and get a hex dump of the file. Then, when you get the database field via a DBI query in your perl script, print it like this: `print join(" ", map { sprintf("%04x", ord()) } split //, $field_val +ue ), "\n";` [download] There are other ways to dump a string as hex bytes; there's no reason to be picky about which method you use, so long as the output is complete and accurate. If the two sets of hex numbers differ, you've got a strange problem (maybe the storage into a plain-text involved some interpretation that you were not aware of). If they are the same, your problem is to figure out why they are showing up differently in one display method vs. another.	[reply] [d/l]
Re: Character issue when pulling data out of MSSQL using DBI by apl (Monsignor) on May 01, 2010 at 12:58 UTC
Is there a way of having Sql Server display the field in hex bytes? Similarly, could you print out the returned string as a series of hex bytes? It's possible that both strings are identical, but that the Sql Server display mechanism (and your print) are interpreting unprintable characters in different ways.	[reply]
Re: Character issue when pulling data out of MSSQL using DBI by bwickland (Initiate) on May 02, 2010 at 07:21 UTC
Thanks for all the help! It turns out I am an idiot, I was pulling data from two tables both of which had a RecordID column, so when I was binding the columns to my hash the RecordID from table2 was overwriting the one from table1...	[reply]