Could someone help me with what is wrong with the below please?
DELIMITER $$
CREATE DEFINER=`user1`@`localhost` PROCEDURE `Local_sp_ExtractPOI`()
BEGIN
SET SESSION group_concat_max_len = 1000000;
SET @OutputPath := 'C:/stuff/folder/nextfolder/files/POIExtract/';
SET @filename := date_format(now(), '%d%m%y_%H%m%s.txt');
SET @fullOutputPath := CONCAT(@OutputPath, @filename);
set @q1 := concat("SELECT group_concat(`NHS` separator '') INTO OUTFILE ",@fullOutputPath,
" FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\'
FROM person WHERE `active` = -1 AND `NHS` IS NOT NULL AND `NHS` REGEXP [0123456789]{10}
ORDER BY 1");
prepare s1 from @q1;
execute s1; deallocate prepare s1;
END$$
DELIMITER ;
I know i can write to the path because this works:
SELECT oid INTO OUTFILE "C:/stuff/folder/nextfolder/files/POIExtract/varmydata.txt"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\'
LINES TERMINATED BY '\n'
FROM person limit 1;
However, when i wrap in a procedure and use the variables and concat(), it doesn't create the outfile.
I have tried multiple ways of formatting the filepath but still no dice. When i try to call it i get Error Code: 1064. You have an error in your SQL syntax...
Is it something around using REGEXP this way that i am not understanding?
I intend to call it using a scheduled event.
TYIA,
Mike
Akina was right. Modified to this:
set @q1 := concat("SELECT group_concat(`NHS` separator '') INTO OUTFILE ", " '", @fullOutputPath, "' ", " FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\' FROM p_person WHERE `active` = -1 AND `NHS` IS NOT NULL AND `NHS` REGEXP", " '[0123456789]{10}' ", "ORDER BY 1");