4

I'm trying to write a MySQL script to import data into a table for my Linux server. Here is the script named update.sql:

SET @query = CONCAT("LOAD DATA LOCAL INFILE '", @spaceName, "' INTO TABLE tmp FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';"); 
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

And also, I write a bash script named main.sh:

mysql -h "localhost" -u "root" "-pmypassword" "mydb" -e "set @spaceName=\"$1\";source update.sql;"

Then I execute ./main.sh France.list. The France.list is the data file that I'm trying to import into my database.

However I get an error:

ERROR 1295 (HY000) at line 2 in file: 'update.sql': This command is not supported in the prepared statement protocol yet

I've found this question:
MySQL - Load Data Infile with variable path

So, does it mean that there is no way to pass arguments to LOAD DATA query?

Yves
  • 11,597
  • 17
  • 83
  • 180

2 Answers2

5

You can't use PREPARE to run LOAD DATA INFILE.

The list of statements that you can run with PREPARE are documented in this page: https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html under the subheading "SQL Syntax Allowed in Prepared Statements". Note this list may be different in earlier versions of MySQL.

Because you can't use PREPARE, you can't do the method you're using by setting a variable and making a dynamic SQL statement.

But you can run LOAD DATA INFILE without using PREPARE. You have to interpolate the filename into the statement using shell variable substitution and then run it as a direct SQL statement.

Your update.sql file might look like this:

LOAD DATA LOCAL INFILE '%spacename%' INTO TABLE tmp 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Then you can substitute your shell variable into the file and run the result this way:

sed s/%spacename%/$1/ update.sql | 
  mysql -h "localhost" -u "root" "-pmypassword" "mydb"

Another simpler way is to use mysqlimport, except this requires that the input filename be the same as your table name. You can either rename your input file to match the table you want to load into (which you call tmp), or else create a symbolic link:

ln -s $1 /tmp/tmp.list
mysqlimport --local -h "localhost" -u "root" "-pmypassword" "mydb" /tmp/tmp.list
rm -f /tmp/tmp.list

The ".list" extension is ignored by mysqlimport, so you can use any file extension, or none.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Oracle and MySQL sucks. You can't even `USE @MY_DB_NAME;` or prepare it. Oracle should be ashamed of themselves. – jww Feb 19 '19 at 21:24
  • @jww Can you give an example of another RDBMS that allows it? The argument to `USE` is an identifier, not a string value. But session variables and query parameters are always treated as values. You can't use session variables or query parameters for identifiers in any type of SQL query in any brand of RDBMS I know of. – Bill Karwin Feb 19 '19 at 21:29
  • No, sorry, I cannot. I'm not a SQL guy. I use SQL every three or five years or so. I can give you examples of other languages that allow you to use variables in Hello World programs, however. SQL is the only language I am aware you cannot use variables and have to do things like prepare them (sometimes, until it breaks like now). – jww Feb 19 '19 at 21:35
  • @jww So can you give an example of another language that is strongly-typed where you can call a function or instantiate a class based on the name of that function or class in a string variable? I know about reflection in Java and variable-variables in PHP and things like that, but the point is it takes extra code or extra syntax, and usually a two-step approach — just like prepare in SQL. – Bill Karwin Feb 19 '19 at 21:58
  • Oh, you're missing the big picture. In other languages I can use variables directly. In SQL sometimes I can use variables indirectly after a prepare, and other times I cannot. `USE @MY_DB_NAME;` is an example of not being able to use a variable at all. – jww Feb 19 '19 at 22:07
  • I'm also not sure what the point of *"Can you give an example of another RDBMS that allows it"* is. Are you arguing other RDBMS suck so it is OK if Oracle sucks, too? I think that is known as *"race to the bottom"*. It is not something to be proud of. – jww Feb 19 '19 at 22:09
  • 1
    I'm saying it's not something that any RDBMS supports because it's not a feature that makes any sense. You can't use a string as an identifier. The same is true of most compiled languages — you can't instantiate an object like `new 'Classname'()` nor `new stringvar()` where the stringvar names the class you want to create. – Bill Karwin Feb 19 '19 at 22:27
1

If you are using Java 8+, Mysql 8.0+ and Connector/J library 8.0.18 then might be you will face this issue like "command is not supported by this version" or something or similar to that.

To solve the issue, you have to add the property which tells preparedStatement to all load local infile. YOu have to add configure it, I configure it in connection url and resolve the issue.

jdbc:mysql://localhost:3306/tempDB?allowLoadLocalInfile=true

Few changes I have also made in my.cnf file like:

  1. local_infile = 1
Atul
  • 3,043
  • 27
  • 39