MySQL Bugs: #13870: Allow variable filename for INTO OUTFILE clause

Bug #13870	Allow variable filename for INTO OUTFILE clause
Submitted:	9 Oct 2005 10:06	Modified:	6 Oct 2010 5:53
Reporter:	Roland Bouman	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	5.0.13	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The INTO OUTFILE clause for SELECT statements requires a quoted string literal as filename. It would add a lot of value if a variable could be used to specify the filename. 

Example: When I want to create a stored procedure to export data from a SELECT statement, I'm either forced to hardwire the filename into the SP, or execute the SELECT using the prepared statement syntax (I did not try if that would even work)

I think both are undesirable and cumbersome solutions. A variable filename would remedy this completely.

How to repeat:
-- sample of accepted syntax. works fine.

select *
into   outfile 'D:\\temp\\myoutfile1.txt'
from   department;

-- sample of desired syntax 
set @filename = 'D:\\temp\\myoutfile2.txt';

select *
into   outfile @filename
from   department;

-- alas it fails:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@file
name
from   department' at line 2

-- sample of even more desired syntax

delimiter %%
create procedure p_export(p_filename varchar(255))
begin
select *
into   outfile p_filename
from   department
;
end
%%

-- fails too
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'p_fil
ename
from   department
;
end' at line 4

Suggested fix:
Allow variable filenames for the INTO OUTFILE clause

> I am trying to use the CURRENT_DATE()+0 function
> as a part of my output file's name, which contains
> the statement as following: 
> 
> SELECT field1,field2 INTO OUTFILE 'mytext.txt' 
> FROM myTable WHERE condition1,condition2; 
> 
> Can anybody come up a solution to name my output
> file as 20060213.txt, instead of 'mytext.txt'?

I had posted the above problem in other forum and got this reply below, but it didn't work for me.  I am wondering if quotes are correct…

SET @sql = CONCAT( "SELECT <whatever> INTO
> OUTFILE '", DATE_FORMAT( Now(), '%Y%m%d' ),
> ".txt'" , <remainder of sql_statement>" ) 
> PREPARE stmt FROM @sql;
> EXECUTE stmt;
> DROP PREPARE stmt;
> 

Can anyone help me in building it right?  Thank you!

This is correct working syntax for achieving your goal:

SET @sql_text = 
   CONCAT (
       "SELECT 'text_or_yourquery' into outfile '"
       , DATE_FORMAT( NOW(), '%Y%m%d')
       , ".txt'"
    );

PREPARE s1 FROM @sql_text;
EXECUTE s1;
DROP PREPARE s1;

You can place this inside a procedure and you can parameterize @sql_text, if you want. Unforatunately, as far as I know, reading back the data from the file can be done with SELECT INFILE, but it is not possible to read back any other data, like statements, this way. You can use 'SOURCE', but you cannot use that with PREPARE or a Stored Procedure.

hello! its been almost 3 years now since this feature request has been escalated.

may i know its status please? our team badly needs this functionality with our new project.

cheers!

Thank you for the bug report.

Hi all,

While waiting for the bugfix for this one, I came up with a spaghetti workaround to create an incremental file which would satisfy our requirement for the meantime...

High level process:
-require table to store latest timestamp
   
   CREATE TABLE `pnc_config`.`cfg_timestamp_chk` (
     `last_insert_ts` datetime default NULL
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-continue handler for "duplicate file" error
-variable to hold timestamp difference between current timestamp and the stored last timestamp

Note:
-The application should manage deletion of files upon reading/interpreting its content to be able to produce newer file having the same name

Cheers!
Ash

===========================

DROP PROCEDURE IF EXISTS admin.create_cfg_file;
CREATE PROCEDURE admin.create_cfg_file()
    READS SQL DATA
		COMMENT 'Creates a flat file config.cfg00x to be used by an external program
		         By      : Ash Collado (adcollado@gmail.com
						 Rev Date: 5.15.2008
						 Version : 001
						'
BEGIN
  declare dupfile       int default 0;
  declare ts_difference int default 0; 

	 select TIMESTAMPDIFF(SECOND,  last_insert_ts,now() ) into ts_difference  from pnc_config.cfg_timestamp_chk;
	  
	 begin
   declare continue handler for 1086 set dupfile = 1;
	 if dupfile = 0 and ts_difference > 0 then
		 SELECT now() curr_timestamp_ai INTO  OUTFILE 'config.cfg001'  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
	   update pnc_config.cfg_timestamp_chk set last_insert_ts = now();
	 end if;
	 end;
	 
   begin
	 declare continue handler for 1086 set dupfile = 2;
	 if dupfile = 1 and ts_difference > 0 then
     
	   SELECT now() curr_timestamp_ai INTO  OUTFILE 'config.cfg002'  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
		 update pnc_config.cfg_timestamp_chk set last_insert_ts = now();
	 end if;
	 end;

   begin
	 declare continue handler for 1086 set dupfile = 3;
	 if dupfile = 2 and ts_difference > 0 then
     
	   SELECT now() curr_timestamp_ai INTO  OUTFILE 'config.cfg003'  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
		 update pnc_config.cfg_timestamp_chk set last_insert_ts = now();
	 end if;
	 end;

   begin
	 declare continue handler for 1086 set dupfile = 4;
	 if dupfile = 3 and ts_difference > 0 then
     
	   SELECT now() curr_timestamp_ai INTO  OUTFILE 'config.cfg004'  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
		 update pnc_config.cfg_timestamp_chk set last_insert_ts = now();
	 end if;
	 end;

   begin
	 declare continue handler for 1086 set dupfile = 5;
	 if dupfile = 4 and ts_difference > 0 then
     
	   SELECT now() curr_timestamp_ai INTO  OUTFILE 'config.cfg005'  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
		 update pnc_config.cfg_timestamp_chk set last_insert_ts = now();
	 end if;
	 end;

   begin
	 declare continue handler for 1086 set dupfile = 6;
	 if dupfile = 5 and ts_difference > 0 then
     
	   SELECT now() curr_timestamp_ai INTO  OUTFILE 'config.cfg006'  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
		 update pnc_config.cfg_timestamp_chk set last_insert_ts = now();
	 end if;
	 end;

   begin
	 declare continue handler for 1086 set dupfile = 7;
	 if dupfile = 6 and ts_difference > 0 then
     
	   SELECT now() curr_timestamp_ai INTO  OUTFILE 'config.cfg007'  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
		 update pnc_config.cfg_timestamp_chk set last_insert_ts = now();
	 end if;
	 end;

   begin
	 declare continue handler for 1086 set dupfile = 8;
	 if dupfile = 7 and ts_difference > 0 then
     
	   SELECT now() curr_timestamp_ai INTO  OUTFILE 'config.cfg008'  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
		 update pnc_config.cfg_timestamp_chk set last_insert_ts = now();
	 end if;
	 end;

   begin
	 declare continue handler for 1086 set dupfile = 9;
	 if dupfile = 8 and ts_difference > 0 then
     
	   SELECT now() curr_timestamp_ai INTO  OUTFILE 'config.cfg009'  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
		 update pnc_config.cfg_timestamp_chk set last_insert_ts = now();
	 end if;
	 end;
	 
   begin
	 declare continue handler for 1086 set dupfile = 0;
	 if dupfile = 9 and ts_difference > 0 then
     
	   SELECT now() curr_timestamp_ai INTO  OUTFILE 'config.cfg010'  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
		 update pnc_config.cfg_timestamp_chk set last_insert_ts = now();
	 end if;
	 end;
	 
 
END;

Sorry, i forgot to add this note:

After the code snippet above, you can now execute this on a TRIGGER without hassle

CREATE TRIGGER `admin`.`trg_test_ai` 
BEFORE INSERT ON admin.test FOR EACH ROW
BEGIN
  call create_cfg_file();
END;

Hi,
We were wondering what the status on this feature request and when it will be implemented as this bug was reported over 4 years ago.  It seems to be a relatively easy fix that appears to cause headaches for many, especially when calling inside of a trigger.
Thanks!

Please include this useful feature! Now I'm stuck and I don't know what to do with my trigger..

Yin Swee Weng,

as a workaround, you can either try to generate a unique filename or use a UDF (http://dev.mysql.com/doc/refman/5.1/en/adding-udf.html) to write the file

This seems still to be a problem in mysql 5.7. Any status updates? Can't see any other way of dynamically setting the outfile name (our use case: in a script that gets run periodically on same host and should produce separate outfiles every time).