Outfile using Prepared Statement

[harrisd]

I am attempting to generate a outfile with a unique name using time and date along with a concat of the file name.
The prepare statement throws an syntax error. I have tried single quotes, double quotes and combinations of each to no success. What am I missing? Can the outfile even be dynamically named as I'd like?

-------------------------------------------------------

SELECT @sync:=concat(CURDATE(),CURTIME());
 
SET @marcFile = CONCAT('c:/scripts/marcUpdate', @sync,'.txt');
 
PREPARE stmt_name FROM concat('SELECT * INTO OUTFILE ', @marcFile , ' FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \' " \' LINES TERMINATED BY \'\n\' FROM marcupdate');
 
EXECUTE stmt_name;
 
DEALLOCATE PREPARE stmt_name;

--------------------------------------------------------
Best regards

[pbmods]

Heya, Harris. Welcome to TSDN!

What is the text of the error that you are getting?

[harrisd]

pbmods:

I get a simple syntax error with no explantion except the line location.

harrisd

[pbmods]

Heya, Harris.

What's the line location? Go ahead and copy-paste the error message (that's what they're for, after all!).

[harrisd]

When I run the six queries, I get the responses:
-------------------------------------------------------------------

SELECT @sync:=concat(CURDATE(),CURTIME());
 
UPDATE marcUpdate
SET ssn = replace(ssn,'-','');
 
SET @marcFile = CONCAT('c:/scripts/marcUpdate', @sync,'.txt');
 
PREPARE stmt_name FROM concat ('SELECT * INTO OUTFILE ', @marcFile , ' FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\' LINES TERMINATED BY \'\n\' FROM marcupdate');
 
EXECUTE stmt_name;
 
DEALLOCATE PREPARE stmt_name;

----------------------------------------------------------------
(1 row(s) returned)
(0 ms taken)

(0 row(s) affected)
(0 ms taken)

(0 row(s) affected)
(0 ms taken)

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 'concat ('SELECT * INTO OUTFILE ', @marcFile , ' FIELDS TERMINATED BY \',\' OPTIO' at line 1
(0 ms taken)

Error Code : 1243
Unknown prepared statement handler (stmt_name) given to EXECUTE
(0 ms taken)

Error Code : 1243
Unknown prepared statement handler (stmt_name) given to DEALLOCATE PREPARE
(0 ms taken)

Obviously the last two fail because the prepared statement is not valid. The error shows from line 1 saying that it is having trouble with the @sync variable. However I have checked the creation of the variable and it is working, as is the @marcFile.(I actually do an update to a table with the variables, I did not include that part of the code). I am running mysql ver. 4.1.18

The frustrating part is this code works if I remove the prepared formatting and use a constant for the outfile. It will not however, overwrite the outfile. A updated file is criticial to the project, thus the dynamic naming requirement.

Best regards.

[pbmods]

Heya, Harris.

MySQL's PREPARE syntax requires a string literal, not an evaluated value. As a result, you can't use CONCAT() there. However, you can use a placeholder and replace it later.

Have a look at this document.

[harrisd]

pbmods:

I'm still struggling with this issue. I'll take it back to the original problem.

This works!

SELECT * INTO OUTFILE 'c:/scripts/marcUpdate.txt' FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM marcupdate;

-----------------------------------------------------
This does not!

SET @marcFile = 'c:/scripts/marcUpdate.txt';
 
SELECT * INTO OUTFILE @marcFile FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM marcupdate;

-----------------------------------------------------
The SET works just fine, the SELECT can not read the @marcFile variable.
Is this actually possible with MySQL?

harrisd

[pbmods]

Heya, Harris.

Please use CODE tags when posting source code. See the REPLY GUIDELINES on the right side of the page next time you post.

Have a look at this:

PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
SET @a = 3;
SET @b = 4;
EXECUTE stmt1 USING @a, @b;
 

[GW22]

I was looking for exactly the same functionality. Have a look at this thread - it sorted it out for me!

http://forums.mysql.com/read.php?97,...2359#msg-62359

The code I ended up with is:

set @myvar = concat('select ?, ?, ?, ?, ?, ?, ?, ?, ?, ? INTO OUTFILE ',  "'",'my/path/',now() + 0,'_', bookingID,'_',Counter, '.email',"'",' FIELDS TERMINATED by \'\r\n\'');
    PREPARE writeBookingFile FROM @myvar;
    EXECUTE writeBookingFile USING @_mailTo, @_mailCC, @_mailBCC, @_mailSubject, @_mailFrom, @_mailFromName, @_mailReplyTo, @_mailPriority, @_mailtype, @_mailBody;
    DEALLOCATE PREPARE writeBookingFile;
 

Which gives me files such as:
/my/path/20070911190701_79108_1.email
/my/path/20070911190701_79108_2.email

Cheers,
Gus