ODBC and MSSQL on Linux/Mac

01_setup_EL.md

Enterprise Linux installation of ODBC is easy.

yum install unixODBC unixODBC-devel freetds freetds-devel perl-DBD-ODBC perl-local-lib

Configuration isn't much harder. You'll need to edit UnixODBC's driver list to add FreeTDS as an available driver.

• vim /etc/odbcinst.ini

# Driver from the FreeTDS package
# Setup from the unixODBC package
[FreeTDS]
Description = ODBC for FreeTDS
Driver      = /usr/lib64/libtdsodbc.so
Setup       = /usr/lib64/libtdsS.so.2
Driver64    = /usr/lib64/libtdsodbc.so
Setup64     = /usr/lib64/libtdsS.so.2
FileUsage   = 1

01_setup_Mac.md

Mac installation of ODBC is easy as well.

Homebrew

If you're using Homebrew, you do the following:

Install necessary bits

brew install unixodbc
brew install freetds --with-unixodbc

Configure UnixODBC's driver list and add FreeTDS

vim /usr/local/etc/odbcinst.ini

# Driver from the FreeTDS package
# Setup from the unixODBC package
[FreeTDS]
Description = ODBC for FreeTDS
Driver      = /usr/local/lib/libtdsodbc.so
Setup       = /usr/local/lib/libtdsodbc.so
FileUsage   = 1

MacPorts

If you're using MacPorts, you do the following:

Install necessary bits

sudo port install freetds +odbc

Configure UnixODBC's driver list and add FreeTDS

cp /opt/local/etc/odbc.ini.dist /opt/local/etc/odbc.ini
vim /opt/local/etc/odbcinst.ini

# Driver from the FreeTDS package
# Setup from the unixODBC package
[FreeTDS]
Description = ODBC for FreeTDS
Driver      = /opt/local/lib/libtdsodbc.so
Setup       = /opt/local/lib/libtdsodbc.so
FileUsage   = 1

01_setup_ubuntu.md

Ubuntu installation of ODBC is easy.

apt-get install unixodbc unixodbc-dev unixodbc-bin tdsodbc freetds-bin freetds-common freetds-dev libdbd-odbc-perl liblocal-lib-perl

Configuration isn't much harder. You'll need to edit UnixODBC's driver list to add FreeTDS as an available driver.

• vim /etc/odbcinst.ini

# Driver from the FreeTDS package
# Setup from the unixODBC package
[FreeTDS]
Description = ODBC for FreeTDS
Driver      = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup       = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage   = 1

02_perl_lib_setup.md

You're on Enterprise Linux, where your Perl is ancient and updating libraries can break your entire setup (not to mention that they may be clobbered when you yum update next time).

Now that you have local::lib installed, you need to tell it where you want to install Perl libraries. We'll go ahead and suggest your own home directory for now.

eval $(perl -I$HOME/perl5/lib/perl5 -Mlocal::lib)
echo '[ $SHLVL -eq 1 ] && eval "$(perl -I$HOME/perl5/lib/perl5 -Mlocal::lib)"' >>~/.bashrc

Now that your Perl is setup to find and install libraries in your own home directory first, you can safely install modules via cpan or cpanm.

• export DBD_ODBC_UNICODE=1 # enable unicode support
  • $env:DBD_ODBC_UNICODE=1 # enable unicode support on windows (PowerShell)
• cpan App::cpanminus
• cpanm App::cpanoutdated DBIx::Connector Data::Recursive::Encode Try::Tiny DBD::ODBC

Optionally, you can update all of your modules now (you'll likely need to yum install <library>-devel lots of things to get through installations) via:

cpan-outdated -p | cpanm

03_example_perl.pl

#!/usr/bin/env perl

use strict;
use warnings;
use utf8;
use v5.10;

use Carp ();
use Data::Dumper qw(Dumper);
use DBIx::Connector ();
use IO::Handle ();
use Try::Tiny qw(try catch);

my $win_dsn = 'dbi:ODBC:DRIVER={SQL Server};SERVER=DB.EXAMPLE.COM;database=NorthWind;port=1433;';
my $mac_dsn = 'dbi:ODBC:DRIVER={FreeTDS};SERVER=DB.EXAMPLE.COM;database=NorthWind;port=1433;tds_version=8.0;';
my $linux_dsn = $mac_dsn;

# DBIx::Connector keeps our connection alive on long-running apps
my $conn = DBIx::Connector->new($linux_dsn, 'username', 'password', {
  PrintError => 0, # don't automatically print out error messages
  RaiseError => 1, # die on error
  AutoCommit => 1, # changes are automatically committed to the database when executed
  LongReadLen => 24*1024, # SQL Server limit
  LongTruncOk => 1, # don't die when we have to grab really long data :/
  odbc_utf8_on => 1, # auto-utf8 all data so we can get it from FreeTDS/Windows ODBC consistently
  odbc_cursortype => 2, # allows multiple concurrent statements on SQL Server
});

die "Warning DBD::ODBC not built for unicode - this will not work as expected" unless $conn->dbh->{'odbc_has_unicode'};

# run a query
my $res;
$conn->run(fixup => sub {
  my $dbh = shift;
  try {
    # MS-SQL stores NVARCHAR in UTF-16. $res will be Perl internal 'utf8'
    $res = $dbh->selectall_arrayref('select * from some_table', {Slice => {}});
  } catch {
    # log the error here if you want.
    warn $_;
  };
});

say Dumper($res);