Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

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

by genio (Sexton)
on Jul 13, 2016 at 21:34 UTC ( #1167735=note: print w/replies, xml ) Need Help??

in reply to Using DBD::ODBC and cygwin to connect to MS SQL?

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: to reflect this environment's process.

Replies are listed 'Best First'.
Re^2: Using DBD::ODBC and cygwin to connect to MS SQL?
by Tommy (Chaplain) on Jul 14, 2016 at 12:45 UTC

    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!

    A mistake can be valuable or costly, depending on how faithfully you pursue correction

      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 => '', 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 :-)

      A mistake can be valuable or costly, depending on how faithfully you pursue correction

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1167735]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (7)
As of 2017-06-28 08:05 GMT
Find Nodes?
    Voting Booth?
    How many monitors do you use while coding?

    Results (628 votes). Check out past polls.