Limbic~Region has asked for the wisdom of the Perl Monks concerning the following question:

All,
If possible, I would like to do the following through DBI: 
# Assume an established DB connection
$dbh->do('commands.sql');

[download]
Where 'commands.sql' is a series of commands such as creating and dropping tables. I asked this question in the CB and the three main response I received were: I really want to avoid the command line if possible. Response 3 is intriguing but I don't have the expertise the person making the suggestion does to avoid the shell. I appreciate any and all input on this.

Cheers - L~R

Replies are listed 'Best First'.
Re: How do I execute an SQL file through DBI
by jZed (Prior) on Mar 14, 2006 at 18:32 UTC
    Here's one way: 
    # ...
$dbh->{RaiseError}=1;
$dbh->{PrintError}=0;
# ...
for my $stmt( split /;\n+/, join('',<FH>) ){
    my $sth = $dbh->prepare($stmt);
    $sth->execute;
    fetch_and_display($sth) if $sth->{NUM_OF_FIELDS}
}

    [download]
    NUM_OF_FIELDS should reliably mark statements like SELECT that return fetchable data so this does a bit more than simply looping over $dbh->do().

    For more complex stuff I do things like use SQL comments to give names to the statements and/or include a trailing square-bracket list of bind values before the semicolon for placeholder statements 

    -- get_customer_name
SELECT name FROM customer WHERE id = ? [47];

    [download]

    You can use DBI::Shell in a program rather than a shell (I've done it, it required a minor subclass IIRC). At the moment DBI::Shell AFAIK uses something very similar to what I've shown above so you won't really gain anything that way.
[reply]
[d/l]
[select]
Re: How do I execute an SQL file through DBI
by leocharre (Priest) on Mar 14, 2006 at 18:13 UTC

    WARNING:I'm no expert with mysql, but I use it a lot.

    Have you actually tried mysql app ? it's very slick and easy.

    Simple one two three.. slurp the file with your commands.. then do 

    $handle->prepare($allyourcommands); 
$handle->execute();
#that will do it.

    [download]

    For example; I have a txt file with commands for creating a table and place some records:

    create table usertypes (    
    usertype varchar(5) not null, 
    usertype_label varchar(25) default 'user',
    usertype_child_create  TINYINT(1) DEFAULT 0, 
    usertype_child_assign_to_file TINYINT(1) DEFAULT 0, 
    usertype_child_assign_to_dir TINYINT(1) DEFAULT 0,    
    usertype_child_share TINYINT(1) DEFAULT 0,
    usertype_child_toggle_live TINYINT(1) DEFAULT 0,
    usertype_child_delete TINYINT(1) DEFAULT 0, 
    usertype_child_admin_all TINYINT(1) DEFAULT 0,
    
    usertype_file_view_all TINYINT(1) DEFAULT 0, 
    usertype_file_download TINYINT(1) DEFAULT 0, 
    usertype_file_move TINYINT(1) DEFAULT 0, 
    usertype_file_rename TINYINT(1) DEFAULT 0, 
    
    usertype_dir_recurse TINYINT(1) DEFAULT 0, 
    usertype_dir_create TINYINT(1) DEFAULT 0, 
    usertype_dir_file_upload TINYINT(1) DEFAULT 0,
    usertype_dir_incoming_file_upload TINYINT(1) DEFAULT 1, 
    usertype_dir_rename TINYINT(1) DEFAULT 0,
    
    usertype_edit_description TINYINT(1) DEFAULT 0, 
    usertype_note TINYINT(1) DEFAULT 0, 
    usertype_scan_for_new_files TINYINT(1) DEFAULT 0, 
    usertype_usertypes_edit TINYINT(1) DEFAULT 0, 
    usertype_view_all_sessions TINYINT(1) DEFAULT 0,
    row_last_modify integer(10),    
    PRIMARY KEY (usertype)
);

INSERT INTO usertypes (
usertype, 
usertype_label,
usertype_child_create, 
usertype_child_assign_to_dir,
usertype_child_toggle_live, 
usertype_child_delete,
usertype_child_admin_all,
usertype_file_view_all,
usertype_dir_recurse,
usertype_dir_rename,
usertype_file_move
) values ('a','admin',1,1,1,1,1,1,1,1,1);

INSERT INTO usertypes (
usertype,
usertype_label,
usertype_child_create,
usertype_child_assign_to_file,
usertype_child_toggle_live,
usertype_child_delete,
usertype_file_view_all,
usertype_edit_description,
usertype_file_download,
usertype_child_admin_all,
usertype_file_rename,
usertype_dir_rename,
usertype_file_move
) values ('m','manager',1,1,1,1,1,1,1,1,1,1,1);

INSERT INTO usertypes (usertype, usertype_label, usertype_file_downloa
+d)
    values ('u','user',1);

    [download]

    I can just cut and paste that into the mysql prompt or feed it through the handle.

[reply]
[d/l]
[select]
Re: How do I execute an SQL file through DBI
by jdtoronto (Prior) on Mar 14, 2006 at 18:12 UTC
    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

[reply]
      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]

Back to Seekers of Perl Wisdom