http://qs321.pair.com?node_id=747991

cnarun86 has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,
I am a newbie in perl. I connect my mysql database to perl via DBI. Now I am trying to load .sql files via perl. Is it possible using DBI or is there any other way to do this. Please bless me.

Thanks in advance.

Arun Kumar C.N

Replies are listed 'Best First'.
Re: How to execute sql scripts via perl
by samtregar (Abbot) on Mar 04, 2009 at 05:55 UTC
    It's possible, but not usually worth the effort. If you want to do it, what you need to do is read in the SQL file and split up each statement. Pass each statement to $dbh->do(). Something like (untested): 

      # read foo.sql into memory
  open my $fh, "<", "foo.sql" or die $!;
  my $sql = do { local $/; <$fh> };

  # split on ; at the end of a line
  my @commands = split /;\s*\n/, $sql;

  # run each command
  $dbh->do($_) for @commands;

    [download]

    It's not perfect - it will break if you have a comment that ends in a ";" for example. Usually it's better to just shell out to the MySQL shell to load a whole SQL file: 

      system("mysql -hhost -uname -ppass db < foo.sql") == 0
    or die "Failed: $?";

    [download]

    -sam

[reply]
[d/l]
[select]
      Thanks for the reply.

      Now I am using the shell command. Its working

[reply]
Re: How to execute sql scripts via perl
by bichonfrise74 (Vicar) on Mar 04, 2009 at 06:43 UTC
    What kind of database are you using? Is it MySQL or Postgres?
[reply]

Back to Seekers of Perl Wisdom