When using MySQL in batch mode, you may often find it useful to use the UNIX shell commands along with the SQL queries. This is a demo script showing how you can achieve that:
# Mixing shell commands and SQL queries in batch mode- demo script use test; #INSTALL PLUGIN example SONAME 'ha_example.so'; #Ignore statement system cp /tmp/mysqld.trace logs/init.trace # Shell commands prefixed with a 'system' create table new4(num integer) engine=EXAMPLE; system cp /tmp/mysqld.trace logs/after_create.trace # Shell commands prefixed with a 'system' system diff logs/init.trace logs/after_create.trace # Shell commands prefixed with a 'system'
This script makes use of the 'system' command:
system (\!) Execute a system shell command.Note that this is available only on UNIX systems.
In someways I find mysql quite restricting when trying to mix SQL and command line line operations or re-executing commands.
For example, you cannot construct queries on the fly, would be nice if you could do something like this.
DELIMITER //
CREATE PROCEDURE exec(IN cmd TEXT)
BEGIN
SYSTEM CONCAT("mysql -uroot -e\"", cmd, "\"");
END;
//
DELIMITER ;
Posted by Andrew Johnstone on July 01, 2008 at 12:15 AM IST #
Andrew,
Exactly. That is the problem I am trying to solve now. Basically it would be nice if I could have some way to use VARIABLES in the script so that I could assign them at 'runtime' and hence the same script could act for a variety of needs.
Posted by Amit on July 01, 2008 at 09:49 AM IST #
Anderew, Amit;
I think probably what you guys are looking for is support for external language stored procedures to make system calls.
C, Java, Perl and XML-RPC are planned I shouldn't think that bash as an external language would be that difficult implement too. If this is something you strongly need, I would encourage you to contact the project authors and find out how you can contribute.
See: http://forge.mysql.com/wiki/ProjectPage_External_Language_Stored_Procedures
Posted by Matthew Montgomery on July 05, 2008 at 06:49 PM IST #
Using "system" command is really interesting. I have also learnt -vvv (output sql query) and --force (ignore errors) from the page that you have linked in this post. Thanks a lot.
Posted by Shantanu Oak on July 07, 2008 at 03:32 PM IST #
Matthew,
I could get a partial solution to my problem by following a somewhat indirect workaround. I have detailed my soln. here at http://blogs.sun.com/amitsaha/entry/generating_on_the_fly_sql - "Generating on-the-fly SQL scripts for batch mode"
Your link is interesting and is a probable solution in cases when one needs to call external languages from Stored procedures.
Thanks for the heads up.
Posted by Amit on July 07, 2008 at 06:39 PM IST #
Shantanu,
Great that you found it useful.
Posted by Amit on July 07, 2008 at 06:41 PM IST #