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

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;

Answer 2

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 3

        #
        # ------------------------------------------------------
        # run a passed sql and retun a hash ref of hash refs
        # ------------------------------------------------------
        sub doRunSqlGetHashRef {

            my $self                   = shift ;
            my $sql                    = shift ;

            my $hsr_meta               = {} ;
            my $hsr                    = {} ;
            my $rowid = 0 ;
            my $flag_filled_hsr_meta   = 0 ;
            my $hsr_meta_colid         = 0 ;

            use DBI;

            my $dbs = "dbi:ODBC:DRIVER=FreeTDS;DSN=DEV_MSSQLSRV_DSN";
            # verify by :
            # isql -v DEV_MSSQLSRV_DSN user pwd

            my $dbh = DBI->connect($dbs, $db_user, $db_user_pw)
                or die "CONNECT ERROR! :: $DBI::err $DBI::errstr $DBI::state $!\n";

            if (defined($dbh)) {

                # Prepare your sql statement (perldoc DBI for much more info).
                my $sth = $dbh->prepare( $sql ) ;

                # Execute the statement.
                if ($sth->execute) {
                # This will keep returning until you run out of rows.
                    while (my $row = $sth->fetchrow_hashref)  {

                        # fill in the meta hash reference with the col names
                        if ( $flag_filled_hsr_meta == 0 ) {
                            for (@{$sth->{ 'NAME' }}) {
                                # debug ok print "$_ => $row->{$_}\t";
                                $hsr_meta->{ $hsr_meta_colid } = $_ ;
                                $hsr_meta_colid++ ;
                                $flag_filled_hsr_meta = 1 ;
                            }
                        }
                        # p ( $row ) ; # row level debug ...
                        $hsr->{ $rowid } = $row ;
                        $rowid++ ;
                    }
                }
                # Done. Close the connection.
                $dbh->disconnect;

                # debug ok p( $hsr_meta ) ;

                return ( $hsr_meta , $hsr )  ;
            }
            else {
                print "Error connecting to database: Error $DBI::err - $DBI::errstr\n";
            }

        }
        #eof sub doRunSqlGetHashRef