how to pass in sql query input file to a perl dbi sub routine

Question

I like to create a generic perl script that will input sql query from a separate file and use it with perl dbi (subroutine), rather than hardcoding the statement. Can someone show me an example how to do this?

For example I have this in a sub routine:

sub get_val
{
  my $sth = $dbh->prepare(q{SELECT count(*) AS COUNT FROM TEST1) ||
      die ("Can't connect: ".$DBI::errstr."\n");         
  $sth->execute;
  my $row = $sth->fetchrow_hashref;
  $sth->finish;
  return $row->{COUNT};
}

Jonathan Hall · Accepted Answer · 2011-07-16 00:47:52Z

1

This would be the general idea:

$/ = ';';
open FH, "< file.sql";
while (<FH>) {
    $dbh->do($_);
    # or:
    # my $sth = $dbh->prepare($_);
    # $sth->execute();
}
close FH;

Of course this won't necessarily handle comments, or ; characters in quoted strings, etc. But this should head you in the right direction.

Or if you know the file will only contain a single statement:

undef $/;
open FH, "< file.sql";
my $sth = $dbh->prepare(<FH>);
close FH;
$sth->execute();

edited Jul 16, 2011 at 0:47

answered Jul 15, 2011 at 23:31

Jonathan Hall

80.5k19 gold badges162 silver badges206 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cjd143SD Over a year ago

thank you. the statements will be pretty simple and short. i will try this.

Jonathan Hall Over a year ago

I don't understand the specific meaning of your edits, but I expanded my answer. Hopefully it helps?

Collectives™ on Stack Overflow

how to pass in sql query input file to a perl dbi sub routine

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related