0

I want to export query result into .txt file

The path of .txt file and query is dynamic

 DELIMITER //
 create procedure test(filepath text , _query text)
 BEGIN
 SET @sql = CONCAT(_query, ' into OUTFILE ' ,filepath ); 
  prepare s from @sql;
   EXECUTE s;
  DEALLOCATE PREPARE s;
  END // 
  DELIMITER ;

When call procedure test it shows me:

CALL  test1('C:/Users/Tajamul Hussain/Desktop/file database/test.txt ','select firstname from t1 where id =1');

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'C:/Users/Tajamul Hussain/Desktop/file database/test.txt' at line 1
  • I removed the sql-server tag as your question has nothing to do with Microsoft SQL Server. – Dan Guzman Feb 15 at 12:02
0

You need to embrace the filepath in quotes. This works for me (tested on MariaDB 10.4, but should work on older versions as well):

DELIMITER //
CREATE PROCEDURE test(filepath text, _query text)
BEGIN
  DECLARE stmt TEXT;
  SET stmt := CONCAT(_query, ' into OUTFILE \'' , filepath, '\''); 
  PREPARE s FROM stmt;
  EXECUTE s;
  DEALLOCATE PREPARE s;
END // 
DELIMITER ;

By the way, it's not good practice to use user-defined variables inside stored programs, like @sql in your procedure, because they retain their values between calls. (I think it will be OK in your procedure, though, unless you use @sql elsewhere.) DECLARE your variables instead.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.