Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

runrig (3385)

runrig
  dougwNO@SPAMcpan.org

Just another perl hacker somewhere near Disneyland

I have this homenode [perlmonks.org] of little consequence on Perl Monks [perlmonks.org] that you probably have no interest in whatsoever.

I also have some modules [cpan.org] on CPAN [cpan.org] some of which are marginally [cpan.org] more [cpan.org] useful [cpan.org] than others.

Journal of runrig (3385)

Tuesday September 02, 2003
11:49 AM

DSN-less tip of the day

[ #14443 ]
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 = <<EOT;
DRIVER={INFORMIX 3.32 32 BIT}
DATABASE=/path/to/database
HOST=hostname
SRVR=Informix
SERV=sqlexec
PR O=sesoctcp
OPT=
EOT

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: $@";
  <>;
}

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.