Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

How to execute sql scripts via perl

by cnarun86 (Initiate)
on Mar 04, 2009 at 05:41 UTC ( #747991=perlquestion: print w/replies, xml ) Need Help??

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;

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

    -sam

      Thanks for the reply.

      Now I am using the shell command. Its working

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?

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://747991]
Approved by samtregar
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (3)
As of 2022-07-01 07:52 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My most frequent journeys are powered by:









    Results (98 votes). Check out past polls.

    Notices?