MYSQL syntax errorm in sql string in java

Question

Here's a code that runs perfectly in mysql command line however when I try to execute the query in java it give a syntax error not sure what's wrong.

I've added spaces and revised the code already, still not finding the solution.

String path = selectedfile.getAbsolutePath();
String sql = "LOAD DATA LOCAL INFILE '" + path + "' REPLACE INTO TABLE 
temp FIELDS TERMINATED BY ',' ENCLOSED BY '" + '"' + " LINES TERMINATED BY 
'\r\n' IGNORE 1 LINES \n" + " 
(fname,lname,email,idemployee,statu,@hiredate,idsupervisor,
jobtitle,description,country,site,clockid) "+ 
"SET hiredate = STR_TO_DATE(@hiredate, '%m/%d/%Y');";

Here's the output in java

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 '' IGNORE 1 LINES (fname,lname,email,idemployee,statu,@hiredate,idsupervisor,job' at line 2

are the field name column names are correct - statu - @hiredate — zod, Sep 27 '19 at 17:06
Possible duplicate of [How to insert selected columns from a CSV file to a MySQL database using LOAD DATA INFILE](https://stackoverflow.com/questions/4202564/how-to-insert-selected-columns-from-a-csv-file-to-a-mysql-database-using-load-da) — zod, Sep 27 '19 at 17:06
@zod yes they're correct as status is a reserved word a replaced the column for statu and hiredate it's because I manipulate string to date when importing from csv — Jared Alfaro, Sep 27 '19 at 17:09

score 0 · Answer 1 · edited Aug 11 '20 at 21:52

0

You need to escape the backslashes in the query, so they'll be passed through from Java to MySQL.

You're also missing a ' around the ENCLOSED BY setting. Instead of concatenating strings there, you can just escape the double quote.

String sql = "LOAD DATA LOCAL INFILE '" + path + "' REPLACE INTO TABLE 
temp FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY 
'\\r\\n' IGNORE 1 LINES \n" + " 
(fname,lname,email,idemployee,statu,@hiredate,idsupervisor,
jobtitle,description,country,site,clockid) "+ 
"SET hiredate = STR_TO_DATE(@hiredate, '%m/%d/%Y');";

edited Aug 11 '20 at 21:52

HoldOffHunger

18,769
10
104
133

answered Sep 27 '19 at 17:07

Barmar

741,623
53
500
612

It give this error message @Barmar: 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 '\r\n' IGNORE 1 LINES – Jared Alfaro Sep 27 '19 at 17:17
I'm not a Java programmer, but I don't think it allows you to wrap strings like this, so your code shouldn't even be compiling in the first place. Can you post the actual code, without putting line breaks into the string? – Barmar Sep 27 '19 at 17:21
Here's the code I use in mysql: LOAD DATA INFILE 'test.csv' REPLACE INTO TABLE temp FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (fname,lname,email,idemployee,statu,@hiredate,idsupervisor,jobtitle,description,country,site,clockid) SET hiredate = STR_TO_DATE(@hiredate, '%m/%d/%Y'); and this code runs perfectly on MYSQL command line – Jared Alfaro Sep 27 '19 at 18:08

score 0 · Answer 2 · answered Sep 27 '19 at 19:09

0

Hi guys I managed to get the code running here's the code that works as an example for future cases:

String sql = "LOAD DATA LOCAL INFILE '" + path + "' REPLACE INTO TABLE temp
\n FIELDS TERMINATED BY ',' \n ENCLOSED BY '" + '"' + "' \n LINES TERMINATED 
BY '\\r\\n' \n 
IGNORE 1 LINES \n" +
" (fname,lname,email,idemployee,statu,@hiredate,idsupervisor,
jobtitle,description,country,site,clockid) \n"+
"SET hiredate = STR_TO_DATE(@hiredate, '%m/%d/%Y');";

answered Sep 27 '19 at 19:09

Jared Alfaro

1
2

Could you explain the fix so we don't have to hunt for the difference? – Barmar Sep 27 '19 at 20:03
The only difference I can see is the placement of `\n` in the string, but that shouldn't make any difference -- MySQL doesn't distinguish between spaces and newlines as whitespace. – Barmar Sep 27 '19 at 20:06
@Barmar The only difference I notice is the addition of a `'` to finish enclosing `ENCLOSED BY '"' `; the originally posted version would have been equivalent to `ENCLOSED BY '"andstuff' 'IGNORE 1 ....` – Uueerdo Sep 27 '19 at 20:45
Seems like it would have been easier to write `ENCLOSED BY '\"'` instead of using that confusing concatenation. – Barmar Sep 27 '19 at 20:47
So it seems that the mysql java driver works by creating the statement exactly as it is in mysql. I've added the \n to the lines where should add an enter and added an extra backslash to LINES TERMINATED BY – Jared Alfaro Sep 27 '19 at 21:49

MYSQL syntax errorm in sql string in java

2 Answers2