List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 1 2012 3:14pm
Subject:Re: How do I use a dynamic filename for an into outfile statement
View as plain text  
On Feb 29, 2012, at 10:43 AM, Ed Patterson wrote:

> Be kind, I am by no means any type of DB expert.
> 
> I would like to eventually move this to a stored procedure but for now I am using the
> \. to fire it off.
> 
> Here is what I have so far (generating a list of machines missing software)
> 
> select last_logon.host_name_short
> from last_logon
> left join mcafee on last_logon.host_name_short = last_logon.host_name_short
> where mcafee.host_name_short is null
> and last_logon.host_name_short like 'w%'
> -- the above works
> into outfile (select concat('Missing-',date_format(now(),'%Y%m%d%H%i'),'txt');
> -- this line breaks it
> 
> The select concat() works from the command line
> I can manually add the file name but would like to automate the process
> Lastly, if it makes a difference, I don't use any graphical tools for DB stuff.
> Inevitably someone says 'click here' :-)
> 
> Thanks for any help
> Ed


-- create statement, assign to user variable
set @s = concat('
select last_logon.host_name_short
from last_logon
left join mcafee on last_logon.host_name_short = last_logon.host_name_short
where mcafee.host_name_short is null
and last_logon.host_name_short like \'w%\'
into outfile \'Missing-',date_format(now(),'%Y%m%d%H%i'),'.txt\'');
-- display so you can verify what it looks like
select @s;
-- prepare statement, execute it, discard it
prepare s from @s;
execute s;
deallocate prepare s;

Note: I added a '.' before 'txt' and a closing quote to the file name.

http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-prepared-statements.html

-- 
Paul DuBois
Oracle Corporation / MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com

Thread
How do I use a dynamic filename for an into outfile statementEd Patterson1 Mar
  • Re: How do I use a dynamic filename for an into outfilestatementhsv1 Mar
  • Re: How do I use a dynamic filename for an into outfile statementJohan De Meersman1 Mar
  • Re: How do I use a dynamic filename for an into outfile statementPaul DuBois1 Mar