2

I'm having a problem with setting a variable path for putting data into my table. This is how i build my path:

SET @path1 = CONCAT('C:/Projekte/Metrics/DXL_CSV_EXPORT_DATA/', YEAR(NOW()), '_',    MONTH(NOW()), '_', DAY(NOW()), '%', '/_','BeMiko/');`

Every day another Folder is created on the server. I want to automatically import the information from the .csv files from inside these folders each day.

I import data from files with:

LOAD DATA INFILE 
path...
IGNORE INTO TABLE table1 FIELDS TERMINATED BY ';' ENCLOSED BY '"'
LINES TERMINATED BY '<*line_end*>\r\n' IGNORE 1 ROWS;

How do i use the string inside my @path1 variable as path? Or if this is not possible: Are there other ways to solve this problem?

Officer Bacon
  • 724
  • 1
  • 7
  • 22
  • You should be able to generate the SQL statement on-the-fly. [Here is an example](http://stackoverflow.com/a/10025538/1446005). – RandomSeed Aug 19 '14 at 11:00
  • This could work, but somehow i always get an error if i try to execute the procedure. The error says: This command is not supported in the prepared statement protocol yet (Error Code 1295). How can i fix this? – Officer Bacon Aug 19 '14 at 12:22
  • Oops. Then I don't know. Do you have shell access? Then you could `mysql -e "LOAD DATA INFILE $var ..."` – RandomSeed Aug 19 '14 at 12:23
  • Working in a shell isn't what i should do right now. I need to create a procedure, which gets executed once a day (event..). – Officer Bacon Aug 19 '14 at 12:32

1 Answers1

2

Variables can't be used to substitute paths in the LOAD DATA INFILE statement.

Also:

User variables may be used in most contexts where expressions are permitted. This does not currently include contexts that explicitly require a literal value, such as in the LIMIT clause of a SELECT statement, or the IGNORE N LINES clause of a LOAD DATA statement.

Bogdan Mircea Stanciu
  • 222
  • 1
  • 11