Solved

Is it possible to use a MySQL Routine to export a query result into a file?

Posted on 2012-04-06
6
238 Views
Last Modified: 2012-06-22
I want to use MySQL Routine to export the result of a query into a file on Linux.  I'm just wondering if this is possible?  Thanks.

The reason why I want to use MySQL Routine is because I have already had a routine running periodically to performance a data transformation.  I just want this export as part of this process.
0
Comment
Question by:thomaszhwang
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 500 total points
ID: 37818234
Why not just do a "SELECT ..... INTO OUTFILE '/path/to/filename' FROM ... " from within the procedure?  Just make sure the file doesn't exist yet (generate a new filename every time) and the directory is writable by the mysql.
0
 

Author Comment

by:thomaszhwang
ID: 37820140
Actually I hope to overwrite this file, possible?
0
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 500 total points
ID: 37820218
No it's not possible.  It was made that way to prevent accidental overwriting of important files such as /etc/passwd.  Maybe you can call your routine from a shell script that deletes the existing file first. (guess this means you won't be using the event scheduler but a cronjob)
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:thomaszhwang
ID: 37820244
I suppose I can use a variable for the file name, right?

I tried but it looks like it doesn't like variables...  Any idea?
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 37820501
Unfortunately I've tried different things you're right it doesn't seem to accept variables.  But I found a workaround.  Here I generate the filename based on the current time:

mysql> select * from test;
+-----+--------+---------------------+
| id  | data   | last_updated        |
+-----+--------+---------------------+
|  20 | second | 0000-00-00 00:00:00 |
|  30 | third  | 0000-00-00 00:00:00 |
|  40 | first  | 0000-00-00 00:00:00 |
|  50 | second | 0000-00-00 00:00:00 |
|  60 | third  | 0000-00-00 00:00:00 |
| 100 | bleh   | 2012-03-19 10:59:24 |
+-----+--------+---------------------+
6 rows in set (0.00 sec)

mysql> delimiter |
mysql> create procedure test_outfile()
    -> begin
    -> SET @sql_text = 
    ->    CONCAT ('SELECT * into outfile \'/tmp/', DATE_FORMAT( NOW(), '%Y%m%d%H%i%s'),  '.txt\' from test');
    -> 
    -> PREPARE s1 FROM @sql_text;
    -> EXECUTE s1;
    -> DROP PREPARE s1;
    -> end;|
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select now(); call test_outfile();
+---------------------+
| now()               |
+---------------------+
| 2012-04-08 10:36:31 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select now(); call test_outfile();
+---------------------+
| now()               |
+---------------------+
| 2012-04-08 10:36:46 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Open in new window


I included a "select now()" above to check against the files that got created:

$ cd /tmp
$ ls -al *.txt
-rw-rw-rw- 1 mysql mysql 176 2012-04-08 10:36 20120408103631.txt
-rw-rw-rw- 1 mysql mysql 176 2012-04-08 10:36 20120408103646.txt
$ cat 20120408103631.txt
20	second	0000-00-00 00:00:00
30	third	0000-00-00 00:00:00
40	first	0000-00-00 00:00:00
50	second	0000-00-00 00:00:00
60	third	0000-00-00 00:00:00
100	bleh	2012-03-19 10:59:24

Open in new window

0
 

Author Closing Comment

by:thomaszhwang
ID: 37900432
Thanks.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Delete image(s) associated with record(s) 16 46
WordPress  Failed to Import Media 8 64
MySQL - passing table names to a stored procedure 3 49
MySQL 6 48
Creating and Managing Databases with phpMyAdmin in cPanel.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question