CODEWITHSUNDEEP
javamysql
Jared Alfaro
Jared Alfaro

Reputation: 1

MYSQL syntax errorm in sql string in java

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

Upvotes: 0

Views: 157

Answers (2)

Barmar
Barmar

Reputation: 781741

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');";

Upvotes: 0

Jared Alfaro
Jared Alfaro

Reputation: 1

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');";

Upvotes: 0

Related Questions