Thursday, July 30, 2009

Real simple to connect to MSSQL from Perl

Again, this took me a little while but here I am. Connecting to a database ( SQL SERVER 2008 ) from a Windows Server 2008 VM I own ( thanks TECH ED 2008 ) and Active State Perl.

Install Scite cos it's a nice editor and you are away laughing...

#!/usr/bin/perl

use DBI;

my $DSN = 'driver={SQL Server};Server=localhost; database=AdventureWorks;TrustedConnection=Yes';
my $dbh = DBI->connect("dbi:ODBC:$DSN") or die "$DBI::errstr\n";

my $sth = $dbh->prepare('select top 10 * from Sales.vSalesPerson')
or die "Couldn't prepare statement: " . $dbh->errstr;

$sth->execute();

while( @data = $sth->fetchrow_array())
{
foreach(@data) {
print "[$_]";
}
print "\n\n";
}

$sth->finish;
$dbh->disconnect;


Results:


C:\Users\dave\Documents>perl perl1.pl
[268][][Stephen][Y][Jiang][][North American Sales Manager][238-555-0197][stephen
0@adventure-works.com][0][2427 Notre Dame Ave.][][Redmond][Washington][98052][Un
ited States][][][][677558.4653][.0000]

[275][][Michael][G][Blythe][][Sales Representative][257-555-0154][michael9@adven
ture-works.com][1][8154 Via Mexico][][Detroit][Michigan][48226][United States][N
ortheast][North America][300000.0000][4557045.0459][1750406.4785]

[276][][Linda][C][Mitchell][][Sales Representative][883-555-0116][linda3@adventu
re-works.com][0][2487 Riverside Drive][][Nevada][Utah][84407][United States][Sou
thwest][North America][250000.0000][5200475.2313][1439156.0291]

[277][][Jillian][][Carson][][Sales Representative][517-555-0117][jillian0@advent
ure-works.com][1][80 Sunview Terrace][][Duluth][Minnesota][55802][United States]
[Central][North America][250000.0000][3857163.6332][1997186.2037]

[278][][Garrett][R][Vargas][][Sales Representative][922-555-0165][garrett1@adven
ture-works.com][0][10203 Acorn Avenue][][Calgary][Alberta][T2P 2G8][Canada][Cana
da][North America][250000.0000][1764938.9859][1620276.8966]

[279][][Tsvi][Michael][Reiter][][Sales Representative][664-555-0112][tsvi0@adven
ture-works.com][1][8291 Crossbow Way][][Memphis][Tennessee][38103][United States
][Southeast][North America][300000.0000][2811012.7151][1849640.9418]

[280][][Pamela][O][Ansman-Wolfe][][Sales Representative][340-555-0193][pamela0@a
dventure-works.com][1][636 Vine Hill Way][][Portland][Oregon][97205][United Stat
es][Northwest][North America][250000.0000][.0000][1927059.1780]

[281][][Shu][K][Ito][][Sales Representative][330-555-0120][shu0@adventure-works.
com][2][5725 Glaze Drive][][San Francisco][California][94109][United States][Sou
thwest][North America][250000.0000][3018725.4858][2073505.9999]

[282][][JosΘ][Edvaldo][Saraiva][][Sales Representative][185-555-0169][josΘ1@adve
nture-works.com][0][9100 Sheppard Avenue North][][Ottawa][Ontario][K4B 1T7][Cana
da][Canada][North America][250000.0000][3189356.2465][2038234.6549]

[283][][David][R][Campbell][][Sales Representative][740-555-0182][david8@adventu
re-works.com][0][2284 Azalea Avenue][][Bellevue][Washington][98004][United State
s][Northwest][North America][250000.0000][3587378.4257][1371635.3158]


C:\Users\dave\Documents>

5 comments:

David Latham said...

Oh Yeah - SQL Server 2008 needed some basic configuration.

1. Enable TC-IP ( 1433 ) connections using the Configuration Manager
2. Ensure the windows authenticated user executing the perl script is also permitted to access the database and whatever part of the database you are accessing. I just made my self an administrator and allows my username full access to MSSQL Server.
3. Make sure there are firewall holes.

Anyone else got any gotchas?

Chirag K. Shah said...

I am trying to connect to a SQL server. Tried your Perl program but no where closer to get it working. How can server be localhost when the server is located out of the system.

Thanks

David Latham said...

So if the server is located out of the system (on SQLDB01.example.net for example) then the server=localhost could change to server=SQLDB01.example.net or even simply the ip address of the server will do.

try:
my $DSN = 'driver={SQL Server};Server=SQLDB01.example.net; database=AdventureWorks;TrustedConnection=Yes';

or:
my $DSN = 'driver={SQL Server};Server=192.168.0.51; database=AdventureWorks;TrustedConnection=Yes';

if your database server is (as it very well should be) on a different physical machine to the application and the name of this machine is SQLDB01.example.net or it's ip address is 192.168.0.51

Substitute your own information for your own network.

HoosierDaddy said...

Any thoughts on connecting a perl program running on a Linux or OS X machine without using ODBC?

I looked in CPAN but most of those packages are from back in the day with Microsoft SQL Server and Sybase were in bed together...a loooooong time ago.

David Latham said...

Unfortunately no. I think that ODBC is almost always required for connecting to Microsoft SQL from Non MS environments. It's kind of the purpose of ODBC.

It's been a long time since I worked on any of this so I am probably no longer the right person to ask.