java.sql.SQLException: near "ON": syntax error

Question

Hey guys I have scoured the many other answers and am still unable to figure out this problem.

statement.executeUpdate("CREATE TABLE IF NOT EXISTS recentlyWatched (fileName STRING NOT NULL, fileLocation STRING, viewCount INTEGER, PRIMARY KEY (fileName))");

Class.forName("org.sqlite.JDBC");
        connection = DriverManager.getConnection("jdbc:sqlite:media.db");
        statement = connection.createStatement();
        statement.executeUpdate("INSERT INTO recentlyWatched (fileName, fileLocation, viewCount) VALUES ('"
                + fileName + "', '"
                + fileLocation + "', 0) ON DUPLICATE KEY UPDATE viewCount = '1'");

I have tried numerous ways to resolve this issue and am at a loss whenever i try to insert a record i get "java.sql.SQLException: near "ON": syntax error".

Update

Final code looked like this

statement.executeUpdate("INSERT OR IGNORE INTO recentlyWatched (fileName, fileLocation, viewCount) VALUES ('"
                + fileName + "', '"
                + fileLocation + "', 0)");
        statement.executeUpdate("UPDATE recentlyWatched SET viewCount = viewCount + 1 WHERE '" + fileName + "' LIKE fileName;");

I am using JDBC it works perfectly fine when i dont have ON DUPLICATE KEY UPDATE viewCount = '1'"). — Ash, Aug 24 '13 at 08:01
You should use PreparedStatement. Your code is vulnerable to SQL Injection attack. — Rohit Jain, Aug 24 '13 at 08:02
I'll give it a shot with bind variables again and see how I go. — Ash, Aug 24 '13 at 08:03

score 5 · Accepted Answer · edited May 23 '17 at 12:21

5

As one can tell from Class.forName("org.sqlite.JDBC") you are using SQLite database which doesn't support the ON DUPLICATE clause. This is the reason why you get this error.

Check this question for a workaround.

edited May 23 '17 at 12:21

Community

1
1

answered Aug 24 '13 at 08:09

svz

4,516
11
40
66

score 1 · Answer 2 · answered Aug 24 '13 at 08:15

As an alternative I would suggest using it like this

("REPLACE INTO recentlyWatched (fileName, fileLocation, viewCount) VALUES ('"
                + fileName + "', '"
                + fileLocation + "', 0)

The difference is that is does a DELETE and then an INSERT

java.sql.SQLException: near "ON": syntax error

2 Answers2