avz2611 avz2611 - 5 months ago 16
MySQL Question

unable to recognize the cause of error using load data infile

i am loading data into a table inside a stored procedure whose file name is passed as a parameter.

select @load_config:= concat('load data local infile ',config_file,' into table config fields terminated by \',\' lines terminated by \n');
prepare stm from @load_config;


for this i am getting an error


ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '/mnt/appln/mysql/dvdbuser/config.csv into table config fields terminated by ',' ' at line 1
i tried this as well


select @load_config:= concat('load data local infile \'',config_file,'\' into table config fields terminated by \',\' lines terminated by \n');
prepare stm from @load_config;


for which i got the error


ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1


to be honest i am lost and i don't know where i am messing up

Answer

\ is the beginning of an escape sequence.

The first problem is most likely the lack of escaping single quotes. So when you do \' it translates into a single quote thus terminating that block that is not closed. As it is closed now, you get a syntax error due to the string fragment that remain. You would need to do \\' which would bring in a slash and not foobar the single quote after that.

See the Mysql manual page for the table of escape sequences at String Literals.

Even still, this whole thing will not work. Because LOAD DATA INFILE is not supported in stored procedures, events, and the like. It is a sad thing, but allegedly for security reasons. It will generate an error 42000 or the similar.

Error: This command is not supported in the prepared statement protocol yet

A workaround is to use UDF's which require you to modify your mysql environment, or to create an external process for such trivial things which is what I do.