Establish Connection from Perl to Microsoft SQL Server

It’s very easy to use perl DBI to connect to mysql server. It’s a little bit headache for Microsoft SQL Server as the DBD::MSSQL is far less mature than the PHP equivalent. However, as I need to use perl Imager to batch create image tiles, i did some googling to figure out how to do the job.

There are two ways, both work only on Microsoft windows:

1. Use Win32::SqlServer module. This module is not included in ppm repositories so you need to go to their homepage to download from the web:
http://www.sommarskog.se/mssqlperl/index.html
To eastablish a connection and query a sql statement:

use Win32::SqlServer;
my $sqlsrv = sql_init($server, $user, $pass, $database);
my $result = $sqlsrv->sql(’blah blah blah’);

2. Use ODBC data source. You need to go to control panel->administrative tools->Data Sources
Under User DSN tab, hit add
Choose data driver, and configure the ODBC, don’t forget to name it. When it’s all done, we use perl DBI

use DBI;
my $dbh = DBI->connect(”dbi:ODBC:’your odbc name here’”, $user, $pass);
#The user name and password are not needed if you do windows authentication.

When the database handler is established, the rest are the same for all DBI methods. The advantage of ODBC is it can be connected via other languages, such as R or JAVA.

P.S. Found another package, Data::Dumper, pretty easy to dump all the data structure in array or hashes, similar to PHP print_r