CONNECT TO SQL USING PERL FROM LINUX

Introduction:

I recently came across a situation where I need to connect SQL using Perl from Linux box. As I have not done that I was like, ‘Is it possible to connect MSSQL from Linux?” but after googling I came to know Microsoft has a driver for Linux which can be used to connect via Linux. I had tried it and it worked like a charm. I was able to connect to SQL server using Microsoft binary. The real problem is that it cannot be used as Perl module to extend the usability via scripting.

Again I started my search and reading multiple articles, websites and I was able to successfully connect to SQL using Perl from Linux. I will briefly describe how to achieve it and where to get the packages in this article.

MSSQL Driver for Linux:

It was always tough to connect to MSSQL from Linux but surprising Microsoft has provided an ODBC driver which can be used to have easy access to SQL server via cmd. The details can be gathered from the below screenshot.

Packages needs to be installed:

• Unix-ODBC
• Unix-ODBC-devel
• FreeTDS
• DBI::Sybase http://search.cpan.org/~mewp/DBD-Sybase-1.15/

Unix-ODBC:

      This can be installed via the default Linux distro package manager.

RHEL:

      yum install unixODBC

It resolves and auto install unixODBC-Devel and freeTDS as dependencies. The distro which does not have FreeTDS and UNIX-ODBC, please download the latest stable packages form the respective website and you have to install it manually using below steps.

Building FreeTDS:

$ ./configure --prefix=/usr/local

$ make

$ su root

Password:

$ make install

Source: http://www.freetds.org/userguide/config.htm

Configuration:

      Once the installation gets completed. The main part is to configure the freetds and odbc.ini, odbcinst.ini files. This plays a crucial part in connecting to SQL server.

FreeTDS:

Edit the freetds.conf from the location you had installed earlier. If you have installed via package manager it can be found at /etc/freetds.conf.

The below is the template for adding DB details.

[DBName]

host =

port = 1433

tds version = 8.0

For some unknown reason, I had to change the version of TDS to 8.0 from 7.0 to get it connect to SQL server 2012.

Odbc.ini

This file passes the details such as DataSource and other driver details to establish the connection.

The below is the template for editing odbc.ini.

[ODBC Data Sources]

DBName = MSSQL Server

[DBName]

Driver = freetds

Description = Microsoft SQL

Trace = No

Server =

port = 1433

Database = DBName

TDS_Version = 7.1

Odbcinst.ini

        This file passes which DB we are connecting and the ODBC driver details. The below is the template for editing odbcinst.ini.

[freetds]

Description = MS SQL database access with FreeTDS

Driver = /usr/lib64/libtdsodbc.so.0

Setup = /usr/lib64/libtdsodbc.so.0

UsageCount = 1

Testing:

Once everything is in place, it’s time for us to test the freetds configuration. It’s important that freetds connection should work to move further.

Execute the below command to verify whether freetds has configured properly.

Execute the below command to test the connection gets established or not.

TDSERVER=7.0 tsql -H -U -p -D

If it’s successful you can see the output as below.

Installing Perl Module:

As we had completed most of the integral configuration, it’s time to install Perl module which we can use later to connect to SQL DB via scripting. There are few ways to use Perl to connect to a SQL Server using FreeTDS. All of them can be installed via CPAN but I have tried installing configuring the module.

•  DBD::Sybase
•    DBD::ODBC
•    Sybperl

DBD::Sybase:

Despite the name it works for any Sybase or Microsoft SQL Server. DBD::Sybase uses the CT-Library API and works well.

Building DBD::Sybase

       Download the latest Sybase module from cpan.org and follow the below steps.

export SYBASE=/usr/local/freetds

perl Makefile.PL

Use 'CHAINED' mode by default (Y/N) [Y]: Y (if this Y, no need to use $dbh->commit() in the script. Autocommit does the job).

Make

make install (as root)

Connect to Server using DBD::Sybase

#!/usr/bin/perl

#

use DBI;

my $user = "";

my $passwd = '

my $server = "

my $dbh = DBI->connect("DBI:Sybase:server=$server", $user, $passwd, {PrintError => 0});

$dbh->do(use ); #Enter your DBName to for default connection.

unless ($dbh) {

die "ERROR: Failed to connect\nError Message: $DBI::errstr";

}else {

print "\n";

print "Successful Connection.";

print "\n\n";

}

$dbh->disconnect;