Can someone explain why MySQL workbench complains about Syntax error at
DECLARE @fil_name, @fil_path STRING; 2 errors: unexpected '@fil_name' (at text suffix) and at STRING (missing colon)
SELECT * FROM gumcad INTO OUTFILE @fil_path FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'; error at @fil_path: unexpected '@fil_path' (at text suffix)
DELIMITER //
CREATE EVENT `Export` ON SCHEDULE EVERY 2 SECOND
ON COMPLETION PRESERVE
ENABLE
DO BEGIN
DECLARE @fil_name, @fil_path STRING;
SET @fil_name=CONCAT('GUMCAD-', DATE_FORMAT(now(), '%d%m%y'));
SET @fil_path=CONCAT('C:/app/reports/gumcad/', @fil_name, '.csv'));
SELECT * FROM gumcad INTO OUTFILE @fil_path FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
INSERT INTO imported_reports (imr_est_id, imr_type, imr_create_date, imr_created, imr_record_status, imr_file_path, imr_file_name, imr_created_by)
VALUES (2, GUMCAD, CURDATE(), NOW(), 'approved', @fil_path, @fil_name, 'SHEP');
END
DELIMITER;
EDIT
i just figured out that when using @var I don't have to use DECLARE. This is my new script:
DELIMITER //
CREATE EVENT `Export` ON SCHEDULE EVERY 2 SECOND
ON COMPLETION PRESERVE
ENABLE
DO BEGIN
SET @fil_name=CONCAT('GUMCAD-', DATE_FORMAT(now(), '%d%m%y'), '.csv');
SET @fil_path='C:/app/reports/gumcad/';
SET @export_path=CONCAT(@fil_path, @fil_name);
SELECT * FROM gumcad INTO OUTFILE @export_path FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
INSERT INTO imported_reports (imr_est_id, imr_type, imr_create_date, imr_created, imr_record_status, imr_file_path, imr_file_name, imr_created_by)
VALUES (2, GUMCAD, CURDATE(), NOW(), 'approved', @fil_path, @fil_name, 'SHEP');
END
DELIMITER;
But then I have an error at @export_path (unexpected '@export_path' (at text suffix)), Can someone explain please?
NEW UPDATE No more error message at the moment, I was able to run the script but one problem now is the script seem not to run every 2 seconds.
Please take a look at my revised script, I'm using MySQL 5.5:
DELIMITER $$
CREATE EVENT `Export` ON SCHEDULE EVERY 2 SECOND
ON COMPLETION PRESERVE
ENABLE
DO BEGIN
DECLARE fil_name varchar(200);
DECLARE export_path varchar(200);
DECLARE fil_path varchar(200);
SET fil_name=CONCAT('GUMCAD-', DATE_FORMAT(now(), '%d%m%y'), '.csv');
SET fil_path='C:/cellma/reports/gumcad/';
SET export_path=CONCAT(fil_path, fil_name);
SELECT * FROM gumcad INTO OUTFILE "export_path" FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
INSERT INTO imported_reports (imr_est_id, imr_type, imr_create_date, imr_created, imr_record_status, imr_file_path, imr_file_name, imr_created_by)
VALUES (2, GUMCAD, CURDATE(), NOW(), 'approved', @fil_path, @fil_name, 'SHEP');
END $$
DECLARE @fil_name, @fil_path STRING;withDECLARE fil_name, fil_path STRING;show events. But it doesn't work every 2 seconds ... Please see my update for information