DSN-less tip of the day

runrig on 2003-09-02T16:49:12

I've always thought it was next to impossible to figure out what arguments you need to make a dsnless ODBC connection, when suddenly the obvious occurred to me...create a file DSN and copy and paste from that. So, e.g., for a (standard engine) Informix database, it's just:

my $dsn = <
chomp ( $dsn = join(";", split "\n", $dsn));

my $dbh = DBI->connect("dbi:ODBC:$dsn", $username, $passwd, {
  PrintError => 0,
  RaiseError => 1,
  ChopBlanks => 1,
  AutoCommit => 1,
});

Of course, there is a certain redundancy in having to create a DSN in order to not use it (but it makes for a good template to substitute other database names into) :-) I've also decided that rather than using Windoze Query-tool(s) to do arbitrary SQL in, and having to mess with scrollbars to see the 89th field across or wait for 1000's of lines of output to to see the first few lines, it's more convenient to write a template DBI/DBD script, which dumps output to a file, then use vim to look at the file. If I knew vim a little better, I could probably do it all within vim. Here's what I'm using for now:

my @sql = ( {
  STMT => qq{
select *
from table_name
},
  ARGS => [],
  LIMIT => 0,
},
);

chomp ( $dsn = join(";", split "\n", $dsn));

eval {
my $dbh = DBI->connect("dbi:ODBC:$dsn", $username, $passwd, {
  PrintError => 0,
  RaiseError => 1,
  ChopBlanks => 1,
  AutoCommit => 1,
});

open(FH, ">sqltmp.out") or die "Acck: $!";

for my $sql (@sql) {
  my $sth = $dbh->prepare($sql->{STMT});
  $sth->execute(@{$sql->{ARGS}});
  my @names = @{$sth->{NAME}};
  my $cnt;
  while (my $row = $sth->fetchrow_hashref) {
    no warnings 'uninitialized';
    print FH "[$_][$row->{$_}]\n" for @names;
    print FH "----------\n";
    last if $sql->{LIMIT} and ++$cnt >= $sql->{LIMIT};
  }
}
close FH;
$dbh->disconnect;
};

if ($@) {
  print "Error: $@";
  <>;
}