MySQL Bugs: #42976: LOAD DATA INFILE command does not accept file names as variables

Bug #42976	LOAD DATA INFILE command does not accept file names as variables
Submitted:	18 Feb 2009 16:15	Modified:	18 Feb 2009 19:04
Reporter:	Pablo S	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	LOAD DATA INFILE, user defined variables

Description:
Cannot use a user-defined variable in sql scripts for the file name in LOAD DATA INFILE.

How to repeat:
use db1;

set @root_folder = 'C:/myfolder/SQL scripts/';
set @file1 = CONCAT( @root_folder, 'file1.txt' );
set @file2 = CONCAT( @root_folder, 'file2.txt' );

SELECT @file1 AS 'file 1';
SELECT @file2 AS 'file 2';

LOAD DATA INFILE @file1 INTO TABLE t1
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n';

LOAD DATA INFILE @file2 INTO TABLE t2
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n';

The output is:
file 1
C:/myfolder/SQL scripts/file1.txt
file 2
C:/myfolder/SQL scripts/file2.txt
ERROR 1064 (42000) at line 11: You have an error in your SQL syntax; check the m
anual that corresponds to your MySQL server version for the right syntax to use
near '@file1 INTO TABLE t1
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'' at line 1

Suggested fix:
Allow user defined variables (i.e. @var style variables) in scripts.

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of bug #39115

Hello,

I used your above code.

I used variable @d2 in the below code to load data from txt file.

Set @d1 = 'C:/Users/pjastrzabek/Desktop/MySQL/Avaya/UoL_Poland/';
set @d2 = concat( @d1, 'UoL_Poland_1_20130114.txt');
load data local infile  @d2  into table Avaya_test ignore 5 lines (Agent_Name, Login_ID);

When I run this cose I received error like below.

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 '@d2  into table test ignore 5 lines (Agent_Name, Login_ID at line 1

I see that the problem is with variable but I do not know how resolve this issue.

Thank you for your feedback.