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...
Results:
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>
Comments
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?
Thanks
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.
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.
It's been a long time since I worked on any of this so I am probably no longer the right person to ask.