Solved

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

Posted on 2012-04-06
6
239 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…

705 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