Solved

Perl Connect to Sql Server 2005

Posted on 2009-04-02
4
617 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
Adam314 earned 80 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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

623 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