What is an easy way to read a sql server table into a hash in Perl?

Question

I'd like a simple perl script / sub to read a sql server table into a hash, does anyone have a good script or snippet (with 'use's) that can do this given a connection string and table name?

Here's a template:

sub sqltable {    
    my ($connStr,$table) = @_;    
    my ($user, $password, $host) = ($connectstr =~ m|^(.*)/(.*)@(.*)$|); # or whatever    
    my $dbh = DBI->connect("dbi:???:$host",$user,$password, { RaiseError => 1 }); # replace ??? with good SQL Server equivalent
    $dbh-> ???; # anything good for this part?  LongTruncOk?  LongReadLen?    
    return $dbh->selectall_arrayref("select * from $table");    
}

Answer 1

See selectall_hashref in the Perl DBI page.

Answer 2

Regarding connecting to SQL Server using Perl,

• If you're on a Windows machine, see: How do I connect to a MSSQL database using Perl’s DBI module in Windows?

• If you're on Linux, see: How do I access a SQL Server database from a Perl script in Linux?

Answer 3

You are exactly describing Tie::DBI. The tie facility in Perl allows native data structures (such as arrays and hashes) to represent data that comes from other mechanisms, such as databases and files.

That said, there's a lot of reading you can do on the general subject of database access and management in Perl. There are many full-fledged ORMs available, such as Rose::DB::Object, as well as simpler libraries like DBI for connecting to a DB.

Answer 4

I think this is the solution you are looking for.

sub get_db {
  my ($server,$database,$tbl,$username,$password) = @_;
  my $dbh = DBI->connect("dbi:ODBC:DRIVER={SQL Server};SERVER=$server;DATABASE=$database",$username,$password); 
  die "Error connecting to database: Error $DBI::err - $DBI::errstr\n" unless defined($dbh);
  my $rows = $dbh->selectall_arrayref("select * from $tbl");
  $dbh->disconnect;
  return $rows;
}