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:
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?
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
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.
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.
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.
Post a Comment