Procedure for connecting to SQL Server using Perl's DBI on CentOS
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 $!;