Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

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

by Tommy (Chaplain)
on Jul 12, 2016 at 23:36 UTC ( #1167659=perlquestion: print w/replies, xml ) Need Help??

Tommy has asked for the wisdom of the Perl Monks concerning the following question:

I find myself in the precarious situation of needing to connect to an MS SQL database from cygwin Perl. So far I've only been able to piece together tidbits, and even got the whole DBIx::Class stack of modules built+installed to use the DBD::ODBC driver (no small feat).

I wonder, has anyone sailed these waters and lived to tell the tale? If so, I hope you'd be willing to offer some pointers in what that setup looked like. I'm struggling to get it operable.

Tommy
A mistake can be valuable or costly, depending on how faithfully you pursue correction
  • Comment on Using DBD::ODBC and cygwin to connect to MS SQL?

Replies are listed 'Best First'.
Re: Using DBD::ODBC and cygwin to connect to MS SQL?
by genio (Beadle) on Jul 13, 2016 at 01:32 UTC

      Thanks genio. That gist is very similar to what I've done so far, but I'm still confused. Cygwin doesn't have a libtdsodbc.so, or libtdsS.so. The only thing I can find is: /usr/lib/cygtdsodbc.dll

      Tommy
      A mistake can be valuable or costly, depending on how faithfully you pursue correction
Re: Using DBD::ODBC and cygwin to connect to MS SQL?
by soonix (Canon) on Jul 13, 2016 at 10:35 UTC

      Thanks for the reply, soonix. This is all new territory for me. I've only ever worked with databases running on Linux/UNIX servers before. I'd like to keep it as simple as possible, but I've read that I need to set up unixodbc and freetds to get this to work. Am I missing something?

      The reference I've been using so far is this StackOverflow post. How do you recommend I do it?

      Tommy
      A mistake can be valuable or costly, depending on how faithfully you pursue correction
        Well, to find out wether the stumbling block is Perl, ODBC, some driver or even the database configuration, I'd try to connect to the database via the most simple tool. "Simple" in this case means: pre-existing and easily configured.

        If you're on Windows, you might have MS Access or Excel, both of which can connect to "external" sources, hidden under something like "Import external data".

        There probably is the possibility to use native MSSQL. I'd try this first to see if the database is reachable at all.
        I know cygwin Perl only from hearsay, but chances are that instead of DBD::ODBC you could also use DBD::ADO (I use this with Strawberry Perl), which AFAIK uses the same "native" drivers as Excel/Access.

        Then I'd try ODBC (probably under "other", where you will have to select in a next step some "data source". If it is not already set up for you, you'll need it for Perl, too, anyway.

        After you got that working, you can extract the connection string from the connection's properties within Excel/Access

        updated "set up" url

Re: Using DBD::ODBC and cygwin to connect to MS SQL?
by genio (Beadle) on Jul 13, 2016 at 21:34 UTC

    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.

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

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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1167659]
Approved by Paladin
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (4)
As of 2022-11-28 18:30 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?