Procedure for connecting to SQL Server using Perl's DBI on CentOS

Perl
database
here

Here are the steps to connect to SQL Server using Perl's DBI on CentOS. You need to properly install and configure the Unix (Linux) libraries FreeTDS, Unix ODBC and the Perl DBD::ODBC module.

(The environment I tried was CentOS release 5.5 (Final). This article may be valid for Red Hat Enterprise Linux, Fedora, and also for other Linux distributions. Disable SE Linux If you don't, the installation may not work. Since it is installed from the source, it is assumed that development tools such as gcc are pre-installed.)

The image of the connection is as follows.

DBI-ODWD (using FreeTDS protocol for communication)-SQL Server

DBI requires ODBC as a driver. The corresponding Perl module is DBD::ODBC. DBD::ODBC also requires a library called UnixODBC. A library called UnixODBC requires a library called FreeTDS to communicate with SQL Server.

Installing UnixODBC

Unix ODBC is one of the Unix implementations of ODBC. Let's install Unix ODBC from source code. As the number of dependent modules increases, GUI support is optionally removed. Make will take some time, so wait for a while.

cd/usr/local/src
wget http://www.unixodbc.org/unixODBC-2.3.0.tar.gz
tar -xzvf unixODBC-2.3.0.tar.gz
cd unixODBC-2.3.0
./configure - enable-gui = no
make
make install

This library is installed under "/ usr/local/lib". Let's check.

ls /usr/local/lib/libodbc.so

FreeTDS installation

FreeTDS is a library that implements the "Tabular Data Stream" communication protocol. Used by Unix ODBC.

cd/usr/local/src
wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-0.82.tar.gz
tar -xzvf freetds-0.82.tar.gz
cd freetds-0.82
export LD_LIBRARY_PATH =/usr/local/lib
export LD_RUN_PATH =/usr/local/lib
./configure - with-unixodbc =/usr/local - with-msdblib
make
make install

This library is installed under "/ usr/local/lib". Let's check.

ls /usr/local/lib/libtdsodbc.so

DBD::ODBC module installation

Install the DBD::ODBC module.

export ODBCHOME =/usr/local
cpan DBD::ODBC

DBD::ODBC does not need to be installed with root privileges, so it is a good idea to install it in your personal directory using something like cpanm.

FreeTDS settings

Edit the FreeTDS configuration file. Describe the connection destination information etc. in the FreeTDS configuration file.

vi /usr/local/etc/freetds.conf

[tdssqlserver]
host = 192.168.1.19
port = 1433
tds version = 8.0

Specify the connection destination information and TDS version. Currently 8.0 seems to be fine. The name in [] is the name used in Unix ODBC.

UnixODBC settings

Edit the Unix ODBC configuration file.

vi /usr/local/etc/odbc.ini

[ODBC Data Sources]
freetds = FreeTDS ODBC Driver

[sqlserver]
Driver = /usr/local/lib/libtdsodbc.so
Description = Microsoft SQL Server
Servername = tdssqlserver
Database = dbname

For Driver, specify the path of the FreeTDS library, for Servername, specify the name set in the FreeTDS configuration file, and for DesDatabase, specify the database name of the connection destination.

Check FreeTDS and UnixODBC settings

First, let's check if the FreeTDS settings are correct by invoking the command tsql.

Next, let's try using tsql and isql to see if the settings are correct.

tsql -S tdssqlserver -U kimoto -P pipipi

If the connection with tsql is successful, you can confirm that the FreeTDS settings are correct. You can exit with exit.

isql sqlserver kimoto pipipi

If the connection with isql is successful, you can confirm that the Unix ODBC settings are correct. You can exit with the Enter key.

Connect to SQL Server from Perl

Then if you can connect from the script using the DBI module, you are successful.

use strict;
use warnings;

use DBI;

my $dbi = DBI->connect('dbi: ODBC: sqlserver', 'kimoto', 'pipipi')
  or die $!;

Perl ABC