Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    Rhodesia
    Posts
    28

    Unanswered: "SELECT INTO OUTFILE" problem

    Hi

    I am running MYSQL 5.0 ON WINXP pro.

    I have the following procedure:

    DELIMITER $$
    DROP PROCEDURE IF EXISTS LSMW_COST_SWITCH $$
    CREATE PROCEDURE LSMW_COST_SWITCH()
    BEGIN
    SELECT * FROM C003_COSTING_LSMW ORDER BY NG_PART ASC limit 100;
    SELECT * INTO OUTFILE 'E:/Work/Processed Files/test.txt' FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n' FROM C003_COSTING_LSMW ;

    END $$

    DELIMITER ;

    This fails returning the following error message:
    "You have an error in your SQL syntax; check the manual that corresponds to your MYSQL server version for the right syntax to use near '$$ DELIMITER' at line 9

    If I remove the SELECT INTO... CLAUSE the procedure works just fine.

    I have tried the SELECT * INTO OUTFILE 'E:............. as a simple query , and it works just fine.
    Can someone pls help?
    thanks and greets

  2. #2
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    It looks like there are a lot of permissions issues when using SELECT INTO OUTFILE...

    Also, the docs specify that LOAD DATA INFILE cannot be used in stored procedures, but don't mention SELECT INTO OUTFILE.

    But since they are basically counterparts and require the same permissions to execute, my guess would be that you can't use it either.

  3. #3
    Join Date
    Oct 2003
    Location
    Rhodesia
    Posts
    28
    Quote Originally Posted by jfulton
    It looks like there are a lot of permissions issues when using SELECT INTO OUTFILE...

    Also, the docs specify that LOAD DATA INFILE cannot be used in stored procedures, but don't mention SELECT INTO OUTFILE.

    But since they are basically counterparts and require the same permissions to execute, my guess would be that you can't use it either.
    Thanks Jfulton, I did look into the 'Load data infile ' and its complement 'Select into outfile', and though Load.... cannot be embedded in a stored procedure the Select INTO... is treated as a select statement extension and CAN be used in a procedure.
    I did some fixes here and there and got it to work partially
    i.e. Create procedure....
    Begin

    Select ........ FIELDS TERMINATED BY '|' ........

    END

    By trial and error for some silly reason if I had the 'LINES TERMINATED by '\n\r' then I got the error but FIELDS TERMINATED '|' seems to be working okay.

    In anycase you are right this seems to be a permissions issue, which one is the problem.! Maybe this will be of help to some in future.

    thanks for the response.
    greets

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •