How can I handle non-ASCII characters when fetching data from SQL Server using Perl?
I have a Perl script running on UNIX that uses DBI to connect and fetch data from a SQL Server database. The script looks like this:
$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 request runs fine, but the print statement spits out ????? when it encounters a non-ASCII character. In DBI-> connect I even set the character set, but no luck.
Any thoughts on how I can get this to work?
source to share
Oh, how many hours have I wasted on zero non-existent errors based on what I saw or did not see when I was typing data on my terminal. There are several ways to validate your data that are not affected by non-printable characters, and are independent of how your system display can match valid characters to non-ASCII character characters. If your data doesn't look right, dump it to a file and view the file with a hex editor or run it through the od utility.
source to share
I connected Perl to SQL Server via FreeTDS + ODBC and had no character encoding issues. Maybe Sybase DBI is the culprit here ...
source to share