0

I am trying to execute the following SQLite upsert statement:

String stmtStr = "INSERT INTO charge_sets(name, description, ref_name) VALUES('test', 'test', 'test') ON CONFLICT(name) DO UPDATE SET description='test', ref_name='test';";

however, when the statement is prepared using:

PreparedStatement placeStatement = conn.prepareStatement(stmtStr);

the following SQLException is thrown:

[10:02:42 INFO]: Test query: INSERT INTO charge_sets(name, description, ref_name) VALUES('test', 'test', 'test') ON CONFLICT(name) DO UPDATE SET description='test', ref_name='test';
[10:02:42 WARN]: org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "ON": syntax error)
[10:02:42 WARN]:        at org.sqlite.core.DB.newSQLException(DB.java:909)
[10:02:42 WARN]:        at org.sqlite.core.DB.newSQLException(DB.java:921)
[10:02:42 WARN]:        at org.sqlite.core.DB.throwex(DB.java:886)
[10:02:42 WARN]:        at org.sqlite.core.NativeDB.prepare_utf8(Native Method)
[10:02:42 WARN]:        at org.sqlite.core.NativeDB.prepare(NativeDB.java:127)
[10:02:42 WARN]:        at org.sqlite.core.DB.prepare(DB.java:227)
[10:02:42 WARN]:        at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:41)
[10:02:42 WARN]:        at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:30)
[10:02:42 WARN]:        at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:19)
[10:02:42 WARN]:        at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:48)
[10:02:42 WARN]:        at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:263)
[10:02:42 WARN]:        at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:235)

I cannot figure out why. Using a software such as SQLite DB Browser I was able to execute this statement perfectly fine, however the java SQLite library is not working (I am using version 3.42.0.0).

Extrreme
  • 1
  • 1
  • Try getting rid of that semicolon – g00se Jun 16 '23 at 14:56
  • [For the INSERT and UPDATE commands, the keywords "ON CONFLICT" are replaced by "OR" so that the syntax reads more naturally.](https://www.sqlite.org/lang_conflict.html) At a quick glance, I don't see any evidence that a qualifier is possible for `CONFLICT` – g00se Jun 16 '23 at 15:00
  • @g00se I got the format for the statement from here: https://www.sqlite.org/lang_UPSERT.html – Extrreme Jun 16 '23 at 15:18
  • 1
    Ah right, I didn't check the `UPSERT` docs out. You need to make sure your driver supports it. Did removing the semicolon have any effect? – g00se Jun 16 '23 at 15:23
  • @g00se Unfortunately, no, I removed the semicolon, and the `(name)` but neither resolved the issue. It says the `UPSERT` syntax was added in 3.24.0 and I am using 3.42.0.0 so it should be there? – Extrreme Jun 16 '23 at 15:35
  • 1
    Does a `SELECT sqlite_version()` from in the same program that you're trying to run this insert in say you're using 3.42? – Shawn Jun 16 '23 at 15:53

0 Answers0