Re: Using DBD::ODBC and cygwin to connect to MS SQL?

[genio]

To explain this a bit, you need two things outside of Perl, FreeTDS, and iODBC/UnixODBC. I use UnixODBC, so setup for iODBC may vary slightly.

FreeTDS is the driver you'll use to actually handle the connection to the database via ODBC. Given that, you have to tell UnixODBC how to use the installed FreeTDS driver.

· Once FreeTDS is installed, make sure it is installed to work with UnixODBC via tsql -C
· Find the location of your odbcinst.ini by running odbcinst -s -j
· 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

Then, your connection string will be as follows in Perl:

my $dsn = 'dbi:ODBC:DRIVER={FreeTDS};';
$dsn .= 'SERVER=DB.EXAMPLE.COM;database=NorthWind;';
$dsn .= 'port=1433;tds_version=8.0;';

my $dbh = DBI->connect($dsn, 'username', 'password', {
  PrintError => 0, # don't automatically print out error messages
  RaiseError => 1, # die on error
  AutoCommit => 1, # automatically committed when executed
  LongReadLen => 24*1024, # SQL Server limit
  LongTruncOk => 1, # don't die when we grab really long data :/
  odbc_utf8_on => 1, # utf8 all data. We'll need to decode everything
});

Once you get things working, please line out the steps for me and I will update the gist here: https://gist.github.com/genio/7c7fc52fcb8be5d486aa to reflect this environment's process.

[Tommy]

Oh, awesome! This looks promising, because I've been able to get the tsql command to work OK. It's just the odbcinst.ini file that's been a problem. I'm going to give this a shot in a few minutes and I'll let you know how it works out!

[Tommy]

OK, here's what worked for me, in 5 steps:

Step 1: don't worry about /etc/odbc.ini -- you don't need to create or modify it to make your MS SQL connection work from cygwin.

Step 2: you also don't need to worry about /etc/freetds/freetds.conf -- the defaults work out of the box

Step 3: set up your /etc/odbcinist.ini file like so:

[FreeTDS]
Description     = TDS Conection
Driver          = /usr/lib/cygtdsodbc.dll
Setup           = /usr/lib/cygtdsodbc.dll
UsageCount      = 1
FileUsage       = 1

Step 4: write your Perl code like so (change as desired) #TMTOWTDI

#!perl
use strict;
use warnings;
use 5.020;

use DBI;

my ( $user, $pass ) = ( 'username goes here', 'password goes here' );

my $dsn =
{
   driver   => 'FreeTDS',
   server   => 'my.server.host.name',
   database => 'name_of_db_here',
   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, $pass, $dbi_opts );

my $rows = $dbh->selectall_arrayref( 'SELECT * from some_table_you_kno
+w' );

use Data::Dumper;

say Dumper $_ for @$rows

Step 5: Profit!

It turns out that this wasn't nearly as complicated as I initially was led to believe, and that's fine by me :-)