Solved

Perl Connect to Sql Server 2005

Posted on 2009-04-02
4
578 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 80 total points
Comment Utility

#!/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
Comment Utility
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
Comment Utility
Is there a way without installing DBD-ODBC package?
0
 
LVL 39

Expert Comment

by:Adam314
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now