Re: How do I execute an SQL file through DBI

I think the $dbh->do() can handle only one command at a time, so if the commands ar eone per line, just reqad the file and end it to the DB one line at a time?

jdtoronto

Comment on Re: How do I execute an SQL file through DBI

Replies are listed 'Best First'.
Re^2: How do I execute an SQL file through DBI by herveus (Prior) on Mar 14, 2006 at 18:35 UTC
Howdy! The problem is one of reading the file one command at a time. If your file is "well formed" (for any useful definition) such that you could slurp the file and split it on a simple regex, this trick can work fine. I had a case where I had the schema in the DATA handle of a module. It went like this: `foreach (creates()) { $dbh->do($_) or die... } sub creates { return split(/^EOC$/m, <<EOS); CREATE TABLE name ( id INTEGER PRIMARY KEY,... ) EOC CREATE TABLE... EOC more create, alter, etc EOS }` [download] The trick is being able to split the SQL file into discrete commands. yours, Michael	[reply] [d/l]

Replies are listed 'Best First'.

Re^2: How do I execute an SQL file through DBI
by herveus (Prior) on Mar 14, 2006 at 18:35 UTC

The problem is one of reading the file *one command* at a time. If your file is "well formed" (for any useful definition) such that you could slurp the file and split it on a simple regex, this trick can work fine.

I had a case where I had the schema in the DATA handle of a module. It went like this:


foreach (creates())
{
    $dbh->do($_) or die...
}

sub creates
{
    return split(/^EOC$/m, <<EOS);
    CREATE TABLE name
    (
        id INTEGER PRIMARY KEY,...
    )
EOC
    CREATE TABLE...
EOC
    more create, alter, etc
EOS
}
[download]

yours,
Michael

[reply]
[d/l]