Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I execute the following query, it works:

SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS' INTO OUTFILE 'D:/stats.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

But when I call this query from the Windows command prompt or from a batch file using the following command:

"%MBIN%\mysql.exe" -u"%MUSER%" -p"%MPASS%" -h "%MHOST%" -e "SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS' INTO OUTFILE 'D:/Stats.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';"

it does not run. Here's what happens:

C:\Documents and Settings\188931>"%MBIN%\mysql.exe" -u"%MUSER%" -p"%MPASS%" -h "
%MHOST%" -e "SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS' INTO OUTFILE 'foldrname/
orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';"
C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe  Ver 14.14 Distrib 5.5.16,
 for Win32 (x86)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Usage: C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe [OPTIONS] [database
]
  -?, --help          Display this help and exit.
  -I, --help          Synonym for -?
  --auto-rehash       Enable automatic rehashing. One doesn't need to use
                      'rehash' to get table and field completion, but startup
                      and reconnecting may take a longer time. Disable with
                      --disable-auto-rehash.
                      (Defaults to on; use --skip-auto-rehash to disable.)
  -A, --no-auto-rehash
                      No automatic rehashing. One has to use 'rehash' to get
                      table and field completion. This gives a quicker start of
                      mysql and disables rehashing on reconnect.
  --auto-vertical-output
                      Automatically switch to vertical output mode if the
                      result is wider than the terminal width.
  -B, --batch         Don't use history file. Disable interactive behavior.
                      (Enables --silent.)

Another thing I noted is, when I run the following:

"%MBIN%\mysql.exe" -u"%MUSER%" -p"%MPASS%" -h "%MHOST%" -e "SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS';"

from the command prompt or a batch file, it does execute. (Note - I removed the csv export).

So it seems a combination of executing a query from batch file (or Windows command prompt) which is having a OUTFILE to csv is not working.

Could any body kindly help on this?

share|improve this question
    
I bet that the double quote is the culprit: it terminates the string at ENCLOSED BY ', and the rest derails the whole thing. –  dezso Oct 26 '12 at 6:56
    
@dezso thanks for the answer. –  srj Oct 26 '12 at 8:37
    
@dezso, flip that to an answer so it doesn't look like srj is waiting for an answer (and you can get the credit you deserve). –  Alain Collins Nov 6 '12 at 0:47
    
@AlainCollins done –  dezso Nov 6 '12 at 10:28

1 Answer 1

I bet that the double quote is the culprit: it terminates the string at ENCLOSED BY ', and the rest derails the whole thing. The solution is very simple: just escape the double quote in your SQL string:

... ENCLOSED BY '\"' ...

(I have to tell that on my local Windows box and psql (instead of mysql) client I don't have to escape the double quote.)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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