Perl Module DBD::ODBC Unicode Support for Cygwin and Linux

I wrote a Perl program a few years back that scraped data from email messages in order to create a set of RSS feeds. I needed to modify it so that it would get the data directly from a MS-SQL database instead. Windows users running Perl would typically be using ActivePerl, but the environment I’m working on already had Cygwin up and running and is using Perl for other daily processes. Unfortunately, the DBD::ODBC module needed to communicate with the database is broken for 64-bit Cygwin environments. Thus began my journey to get an incompatible Linux-like environment that runs on top of a Windows OS to communicate with a Microsoft database.

Installing modules in Perl is easy...or it ought to be. The Comprehensive Perl Archive Network (CPAN) is a large collection of Perl software and documentation. You can install the DBD::ODBC module from a standard Perl install using the commands:

perl -MCPAN -e shell
install DBD::ODBC

This will download, build and install the module as needed. The problem is that there’s a long-standing bug with the 64-bit version of Cygwin and this module that has still not been fixed. It was discovered and diagnosed by the Cygwin team in 2013. Another user in that thread came up with a temporary patch to work around the problem. However, this requires the user to manually build and install the module.

Another issue with DBD::ODBC is that it doesn’t get built with Unicode support by default on non-Windows environments. Remember, Cygwin for all intents and purposes is a non-Windows environment even though it runs in a Windows OS. Also, additional drivers are needed to get this module to work properly and those drivers must also support Unicode. Perhaps it was lucky for me that the CPAN install of DBD::ODBC failed since I would have spent more time troubleshooting why Unicode didn’t work as intended and that fix also requires a manual install of the module.

UPDATE 2019-01-15: Unicode support can be enabled without using the the "-u" switch for version 1.57 of DBD::ODBC and higher. An environment variable can be set instead, which makes it possible to enable Unicode while using CPAN:

export DBD_ODBC_UNICODE=1
perl -MCPAN -e shell
install DBD::ODBC

Unfortunately, the bug when compiling in 64-bit Cygwin environments still exists. The patch to the Makefile.PL must be done and DBD::ODBC has to be built and installed manually even with the latest release. No CPAN for you!

Instructions

1. Download FreeTDS, UnixODBC, and the DBD::ODBC module.
2. Extract and build/install the FreeTDS and UnixODBC programs as per the instructions included.
3. Once FreeTDS is installed, make sure it’s configured to work with UnixODBC by running the command: tsql -C
4. Find the location of your odbcinst.ini by running odbcinst -s -j
5. Edit your odbcinst.ini file to add the FreeTDS driver as below:
   
   # Driver from the FreeTDS package
# Setup from the unixODBC package
[FreeTDS]
Description = ODBC for FreeTDS
Driver      = /usr/lib/cygtdsodbc.dll
Setup       = /usr/lib/cygtdsodbc.dll
FileUsage   = 1
6. Extract the DBD::ODBC tarball. 64-bit Cygwin only: Patch the Makefile.PL file to ensure the “Trade cygwin as MSWin” code block never executes. (This forces the Makefile.PL to process this build as it were natively done on a Unix OS.)
7. Follow the instructions for building and installing DBD::ODBC as per the README file. However, use the “-u” switch to enable Unicode support: perl Makefile.PL -u

Test if everything works in Perl using this short program:

#!/usr/bin/perl
use DBI;

my ( $user, $password ) = ( 'username', 'password' );
my $dsn = {
    driver   => 'FreeTDS',
    server   => 'servername',
    database => 'dbname',
    port     => 1433,
    tds_ver  => '8.0'
};
$dsn =
  sprintf
  'dbi:ODBC:DRIVER={%s};SERVER=%s;database=%s;port=%s;tds_version=%s;',
  $dsn->{driver},
  $dsn->{server},
  $dsn->{database},
  $dsn->{port},
  $dsn->{tds_ver};

my $dbi_opts = {
    PrintError   => 0,
    RaiseError   => 1,
    AutoCommit   => 1,
    LongReadLen  => 24 * 1024,
    LongTruncOk  => 1,
    odbc_utf8_on => 1,
};

my $dbh =
  DBI->connect( $dsn, $user, $password), $dbi_opts );

# Verify unicode support
die "Unicode support not compiled or enabled for DBD::ODBC." unless ( $dbh->{odbc_has_unicode} ) ;

Just replace the italicized strings above with your user, password, and server settings.

References:

• Some Common Unicode Problems and Solutions using Perl DBD::ODBC and MS SQL Server http://www.easysoft.com/developer/languages/perl/sql-server-unicode.html
• Configuring FreeTDS and UnixODBC: http://www.perlmonks.org/?node_id=1167735
• Win32 vs. Win64 issues with Cygwin and DBD::ODBC: https://cygwin.com/ml/cygwin/2013-08/msg00011.html