Connecting to SQL Server using Perl

I'm new to perl.  Can anyone help me with how I find a DBI driver for MS SQL Server, and how I could connect to MS SQL Server using perl?

Thanks
LVL 1
HLRosenbergerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HLRosenbergerAuthor Commented:
Follow up:

By issuing this command: print map "$_\n", DBI->available_drivers;     I get this list:

DBM
ExampleP
File
Gofer
ODBC
Pg
Proxy
SQLite
Sponge
mysql


This list shows ODBC.  If on Windows XP, my ODBC Data Source Administrator, I have a MS Access data source, does this mean I can use perl to connect to any MS access database?   And if so, what' the proper per command.  Something like this:

my $dbh = DBI->connect("dbi:MS Access Database:phonebill", $user, $password)
0
deiaccordCommented:
This is a very simple example but should get you working on the right road.

If you are using ActiveState perl you sholud be able to use ppm to ensure the dbi drivers are installed (I believe they are by default)

To use a trusted connection set that parameter to Yes and set the username and password to undef (with no quotes)
use DBI;
use DBI qw(:sql_types);
$dbh = DBI->connect('dbi:ODBC:DRIVER=SQL Server;
				DATABASE=database_name;
				APP=Microsoft Data Access Components;
				Trusted_Connection=No;
				SERVER=mssql_server;',"username","password");

$sth = $dbh->prepare("SELECT * FROM table");
$tmp = $sth->execute();

while(@row = $sth->fetchrow_array) {
	print @row;
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HLRosenbergerAuthor Commented:
OK.  I'll give that a shot.  I do not know what ActiveState perl is, nor do I know what ppm is.  Is ActiveState perl a particular version of perl?

I'm using "strawberryl" perl  on Windows XP.  
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

deiaccordCommented:
Although I do not use Straberry perl myself I believe it also comes with the ODBC client built in.

It is possible to connect to an Access database or otherwise by modifying the connection string. You could just setup a System DSN in the control pannel using whatever driver you want (MSSQL, Access, Oracle, Excel....) and just reference it by name depending on you needs.

In the below example this uses a ODBC connectection called 'Test'


$dbh = DBI-> connect('dbi:ODBC:Test', username, password)

Open in new window

0
HLRosenbergerAuthor Commented:
That worked! Great!  

Two Question:
1) How would I change it to access a MS Access database?

2) How do I reference columns within row?
0
deiaccordCommented:
To reference a column in a row use $row[0], $row[1] etc to reference the column recturned rather than the whole row array ( @row ) in my example.

If you check in the dbi documentation there is a way to return the data as a hash rather than an array if you need to reference them by name rather than the position from you select cause.
0
HLRosenbergerAuthor Commented:
Thanks again!  Terrific.

Question:  What does   print @row;  return all the columns concatenated together, while  print "@row";  places a space between each column?



0
HLRosenbergerAuthor Commented:
OK .  For access, I added a DSN, named Aramark.

DBI->connect("dbi:ODBC:DRIVER=Microsoft Access Driver;
                                                DATABASE=c:\\Aramark\\Aramark.mdb;","","")

 But I get this error:

connect('Aramark;','',...) failed: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002) at C:\Program Files\OptiPerl\webroot\cgi-bin\newscript1.cgi line 30                                              
0
HLRosenbergerAuthor Commented:
For Access, I'm using this to connect:

$dbh1 = DBI->connect("dbi:ODBC:DRIVER=Microsoft Access Driver (*.mdb);
                                                DSN=Aramark;","","")
                                                or die "Connect Error: $DBI::errstr\n";

I have a DSN named Aramark.

I'm getting this error:


DBI connect('DRIVER=Microsoft Access Driver (*.mdb);
                                                DSN=Aramark;','',...) failed: (no error string) at C:\Program Files\OptiPerl\webroot\cgi-bin\newscript1.cgi line 28
Connect Error:

What's the no error string mean?
0
HLRosenbergerAuthor Commented:
AH!  This works!

DBI->connect("dbi:ODBC:DRIVER=Microsoft Access Driver (*.mdb);
                       DBQ=c:\\Aramark\\Aramark.mdb;");


Why DBQ keyword, and what does it mean?  Why DBQ for Access, and not the DSN name that I setup in the ODBC manager?
0
deiaccordCommented:
Try something like the below

$dbh = DBI-> connect('dbi:ODBC:Aramark', undef, undef);

for your access database you setup in ODBC manager.

Good luck.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.