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
– zod, Commented Sep 27, 2019 at 17:06
Possible duplicate of How to insert selected columns from a CSV file to a MySQL database using LOAD DATA INFILE — zod
– zod, Commented Sep 27, 2019 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
– Jared Alfaro, Commented Sep 27, 2019 at 17:09

Jared Alfaro · Accepted Answer · 2019-09-27 19:09:17Z

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, 2019 at 19:09

Jared Alfaro

12 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Barmar Over a year ago

Could you explain the fix so we don't have to hunt for the difference?

Barmar Over a year ago

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.

Uueerdo Over a year ago

@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 ....

Barmar Over a year ago

Seems like it would have been easier to write ENCLOSED BY '\"' instead of using that confusing concatenation.

Jared Alfaro Over a year ago

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

HoldOffHunger · Accepted Answer · 2020-08-11 21:52:02Z

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, 2020 at 21:52

HoldOffHunger

21.3k11 gold badges123 silver badges147 bronze badges

answered Sep 27, 2019 at 17:07

Barmar

789k57 gold badges554 silver badges669 bronze badges

3 Comments

Jared Alfaro Over a year ago

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

Barmar Over a year ago

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?

Jared Alfaro Over a year ago

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

Collectives™ on Stack Overflow

MYSQL syntax errorm in sql string in java

2 Answers 2

5 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related