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>
Post a Comment