How can I handle non-ASCII characters when retrieving data from SQL Server using Perl?

Question

I have a Perl script running on UNIX that uses DBI to connect to and retrieve data from a SQL Server database. The script looks like the following:

$dbh = DBI->connect("dbi:Sybase:server=$connect;charset=UTF-8", $login, $password) or die("Couldn't connect to $connect as $login/$password:
$DBI::errstr");


$sql = "use mydb";
$sth = $dbh->prepare($sql);
$sth->execute or die("execute failed");
$sth->finish;


$sql = "MyProc \@DATE='1/1/2008'";
$sth = $dbh->prepare($sql);
$sth->execute or die("execute failed");
while (($body) = $sth->fetchrow()) {
        print "$body\n";
}
$sth->finish;

$dbh->disconnect if $dbh;

The body variable retrieves data from a column that is NVARCHAR and contains non-ASCII characters. The query runs fine, but the print statement spits out ????? when it encounters a non-ASCII character. In DBI->connect I even specify the character set, but no luck.

Any thoughts on how I can get this to work?

Answer 1

I've connected Perl to SQL Server via FreeTDS + ODBC and have had no problem with character encodings. Maybe the Sybase DBI is the culprit here...

Answer 2

Your code looks OK.

I've no reason to believe that what you're putting into the database and subsequently retrieving isn't still in UTF-8 encoding.

Have you confirmed that the terminal on which your printing the data is actually in UTF-8 mode?

Answer 3

Oh how many hours I've wasted chasing non-existent bugs based on what I saw or didn't see when I printed data to my terminal. There are several different ways to verify your data that aren't affected by non-printing characters and don't depend on your system's display being able to map the correct glyphs to non-ASCII character codes. If your data don't look right dump them into a file and browse the file with a hex editor or run them through the od utility.