Execute SQL file in Perl

Question

We have a Perl script which runs a SQL and puts data in the table. Now instead of supplying a single SQL statement, we want to pass bunch of them putting them together in a .sql file. We know that our program will fail because it expects a single SQL statement, not s bunch of them (that too from a .sql file). How do we make it work with a .sql file (having multiple INSERT statements?). We are using the DBI package.

A small snippet of code:

$sth = $dbh->prepare("/home/user1/tools/mytest.sql");
$sth->execute || warn "Couldn't execute statement";
$sth->finish();

Also see stackoverflow.com/questions/945584/… stackoverflow.com/questions/1232950/… — daxim
– daxim, Commented Nov 18, 2010 at 22:20

Miller · Accepted Answer · 2014-08-29 06:30:01Z

7

There is a sort of workaround for DDL. You need to slurp SQL file first and then enclose it's contents into BEGIN ... END; keywords. Like:

sub exec_sql_file {
    my ($dbh, $file) = @_;

    my $sql = do {
        open my $fh, '<', $file or die "Can't open $file: $!";
        local $/;
        <$fh>
    };

    $dbh->do("BEGIN $sql END;");
}

This subroutine allows to run DDL (SQL) scripts with multiple statements inside (e.g. database dumps).

edited Aug 29, 2014 at 6:30

Miller

35.3k4 gold badges42 silver badges61 bronze badges

answered Apr 23, 2014 at 13:35

Elvenfighter

2913 silver badges4 bronze badges

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

Comments

MattSizzle · Accepted Answer · 2017-05-26 19:48:20Z

6

Not exactly sure what you want...

Once you create a DBI object, you can use it over and over again. Here I'm reading SQL statement after SQL statement from a file and processing each and every one in order:

use DBI;

my $sqlFile = "/home/user1/tools/mytest.sql"

my $dbh = DBI::Connect->new($connect, $user, $password)
    or die("Can't access db");

# Open the file that contains the various SQL statements
# Assuming one SQL statement per line

open (SQL, "$sqlFile")
    or die("Can't open file $sqlFile for reading");

# Loop though the SQL file and execute each and every one.
while (my $sqlStatement = <SQL>) {
   $sth = dbi->prepare($sqlStatement)
      or die("Can't prepare $sqlStatement");

   $sth->execute()
      or die("Can't execute $sqlStatement");
}

Notice that I'm putting the SQL statement in the prepare and not the file name that contains the SQL statement. Could that be your problem?

edited May 26, 2017 at 19:48

MattSizzle

3,1752 gold badges25 silver badges43 bronze badges

answered Nov 18, 2010 at 23:42

David W.

107k40 gold badges224 silver badges349 bronze badges

6 Comments

Piyush Over a year ago

here i m calling dq.sql file in perl script in which i m passing variable like $patch_name ,$svn_url, $ftp_path which is defined in perl file but db.sql file is not accessing those variable? pls help me out asap.

$sqlfile = "C:/BuildScript/YellowBox_Script/YellowBox_Core_Script/db.sql";open(SQL, "$sqlfile") or die("Can't open file $sqlFile for reading");  while ($sqlStatement = <SQL>)  {    $sth = $dbh->prepare($sqlStatement)      or die ("Can't prepare $sqlStatement");     $sth->execute()       or die ("Can't execute $sqlStatement"); }

David W. Over a year ago

@picnic4u: Can you create a question instead of a comment? Others will see it, and can help you.

Brad Mace Over a year ago

Unless I'm missing something this assumes one statement per line, which is hardly guaranteed.

David W. Over a year ago

@BradMace You're correct, and I've stated that in my comments. They didn't give me the format of the file, so I took the simplest arrangement. The point of my answer is that they needed to loop through the file, and do a prepare and execute of each statement.

user2647888 Over a year ago

Hi David W i have a similar doubt can you pls answer my question in the link below ..

|

Cfreak · Accepted Answer · 2010-11-18 16:49:13Z

4

You don't need perl for this at all. Just use the mysql command line client:

mysql -h [hostname] -u[username] -p[password] [database name] < /home/user1/tools/mytest.sql

replace the [variables] with your information.

Note no space after -u or -p. If your mysql server is running on the same machine you can omit -h[hostname] (it defaults to localhost)

answered Nov 18, 2010 at 16:49

Cfreak

19.4k6 gold badges52 silver badges64 bronze badges

1 Comment

t0mcat Over a year ago

Hi Cfreak, actually Its a script that doesnt bunch of other stuff too, so we need the modification is this script. Thanks for the solution though.

Miller · Accepted Answer · 2014-08-29 06:27:59Z

1

Here is how I've done it. In my case I dont assume one SQL per line and I assume, my example is a bit better :)

sub get_sql_from_file {
    open my $fh, '<', shift or die "Can't open SQL File for reading: $!";
    local $/;
    return <$fh>;
};

my $SQL = get_sql_from_file("SQL/file_which_holds_sql_statements.sql");
my $sth1 = $dbh1->prepare($SQL);
$sth1->execute();

edited Aug 29, 2014 at 6:27

Miller

35.3k4 gold badges42 silver badges61 bronze badges

answered Feb 10, 2012 at 14:39

Nickolay Komar

3401 silver badge8 bronze badges

6 Comments

Dave Cross Over a year ago

Why wouldn't get_sql_from_file return the SQL, rather than setting a global variable? Global variables are bad.

Nickolay Komar Over a year ago

It could return the SQL, but why do you think that using $SQL variable is bad in that example? Can you explain it? did you mean that using of global variables is a bad idea in general?

Dave Cross Over a year ago

Global variables are, in general, a bad idea. Your functions will be easier to reuse if they are self-contained and don't assume the existence of external variables.

Nickolay Komar Over a year ago

ok, thanks. I haven't been writting on perl at least for last 3 years and I'm currently trying to remember its concepts. Thanks for your comments

Dave Cross Over a year ago

This is not a Perl concept. It's just programming practice - no matter which language you are using.

|

Collectives™ on Stack Overflow

Execute SQL file in Perl

4 Answers 4

Comments

6 Comments

1 Comment

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

6 Comments

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related