Connecting to MS SQL databases with Perl on Unix

This entry won’t be too explorative. It’s rather a really short mini-HOWTO (or simply a practical example). Googling the issue mentioned in the subject of this entry I found nothing but chaos. Lots of terms, lots of software, but no clear hints as for what is what and how to connect all this stuff together.

Just a few words of introduction. I wanted to use the DBI module and it’s underlying DBD::ODBC. To connect to an MS SQL database, a Unix system needs a driver manager, a driver and a data source. So, moving on:

  1. Install unixODBC (http://www.unixodbc.org/) – the driver manager. I worked in CentOS, so the following should be enough:
    yum install unixODBC
    yum install unixODBC-devel
    
  2. Install FreeTDS (http://www.freetds.org/) – the driver for Microsoft and Sybase databases. The installation process looks as above:
    yum install freetds
    
  3. Install DBD::ODBC:
    perl Makefile.PL -o /usr
    make
    make test
    make install
    
  4. Make unixODBC use FreeTDS. Create a file with any name you want, e.g. freetds.template and the following contents::
    [FreeTDS]
    Description = For MS SQL
    Driver      = /usr/lib/libtdsodbc.so.0
    
  5. Register the FreeTDS driver in unixODBC, running the following command as root:
    # odbcinst -i -d -f freetds.template
    
  6. Define the data source. Create a file with any name you want, e.g. datasource.template and the following contents:
    [MSSQLDatabase]
    Driver      = FreeTDS
    Description = Database description
    Trace       = No
    Server      = 192.168.1.100
    Port        = 1433
    Database    = mydatabase
    
  7. Run the following command as a regular user:
    $ odbcinst -i -s -f datasource.template
    
  8. The command from step 7 creates (or modifies) the .odbc.ini file in the user’s home directory. That’s enough for the scripts ran by that user to connect to such defined data source. If you want all users to have access to a certain data source, you need to add an entry for it in the /etc/odbc.ini file. So, if the .odbc.ini file created by the command from step 7 contains only one data source, the following command should be enough to enable all users to use it:
    # cat /home/user/.odbc.ini >> /etc/odbc.ini

Connecting to such defined data source may look as following:

my $dbh = DBI->connect("dbi:ODBC:MSSQLDatabase", "username", "password");

From then on you can use all the DBI module functions, such as prepare(), execute() etc.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s