Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Perl Connect to Sql Server 2005

Posted on 2009-04-02
4
Medium Priority
?
626 Views
Last Modified: 2012-05-06
Hi Experts

Windows Server 2003
MS SQL Server 2005

I require a perl script that executes a sql select statement and outputs the result to a text file.
This perl script will be run inside an Openview policy.
The sql isn't anythng fancy, something like:
select count(USER_ID) from USERS where STATUS = 'Open'

How can I do this?

Thanks in advance
0
Comment
Question by:mistermuv
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
Adam314 earned 240 total points
ID: 24051777

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
 
open(my $out, ">output.txt") or die "Output: $!\n";
 
#You will need to define your $dsn, $user, and $pass
my $dbh = DBI->connect($dsn, $user, $pass);
my $sth = $dbh->prepare(qq{select count(USER_ID) from USERS where STATUS = 'Open'});
$sth->execute;
while(my $row=$sth->fetchrow_arrayref) {
    print $out join(",", @$row) . "\n";
}
close($out);

Open in new window

0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24052291
Hi mistermuv, the assumption with Adam's script is that you install DBD-ODBC package.

If using ActiveState Perl, do: ppm install DBD::ODBC
DBI should already exist, but DBD::* usually requires install. If you are also missing DBI just install it the same way with ppm.

0
 

Author Comment

by:mistermuv
ID: 24070809
Is there a way without installing DBD-ODBC package?
0
 
LVL 39

Expert Comment

by:Adam314
ID: 24131517
The DBI module is a generic interface for perl to talk to any database.  Behind it are DBD::* modules, where each one is designed to talk to a specific database.  This allows your perl code to be written independent of which database you are using - you just tell it (through the $dsn) which DBD driver to use.  Using DBI along with one of the DBD drivers is by far the most common method to having perl talk to a database.

So, is it DBD::ODBC that you don't want to install, or you don't want to install any modules?

If you just want to avoid DBD::ODBC, these DBD modules should work to talk to ms sql server:
    DBD::FreeTDS
    DBD::ADO
They use the DBI module, so the code above should work as is.  This is by far the recommended method.

If you are trying to avoid DBI completly, there are a few modules that might help:
    Win32::ODBC
    Win32::OLE with ADO
    Win32::ADO
These are far less standard methods of connecting perl to a database.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question