Perl to Microsoft SQL Server 2008 Standard via ODBC using FreeTDS Drivers

Perl to Microsoft SQL Server 2008 Standard via ODBC using FreeTDS

Today I had a need to have a perl script (running on CentOS 5.4) connect to a MSSQL Server 2008 Std server so i had to do a little research and playing around. The solution to getting everything working with “Free/OpenSource” stuff was not difficult if you don’t follow everything that everyone publishes.

I have provided the steps I used below to get everything working.

Updated step 5 on Sep 11, 2012 with a note that reflects the findings of  user xtruthx.

Pre-requisites

Before you start you need to ensure you know how to satisfy all the RPMs in the pre-requisites.
Location from where you can get the RPMs is listed in parentheses ().

1. RPMs Needed/used:

   perl - 5.8.8-10 (Yum)
   perl-DBI - 1.52-1 (Yum)
   freetds - 0.64-11 (Yum)
   unixODBC-devel - 2.2.11.7.1 (Yum)
   unixODBC - 2.2.11.7.1 (Yum)
   perl-DBD-ODBC - 1.23-1 (http://pkgs.repoforge.org/perl-DBD-ODBC/perl-DBD-ODBC-1.23-1.el5.rf.i386.rpm)
   

Steps to get MSSQL,ODBC and Perl working as one happy product.

1. How did I get ODBC setup?
   Install freetds, unixODBC, unixODBC-devel via Yum

   sudo yum install freetds unixODBC unixODBC-devel
   

   Then install the perl, perl-DBI if you don’t have them installed already.

   sudo yum install perl perl-DBI
   

   If you haven’t downloaded the perl-DBD-ODBC module you can do so using wget/curl/etc. I prefer wget.
   Then install the perl, perl-DBI if you don’t have them installed already.

   wget http://pkgs.repoforge.org/perl-DBD-ODBC/perl-DBD-ODBC-1.23-1.el5.rf.i386.rpm
   

   Then install the perl-DBD-ODBC module. I am assuming the perl-DBD-ODBC is in the same directory you are working in. If not you will need to provide a complete path to the rpm.

   sudo rpm -ivh perl-DBD-ODBC
   

2. DBD::ODBC We used DBD::ODBC. You can use similar methods as above to determine if DBD::ODBC is installed and to see what version you have:To check you have the DBD::ODBC module installed:

   perl -e 'use DBD::ODBC;'
   

   If you have not got DBD::ODBC installed you can build it via CPAN or download the RPM and go from there. I just downloaded it, see the RPM list at the top of the article.To show the DBD::ODBC version:

   perl -MDBD::ODBC -e 'print $DBD::ODBC::VERSION;'
   

   e.g.

   perl -MDBD::ODBC -e 'print $DBD::ODBC::VERSION;'
   1.23
   

   To show all drivers DBI knows about and their versions:

   perl -MDBI -e 'DBI->installed_versions;'
   

   e.g.

   perl -MDBI -e 'DBI->installed_versions;'
     Perl            : 5.008008    (i386-linux-thread-multi)
     OS              : linux       (2.6.9-42.0.3.elsmp)
     DBI             : 1.52
     DBD::Sponge     : 11.10
     DBD::Proxy      : install_driver(Proxy) failed: Can't locate RPC/PlClient.pm in @INC
     DBD::ODBC       : 1.23
     DBD::File       : 0.35
     DBD::ExampleP   : 11.12
     DBD::DBM        : 0.03
   

3. What ODBC drivers have I got?
   You can find out what ODBC drivers are installed under unixODBC with:

   odbcinst -q -d
   

   e.g.

   odbcinst -q -d
   [PostgreSQL]
   

   For unixODBC, drivers are installed in the odbcinst.ini file. You can find out which odbcinst.ini file unixODBC is using with:

   odbcinst -j
   

   e.g.

   odbcinst -j
   unixODBC 2.2.11
   DRIVERS............: /etc/odbcinst.ini
   SYSTEM DATA SOURCES: /etc/odbc.ini
   

4. Here, /etc/odbcinst.ini defines the ODBC drivers. Configure unixODBC to use FreeTDS as the Driver for the MSSQL connection.
   First we need to locate the driver file.

   sudo updatedb
   locate libtdso
   

   e.g.

   sudo updatedb
   locate libtdso
   /usr/lib/libtdsodbc.so.0
   /usr/lib/libtdsodbc.so.0.0.0
   

   Now we know where the source files are so lets create a driver template to use for driver installation.
   You can use vi if you want, but to make sure people can copy/paste as much as possible I have provided an echo command below.
   Adjust the path and possibly filename for “/usr/lib/libtdsodbc.so.0”.
   Note: Only use the driver that has a single 0 if there is more than one returned like my example.

   echo "[FreeTDS]
   Driver = /usr/lib/libtdsodbc.so.0" >> tds.driver.template
   

   Now lets install the driver from the template. This will install the driver so anyone can use this driver.

   sudo odbcinst -i -d -f tds.driver.template
   

   e.g.

   sudo odbcinst -i -d -f tds.driver.template
   odbcinst: Driver installed. Usage count increased to 1.
       Target directory is /etc
   

   If you check to see what drivers are available to ODBC you will now see “FreeTDS” has been added.

   odbcinst -q -d
   

   e.g.

   odbcinst -q -d
   [PostgreSQL]
   [FreeTDS]
   

5. Configure ODBC with MSSQL parameters.
   To do this we need to modify the /etc/odbc.ini file.
   Your odbc.ini file could be in a different location. use odbcinst -j to location your ini file.
   You need to adjust the values accordingly for the echo command to work for your install.
   You will want to be root to do the file modification. I jump to root by way of sudo su -.
   Note:You will need a SQL user account setup in MSSQL to allow you to connect remotely with the way I am showing you.Note: A user (xtruthx) has reported that “TDS Version” needed an underscore in order to work for his setup. The user used “TDS_Version = 8.0” instead of what is in the example below.

   sudo su -
   echo "[MSSQL]
   Driver = FreeTDS
   Address = IPADDRESSOFMSSQL
   Port = 1433
   TDS Version = 8.0
   Database = MYDATABASENAME" >> /etc/odbc.ini
   

   e.g.

   sudo su -
   echo "[MSSQL]
   Driver = FreeTDS
   Address = 192.168.10.1
   Port = 1433
   TDS Version = 8.0
   Database = Northwind" >> /etc/odbc.ini
   

6. Test the ODBCconnect to your MSSQL database.

   isql -v -s MSSQL SQLUSERNAME SQLUSERPASSWORD
   +---------------------------------------+
   | Connected!                            |
   |                                       |
   | sql-statement                         |
   | help [tablename]                      |
   | quit                                  |
   |                                       |
   +---------------------------------------+
   SQL>
   

7. If the step above was successful then lets try using a Perl script.
   Note: Below I specify DSN=MSSQL, that should match the name in [ ] in your odbc.ini file.
   You will also want to change SQLUSERNAME and SQLUSERNAMEPASSWORD to your username/password needed to connect to MSSQL using SQL username/password.
   This is NOT windows authentication.

   #!/usr/bin/perl
   use DBI;
   use strict;
   use DBI;
   my @dsns = DBI->data_sources('ODBC');
   foreach my $d (@dsns)
   {
     print "$d\n";
   }
     my $dbh = DBI-> connect('dbi:ODBC:DSN=MSSQL;UID=SQLUSERNAME;PWD=SQLUSERPASSWORD') or die "CONNECT ERROR! :: $DBI::err $DBI::errstr $DBI::state $!\n";
   if ($dbh)
   {
     print "There is a connection\n";
     my $sql = q/SELECT * FROM dbo.users/;
     my $sth = $dbh->prepare($sql);
     $sth->execute();
     my @row;
     while (@row = $sth->fetchrow_array) {  # retrieve one row at a time
       print join(", ", @row), "\n";
     }
     $dbh->disconnect;
   }
   

External Reference links used to get everything working.

http://www.unixodbc.org/doc/FreeTDS.html
http://www.martin-evans.me.uk/node/20
http://www.easysoft.com/developer/languages/perl/dbi_dbd_odbc.html
http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_1.html
http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_2.html
http://www.easysoft.com/developer/languages/perl/sql_server_unix_tutorial.html
http://www.easysoft.com/developer/languages/perl/tutorial_data_web.html
http://www.easysoft.com/developer/languages/perl/dbi-debugging.html