Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

Re: DBI execute .sql file

by hardburn (Abbot)
on Jul 08, 2003 at 14:50 UTC ( [id://272311]=note: print w/replies, xml ) Need Help??


in reply to DBI execute .sql file

Your specific DBD driver might have a method to do it for you. Barring that, you could try:

my $sql_file = '/path/to/file.sql';
my $dbh; # DBI connection initilized elsewhere

local $/ = ';';
open(SQL, '<', $sql_file) or die "Can't open $sql_file: $!\n";
while(my $line = <SQL>) {
    $dbh->do($line) 
        or warn "Can't execute statement in $sql_file, line $.: " . $d
+bh->errstr;
}
close(SQL);

[download]

Though there might be some special cases where the above will fail.

----
I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
-- Schemer

Note: All code is untested, unless otherwise stated

Replies are listed 'Best First'.
Re: Re: DBI execute .sql file
by arthas (Hermit) on Jul 08, 2003 at 15:53 UTC

    I think this is going to fail if there is a ; in a string inside a SQL statement. You should probably set $/ depending on the structure of your SQL file.

    Michele.

[reply]
Re: Re: DBI execute .sql file
by Mago (Parson) on Jul 08, 2003 at 20:07 UTC
    Mago 
    my $sql_file = '/path/to/file.sql';
my $dbh; # DBI connection initilized elsewhere
my $query = ' ';
open (SQL, '<', $sql_file) or die "Can't open $sql_file: $!\n";
while (my $line = <SQL>) {
      chomp $line;
      $line = join(' ',split(' ',$line));
      if ((substr($line,0,2) ne '--') and (substr($line,0,3) ne 'REM')
+) {
         if (substr($line,- 1,1) eq ';') {
              $query .= ' ' . substr($line,0,length($line) -1);
            $dbh->do($query) or warn "Can't execute statement in $sql_
+file, line $.: " . $dbh->errstr;
            $query = ' ';
          } else {
                 $query .= ' ' . $line;    
          }
      }
}
close(SQL);

    [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Node Status?
node history
Node Type: note [id://272311]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others avoiding work at the Monastery: (7)
As of 2024-04-23 07:55 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found