Solved

Perl Connect to Sql Server 2005

Posted on 2009-04-02
4
609 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Grid querry results 41 83
kill process lock Sql server 9 75
LAG_ROWID - how do I get the right order using this query? 2 22
Stored Procedure needs owner to execute 5 45
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
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…

726 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