unable to select into outfile from mysql stored procedure

Hi Im new to mysql and need to know how to write an output file from mysql stored procedure. I basically want to select some data from a table and write the result to an output file on the server. I tried the below procedure which seems to run without error, but I never see the file written to the server. Im using mysql 5.1 on Windows XP pro.


Delimiter $
create procedure output_file (p1 varchar(1))
begin
select col1, col2, col3  
  from table_t1
where col1= p1
  into outfile 'outfile1.txt';
END$
delimiter ;

When I call the above procedure it runs without error, but no file is generated.
However if I run the procedure a second time it gives an error:
ERROR 1086 (HY000): File 'outfile1.txt' already exists.

I tried running the select into file from the command line outside of the procedure and the same thing happens.

heligerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

UmeshMySQL Principle Technical Support EngineerCommented:
That means file has generated...and when you try next time it refuses to overwrite... Pls check on the server for file... also make sure you provide complete path to the out file..
0
k_murli_krishnaCommented:
Yes, give complete path. Also, when you dont many a time file gets written to a default location in MySQL installation folder. make a search of this file on entire computer or the drive in which MySQL is installed. Since you are using windows, there is not much security in terms of file/folder permissions like in unix, linux etc. Hence, I do not understand why it is refusing to overwrite. Unless there is a setting that all newly created/saved files should be marked by default as read only which again is not the default behaviour of windows whether you are logged in as a normal local user/domain user/local administrator. Hence, once if you find the file check whether it is created as read only.
0
UmeshMySQL Principle Technical Support EngineerCommented:
Also... the syntax should be something.. if you don't provide the outfile path... then by default it will be created on server's data directory and into the database name directory...

Say you have this procedure in test schema.. then when you call without complete path to outfile... file will be created in mysql/data/test/outfile1.txt

SELECT col1,col2,col2 INTO OUTFILE 'outfile1.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

heligerAuthor Commented:
I do not see the file created anywhere on the system. I looked in all the possible folders and I also searched by name and time created. I cannot find the file anywhere on the computer. Below shows eaxctly what happens:

mysql> select * from table_t1;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| A    | B    | C    |
| A    | B    | C    |
| A    | B    | C    |
| A    | B    | C    |
| A    | B    | C    |
| A    | B    | C    |
+------+------+------+
6 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> Delimiter $
mysql> create procedure output_file (p1 varchar(1))
    -> begin
    -> select col1, col2, col3
    ->   from table_t1
    -> where col1= p1
    ->   into outfile 'c:\outfile1.txt';
    -> END$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call output_file ('A');
Query OK, 0 rows affected (0.03 sec)

mysql> call output_file ('A');
ERROR 1086 (HY000): File 'c:outfile1.txt' already exists
mysql>

C:\>dir c:\out*
 Volume in drive C has no label.
 Volume Serial Number is C8AD-1A67

 Directory of c:\

File Not Found

C:\>dir c:\out*.*
 Volume in drive C has no label.
 Volume Serial Number is C8AD-1A67

 Directory of c:\

File Not Found

C:\>


As you can see the file is not there. I also searched the entire computer and the outfile1.txt  is not found. I've tried various directories and including the default but nothing seems to work.
0
UmeshMySQL Principle Technical Support EngineerCommented:
Is MySQL running on local host or remote? file should be created on the machine where MySQL is running...

I tried below code.. and just works fine for me.. I can see file created on C:/
Delimiter $
create procedure output_file (p1 varchar(100))
begin
 
SELECT col1,col2,col2 INTO OUTFILE 'c:/outfile1.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
   from table_t1
  where col1= p1;
 
END$
delimiter ;

Open in new window

0
heligerAuthor Commented:
Everything is running on a single computer Windows XP pro SP3. No other computers are involved. Also I am using version 5.1 community server that I just downloaded and installed. It seem very odd that this does not work.

From the command line I tried doing just this simple test:

mysql> select * from table_t1 into outfile 'c:\simple.out';
Query OK, 6 rows affected (0.05 sec)

However, there is no c:\simple.out. Or any other new file crated anywhere on the computer.
0
UmeshMySQL Principle Technical Support EngineerCommented:
Same configuration here at my end..it works.. bcoz  I'm doing what I have written in comments.. Pls don't use '\' on windows... you are just ignoring my comments..

Also can u post below command outputs.. file should be created on server so you need to have FILE privileges in order to do this.... try with the root user and see how things goes..

Change the user name in grant statement..
mysql>show grants for userName@localhost;
mysql>show grants for root@localhost;
 
 
mysql>select * from table_t1 into outfile 'c:/simple.out';
 
or
 
mysql>SELECT * INTO OUTFILE 'c:/simple.out'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
   from table_t1;
 
or
 
mysql>SELECT * INTO OUTFILE 'c:\\simple.out'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
   from table_t1;

Open in new window

0
UmeshMySQL Principle Technical Support EngineerCommented:
Any success??? Pls update me
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
heligerAuthor Commented:
Thanks Ushastry:

Your comment Pls don't use '\' on windows...  solved my problem. All along I was setting the file up as c:\filename instead of c:/filename.   I dont know why on windows you should use slash instead of back slash (as normal in windows environment), but as long as it works I wont worry about it. Also, I cant explain why it didnt work when I just used file name without c:/ , but again I can write to a file now and that is all that counts.

Thanks
0
UmeshMySQL Principle Technical Support EngineerCommented:
Anytime...

Thanks GOD finally it worked!

As I commented in one of my post, when you don't specify the path to the out file, it actually creates the file in the schema directory.. say your table ''xyz' is in schema 'test'.. and you do outfile without specifying the complete path.. then it should be created in <MySQL_DATA_DIR>/test/outfile*

About the '/' and '\' .. I have suffered a lot during my initial period.. no matter you work on Windows/Unix..always use forward slash '/'
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.