Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked

DBI execute .sql file

by Anonymous Monk
on Jul 08, 2003 at 14:43 UTC ( #272308=perlquestion: print w/replies, xml ) Need Help??

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

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

Replies are listed 'Best First'.
Re: DBI execute .sql file
by hardburn (Abbot) on Jul 08, 2003 at 14:50 UTC

    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

      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.


      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);
Re: DBI execute .sql file
by cfreak (Chaplain) on Jul 08, 2003 at 18:06 UTC

    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!
Re: DBI execute .sql file
by Jenda (Abbot) on Jul 08, 2003 at 16:48 UTC

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

    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

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (2)
As of 2023-10-01 12:24 GMT
Find Nodes?
    Voting Booth?

    No recent polls found