Executing a file of SQL commands against MySQL server

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

I am writing a Perl script which downloads and installs the MySQL sakila database. I need it run under Strawberry Perl 5.10. The part I am having problems with is executing a file of SQL against MySQL:

for my $sql qw(sakila-schema.sql sakila-data.sql) {
    my $file     = "sakila-db/$sql";
    my $contents = read_file($file);

    print "\t$file\n";

    open my $fh, '|-', 'mysql', @opt;

    print $fh $contents;
}
[download]

The error message is

List form of pipe open not implemented at (eval 19) line 109
        main::__ANON__('GLOB(0x2f641ec)', '|-', 'mysql',
    '--user=root', '--password=passw0rd', '--host=localhost',
    '--port=3306') called at etc/install_sakila.pl line 56
[download]

Now, it is not as simple as changing the input record separator to semicolon because certain sections of the file change the SQL delimiter to $$:

DELIMITER $$

CREATE FUNCTION inventory_held_by_customer(p_inventory_id INT) RETURNS
+ INT
READS SQL DATA
BEGIN
  DECLARE v_customer_id INT;
  DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;

  SELECT customer_id INTO v_customer_id
  FROM rental
  WHERE return_date IS NULL
  AND inventory_id = p_inventory_id;

  RETURN v_customer_id;
END $$

DELIMITER ;
[download]

Call mysql SOURCE would've been awesome, but that's only in the MySQL client, not in the server. So it looks like my options are:

figure out some way to get pipes to the mysql client working
figure out some way to emulate source in a Perl module

Comment on Executing a file of SQL commands against MySQL server Select or Download Code

Replies are listed 'Best First'.
Re: Executing a file of SQL commands against MySQL server by Gangabass (Vicar) on Aug 18, 2011 at 02:50 UTC
Why don't you want just make a `system()` with this command `mysql -u username -p password database < sql_file`?	[reply] [d/l] [select]
Re^2: Executing a file of SQL commands against MySQL server by Neighbour (Friar) on Aug 18, 2011 at 08:35 UTC
Or, alternatively, use the -e option of the mysql client to parse the sql_file: `mysql -u username -p password -e 'source sql_file' database` This will make the mysql-client read the file instead of piping it in.	[reply] [d/l]
Re^2: Executing a file of SQL commands against MySQL server by metaperl (Curate) on Aug 18, 2011 at 17:50 UTC
Yes, that worked. Thanks. The mantra of every experienced web application developer is the same: thou shalt separate business logic from display. Ironically, almost all template engines allow violation of this separation principle, which is the very impetus for HTML template engine development. -- Terence Parr, "Enforcing Strict Model View Separation in Template Engines"	[reply]
Re: Executing a file of SQL commands against MySQL server by juster (Friar) on Aug 18, 2011 at 14:55 UTC
The other comments give cleaner solutions to your overall problem but in response to your error: `List form of pipe open not implemented at (eval 19) line 109 main::__ANON__('GLOB(0x2f641ec)', '\|-', 'mysql', '--user=root', '--password=passw0rd', '--host=localhost', '--port=3306') called at etc/install_sakila.pl line 56` [download] It seems that the "list form of pipe" is not implemented on Windows. Quoth the `perldoc -f open` (emphasis mine): ... In the form of pipe opens taking three or more arguments, if LIST is specified (extra arguments after the command name) then LIST becomes arguments to the command invoked if the platform supports it. ... So I suppose you could use `join` to avoid that problem.	[reply] [d/l] [select]