Solved

Perl Connect to Sql Server 2005

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2005 - Memory Table Column Names 11 72
Perl Untar File 1 45
Can Unique column have more than one Null? 8 53
Create an automated page index 9 16
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…
I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
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 video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

809 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