DBI execute .sql file

[Anonymous Monk]

Does anyone know how to execute a .sql file using prepare()?
I want to do the same thing as:
sqlplus> @file
Thanks

[hardburn]

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);

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

[arthas]

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.

[Mago]

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);

[cfreak]

I don't think the suggestions of slurping in the file and then trying to just call do on it will work. I seem to recall that DBI won't handle multiple statements, which most .sql files would be (though I've only used it with MySQL and PostgreSQL, so that may be a limit of the drivers).

My suggestion would be to go line by line and rebuild the statements, looking for ";" characters that aren't in quotes. I'll try to update this and post some code in a moment, as I'm at work and can't come up with a solution off the top of my head. :)

Lobster Aliens Are attacking the world!

[Jenda]

Did you try to slurp the file into a scalar and then just $dbh->do($sql)?

Jenda
Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.
   -- Rick Osborne

Edit by castaway: Closed small tag in signature