Re: 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

Comment on Re: DBI execute .sql file Download Code

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]