Hi,
I am writing a stored procedure that needs to execute a .sql source file. The MySQL stored procedure must run the .sql script and provide a return value based on whether the .sql file exists, if an error occurs or if it exists and executes without errors. Can anyone provide me with some direction on the proper syntax to use in my stored procedure to call the .sql file? Is it simply CALL myfile.sql?
Thanks, Sid
There are no commands in MySQL language to read a .sql file. The "source" keyword is an internal command of the MySQL command line. If you want to execute one SQL command from a file, you can do the following: 1) read the file into a table, using the LOAD DATA INFILE command (but some security restrictions apply); 2) load the first record from that file into a variable; 3) create a prepared statement from the variable; 4) execute the prepared statement.
The above procedure is fraught with restrictions and problems, however. The first and most notable one is that allowing execution of generic code is a security liability. The second one is that you will have little control on what you are executing.
If you really must, you could use MySQL Proxy (http://launchpad.net/mysql-proxy), which can perform the above operations with much more control and flexibility on each step.