Problem with creating and adding data into a text file in MySQL?

Question

I have created the following procedure that using two different prepared statements, writes some data in a text file with the same name.

PROCEDURE

delimiter !!
drop procedure if exists copyIntoFile !!
create procedure copyIntoFile()

begin
    declare path varchar(255);
    set path = concat("'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/", curdate(), ".txt'");

    set @aux1 = concat("select * from movie_modification where modified = true into outfile ", path, 
                        " fields terminated by ';' lines starting by 'Edit: ' terminated by '\n';");
    prepare stmt1 from @aux1;
    execute stmt1;
    deallocate prepare stmt1;

    set @aux2 = concat("select * from movie_modification where modified = false into outfile ", path, 
                        " fields terminated by ';' lines starting by 'New: ' terminated by '\n';");
    prepare stmt2 from @aux2;
    execute stmt2;
    deallocate prepare stmt2;

    delete from movie_modification;
end !!
delimiter ;

When I executed this procedure, statement aux1 executes correctly and creates the text file and writes the data in it. But when the execution goes to the second statement (aux2), I get the following error:

Error Code: 1086. File 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/2020-04-03.txt' already exists

This error occurs because the file is already being created in statement aux1.

How can I modify then the second statement aux2 knowing the file it's already been created?

How do you want to modify the second statement - do you want to create a different file or append to the existing file? — P.Salmon
– P.Salmon, Commented Apr 3, 2020 at 11:38
TRUNCATE TABLE movie_modification; is probably the fastest way to remove all rows from the table. It's a big, and effective, hammer. — O. Jones
– O. Jones, Commented Apr 3, 2020 at 12:46

RiggsFolly · Accepted Answer · 2020-04-03 12:39:42Z

4

I dont think you can ask MySQL to append to the same file, but you could UNION the 2 queries.

You can add the 'Edit: ' and 'New: ' into the query, therefore allowing you to use a UNION to reduce this to one query.

You may have to change your query to specify all the columns you want a SELECT 'Edit: ',* wont work, but here is a suggestion.

delimiter !!
drop procedure if exists copyIntoFile !!
create procedure copyIntoFile()

begin
    declare path varchar(255);
    set path = concat("'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/", curdate(), ".txt'");

    set @aux = concat("select 'Edit: ', col1,col2 from movie_modification where modified = true
                    UNION ALL
                    select 'New: ', col1,col2 from movie_modification where modified = false
                 into outfile ", path, 
                 " fields terminated by ';' lines terminated by '\n';");

    prepare stmt from @aux;
    execute stmt;

    deallocate prepare stmt;
    delete from movie_modification;
end !!
delimiter ;

edited Apr 3, 2020 at 12:39

answered Apr 3, 2020 at 11:48

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

P.Salmon Over a year ago

Not sure what '' lines starting by 'New: '' does in an outfile but second query is slightly different to first in this regard Perhaps a simple string(s) in the select would suffice.

RiggsFolly Over a year ago

@P.Salmon Ooo I didnt spot that, thanks, will have to think again on this one

RiggsFolly Over a year ago

think I have a solution, sorry took so long, got called away

Akina Over a year ago

@RiggsFolly Even when no duplicated UNION causes the server to perform excess sorting.

RiggsFolly Over a year ago

Ok, will remember that

|

Collectives™ on Stack Overflow

Problem with creating and adding data into a text file in MySQL?

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related