CSIM Technical Notes

Accessing an MS SQL server from Perl is a topic that lack complete information (or at least information that I can relate to.)

One solution is to use DBD::Sybase module in a compatible way. Better is to use DBD::ODBC in a more native way.

While the file path and name are specific to freeBSD, I am confident that the mechanism is the same fo other Unixes and the relationship between the components should be the same.

Install the needed components

You need to install:

• DBD::ODBC the Perl module
• unixODBC the ODBC library for Unix
• FreeTDS the Microsoft TDS library for Unix

Configure and test FreeTDS

FreeTDS configuration file is located in /usr/local/etc/freetds/freetds.conf and you need to add a section of the form:

# A typical Microsoft server
[SOMENAME]
        host = IP or hostname of MS SQL server
        port = 1433
        tds version = 7.0

You can test the configuration with:

tsql -S SOMENAME -U usernamne -P password -D database

At the prompt >1 you can enter SQL queries, enter go to execute the query.

Configure and test unixODBC

unixODBC configuration files are located in /usr/local/etc/odbcinst.ini and /usr/local/etc/odbc.ini.

The file odbcinst.ini defines the driver used by the database and should have a section of the form:

[some driver name]
Driver = /usr/local/lib/libtdsodbc.so
FileUsage   = 1

The Driver option points to the library in use, in that case, it points to FreeTDS driver.

The file odbc.ini defines the database server and should have a section of the form:

[SOMENAME]
Driver = some driver name (sqame as the section name above)
Database = database name
Server = IP or hostname of MS SQL server
TDS_Version = 7.0
Port = 1433

You can test the configuration with:

isql -v SOMENAME username password

At the prompt, you can enter SQL queries.

Configure and use DBD::ODBC

If everything worked so far, it is as simple as using:

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