Connect to SQL Server 2005 from Perl and do a SELECT

Question

How do I do a SELECT on a SQL Server 2005 from a Perl script?

Answer 1

Here's a basic example using DBI (edited after comment):

use DBI;

my $dbh = DBI->connect("dbi:Sybase:database=<dbname>;server=<servername>",
    <user>, <password>, 
    { PrintError => 0, RaiseError => 1 });
my $sth = $dbh->prepare( "select field from table" );
my $result = $sth->execute();
while( my $result = $sth->fetchrow_hashref ) {
    print $result->{field};
}
$sth->finish;
$dbh->disconnect;

Hoping to see other answers with a simpler solution :)

Answer 2

You will need to use DBI and you are probably best using the DBD::ODBC provider from (CPAN). If you don't know about DBI, then you need to read up about that. There's a book (Programming the Perl DBI) which is old but still valid.

Then something like the following:

use strict;
use warnings;
use DBI;

# Insert your DSN's name here.
my $dsn = 'DSN NAME HERE'

# Change username and password to something more meaningful
my $dbh = DBI->connect("DBI::ODBC::$dsn", 'username', 'password')

# Prepare your sql statement (perldoc DBI for much more info).
my $sth = $dbh->prepare('select id, name from mytable');

# Execute the statement.
if ($sth->execute)
{
    # This will keep returning until you run out of rows.
    while (my $row = $sth->fetchrow_hashref)
    {
        print "ID = $row->{id}, Name = $row->{name}\n";
    }
}

# Done. Close the connection.
$dbh->disconnect;