How to create a .txt file and insert data into it in SQL?

Question

I'm learning about triggers and events and I have the following issue:

I have created a trigger that inserts data in a table called updated_movie when an update is made in table movie.

Also, I want to create an event that executes every day at 11:30am that creates a text file with some data from the table updated_movie. I have made this so far:

delimiter !!
drop event if exists createFile !!
create event createFile on schedule at "11:30"

do begin

    declare path varchar(255) default "/Users/Shared/BDD/L19/";
    declare nameFile varchar(255) default curdate();
    declare done int default 0;
    declare t varchar(255);

    -- creation of the text file?

    declare c cursor for select title from updated_movie;
    declare continue handler for not found set done = 1;

    open c;

    l:loop
        fetch c into t;

        if done = 1 then
            leave l;
        end if;

        call copyIntoFile(t, nameFile);

    end loop l;

end!!

delimiter ;

This is the event I want to be executed every day. How can I create a text file into the path declared in the event with the same file name as the declared variable nameFile?

Also, procedure copyIntoFile looks like this so far:

delimiter !!
drop procedure if exists copyIntoFile !!
create procedure copyIntoFile(in str varchar(255), in fileName varchar(255)

begin

    -- Copy into the text file?

end !!

delimiter ;

How can I make it so I can insert some data into the text field?

In case that you are wondering, table updated_movie just have one varchar(255) field.

Hey this could be a duplicate see https://stackoverflow.com/a/21253800/2408013 or better here https://stackoverflow.com/a/22342839/2408013 — creep3007, Apr 01 '20 at 13:16
Does this answer your question? [mysql stored procedure with INTO OUTFILE](https://stackoverflow.com/questions/22265517/mysql-stored-procedure-with-into-outfile) — creep3007, Apr 01 '20 at 13:17

score 0 · Accepted Answer · answered Apr 01 '20 at 13:49

0

CREATE EVENT createFile 
ON SCHEDULE 
    EVERY 1 DAY
    STARTS CURRENT_DATE + INTERVAL '11:30' HOUR_MINUTE
ENABLE
DO
SELECT *
    INTO OUTFILE 'drive:\\folder\\filename.ext'
    FROM updated_movie
    WHERE created_at >= NOW() - INTERVAL 1 DAY;

Modify conditions, output expressions, add export specifications if needed.

Check secure_file_priv setting and related ones, and justify destination forder accordingly. FILE privilege needed.

PS. BEGIN-END, DELIMITER, etc. - are excess.

answered Apr 01 '20 at 13:49

Akina

39,301
5
14
25

Thanks! It may work. Now I have to figure out how to change `secure_file_priv` setting. I am using Mac and MySQLWorkbench, any help is welcome! – user157629 Apr 01 '20 at 14:56
*I have to figure out how to change secure_file_priv setting* Edit INI file. Restart MySQL. – Akina Apr 01 '20 at 15:49

How to create a .txt file and insert data into it in SQL?

1 Answers1