2

I have managed to come up wit the below script for exporting some files. This needs to be done on a number of datasets, and is done daily.

The problem I am having is the I am getting the following error message:

Error Code: 1064. 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 ''20160913.csv'' at line 12

    SET @exportfile = 
   CONCAT("SELECT   
            'FIELD_1'
        ,   'FIELD_2'
        ,   'FIELD_3'
        ,   'FIELD_4'
        ,   'FIELD_5'
        ,   'FIELD_6'
        UNION ALL
        SELECT * FROM mydatabasetable INTO OUTFILE 'E:/Data/exports/DailyExport/myfilename_' 
        FIELDS TERMINATED BY ','
        ENCLOSED BY '''
        LINES TERMINATED BY '\r\n'"
       , DATE_FORMAT( NOW(), '%Y%m%d')
       , ".csv'"
    );

PREPARE processing FROM @exportfile;
EXECUTE processing;
DROP PREPARE processing;

Please can someone identify what I am doing wrong?

3

The immediate issue is that you are appending the formatted date string and .csv' to the end of the query string, rather than to the end of the file path as I am assuming is the intention.

It seems to me your CONCAT expression should actually go like this:

CONCAT("SELECT ... INTO OUTFILE 'E:/Data/exports/DailyExport/myfilename_"
  , DATE_FORMAT( NOW(), '%Y%m%d')
  , ".csv'
    FIELDS TERMINATED BY ','
    ..."
)

Note that there's no ' at the end of the E:/Data/exports/DailyExport/myfilename_, because the date is appended at that point, followed by .csv' which completes the name.

Once you have fixed that, you are going to face another issue, which will be caused by the ENCLOSED BY line of your dynamic query. The ' character must be escaped when itself enclosed in '. So, it should be either:

...
ENCLOSED BY ''''
...

or

...
ENCLOSED BY '\''
...
5
  • Sorry wanted to ask, would I be able to use this as is in a PHP script? The problem I foresee are the " marks as one would use this for your PHP script... – Leon Claassen Sep 13 '16 at 13:33
  • @LeonClaassen: I'm not really a PHP expert, but, if I'm not mistaken, PHP allows you to use either double or single quotes to delimit your string literals. So it would be very similar to how you are building the query in MySQL: you would use double quotes to delimit the string literals that are parts of the SQL query being built, and you would use single quotes inside that SQL query (to delimit the file name and other OUTFILE parameters). If it's MySQLi, for instance, then it would be something like mysqli_query("SELECT ... INTO 'E:.../myfilename_", dateformattingstuff, ".csv' FIELDS ...") – Andriy M Sep 13 '16 at 14:02
  • Thanks, I appreciate all the help here. The part that is confusing me or has me scratching my head is the SET @exportfile = CONCAT. as well as the PREPARE / EXECUTE / DROP PREPARE statements. I'm assuming this is seen as part of the script in PHP and would therefore also need to be in the double quotes. I'm going to give it a try and let you know the outcome. – Leon Claassen Sep 14 '16 at 5:28
  • @LeonClaassen: Ah, so you were talking about building your entire MySQL script in PHP, including PREPARE/EXECUTE? Yes, that would probably make it a little trickier. I think, if I was doing that, I would still use double quotes in PHP and escape inner double quotes. However, I wouldn't be building everything. Building and executing just the SELECT ... INTO OUTFILE query would seem to me enough. In fact, the MySQLi library may actually be using PREPARE under the covers. But, as I said, I don't have much experience, I can only speak based on what I saw in examples found across the Internet. – Andriy M Sep 14 '16 at 5:40
  • Yip, my first step was to just get it working as a regular MySQL script. I find that doing it that way always makes it easier to then plug into PHP. Kind of a quality control, to see that the script actually works. The reason for my madness here is because I am trying to automate a large amount of data imports that include generating a number of daily file dumps. The entire manual process currently takes 2 hours just to get the data out. Which is time wasted. So if I can manage to get all of this working, it will save me 2 hours. – Leon Claassen Sep 14 '16 at 5:45

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.