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

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.

Answer

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.

Advertisement

Answer