Generating a text file in MYSQL stored procedure

Question

Hi, I'm new to MySQL, I am trying to create a text file using a stored procedure. I'm currently at the stage where I have a temporary table that contains all of the records that I want to output to a text file. I have the following line at the end of my SP, it works in PHPMYAdmin's query but it does not work when part of a stored procedure the code is as follows:

SELECT * into outfile '../../htdocs/VIP/Temp/temp.txt' from tmp_Menu2;

note that tmp_Menu2 is a table that only includes one field of type VARCHAR(1000)

Any help would be greathly appreciated.

Thank you,

Answer 1

Yes. The user making the call needs file privileges on the host. The text file will be created on the server.

Answer 2

The problem is with your pathname, when you run it from mysql the pathname is relative to the PHP myadmin directory where as when you call the SP from the mysql console, the path is relative to the current working directory.