0

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 $$
7
  • try to change DECLARE @fil_name, @fil_path STRING; with DECLARE fil_name, fil_path STRING; Commented Jul 8, 2015 at 10:19
  • I tried. But the error missing colon at STRING can't be resolved. Also, as I know there are 2 types of variable in MySQL, procedure (without @) and session-specific variable (with @). The procedure variable would be reinitialized every time running the script. So I would need a session-specific variable here .. Commented Jul 8, 2015 at 10:24
  • @TrungBún: Can you please post exact error stack as thrown and seen Commented Jul 8, 2015 at 10:43
  • Think main the problem is you cannot specify variable as path to output file. Please see example here stackoverflow.com/questions/13548118/… Commented Jul 8, 2015 at 10:49
  • I fixed it. No more errors message appear now. The event is listed when I run query show events. But it doesn't work every 2 seconds ... Please see my update for information Commented Jul 8, 2015 at 10:51

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.