B4J Question On Conflict - SQLITE [SOLVED]

[ilan]

hi

i am trying to run a Insert query that includes an update statement if a specific value already exists like it is explained here:

UPSERT

INSERT INTO phonebook2(name,phonenumber,validDate)
  VALUES('Alice','704-555-1212','2018-05-08')
  ON CONFLICT(name) DO UPDATE SET
    phonenumber=excluded.phonenumber,
    validDate=excluded.validDate
  WHERE excluded.validDate>phonebook2.validDate;

but i am getting an error:

Waiting for debugger to connect...
Program started.
Error occurred on line: 241 (Main)
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "ON": syntax error)
at org.sqlite.DB.newSQLException(DB.java:383)
at org.sqlite.DB.newSQLException(DB.java:387)
at org.sqlite.DB.throwex(DB.java:374)
at org.sqlite.NativeDB.prepare(Native Method)
at org.sqlite.DB.prepare(DB.java:123)
at org.sqlite.Stmt.execute(Stmt.java:113)
at anywheresoftware.b4j.objects.SQL.ExecNonQuery(SQL.java:161)
at b4j.example.main._editbtn_mouseclicked(main.java:582)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:629)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
at jdk.internal.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:111)
at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:98)
at anywheresoftware.b4j.objects.NodeWrapper$1.handle(NodeWrapper.java:109)
at anywheresoftware.b4j.objects.NodeWrapper$1.handle(NodeWrapper.java:1)
at javafx.base/com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
at javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at javafx.base/com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
at javafx.base/com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:54)
at javafx.base/javafx.event.Event.fireEvent(Event.java:198)
at javafx.graphics/javafx.scene.Scene$ClickGenerator.postProcess(Scene.java:3564)
at javafx.graphics/javafx.scene.Scene$ClickGenerator.access$8200(Scene.java:3492)
at javafx.graphics/javafx.scene.Scene$MouseHandler.process(Scene.java:3860)
at javafx.graphics/javafx.scene.Scene$MouseHandler.access$1200(Scene.java:3579)
at javafx.graphics/javafx.scene.Scene.processMouseEvent(Scene.java:1849)
at javafx.graphics/javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2588)
at javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:397)
at javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:295)
at java.base/java.security.AccessController.doPrivileged(Native Method)
at javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$2(GlassViewEventHandler.java:434)
at javafx.graphics/com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:390)
at javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:433)
at javafx.graphics/com.sun.glass.ui.View.handleMouseEvent(View.java:556)
at javafx.graphics/com.sun.glass.ui.View.notifyMouse(View.java:942)
at javafx.graphics/com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at javafx.graphics/com.sun.glass.ui.win.WinApplication.lambda$runLoop$3(WinApplication.java:174)
at java.base/java.lang.Thread.run(Thread.java:834)

it looks like the ON statement is making the issue. any help please?

 

[udg]

Did you check if your SQLite version supports ON CONFLICT?
"UPSERT syntax was added to SQLite with version 3.24.0"

 

[ilan]

Did you check if your SQLite version supports ON CONFLICT?
"UPSERT syntax was added to SQLite with version 3.24.0"

how can i check it?
i think that it does not support it and there is a conflict between SQL to SQLite in the lib because SQL b4j lib includes both. but is there a way to check if it supports ON CONFLICT?

 

[Mahares]

check if it supports ON CONFLICT?

If you show this line in your B4J project: #AdditionalJar: sqlite-jdbc-3.7.2, then you need to download the latest version or version 24 or higher from this link:

SQLite JDBC - Library version updates

Hi everybody! This thread is supposed to get all SQLite JDBC library version links together so as to find and download them easier. As a heavy B4J+SQLite user, I'm always looking forward to have new JDBC versions. All information contained herein has been taken from Mr. Taro L. Saito (xerial)...

www.b4x.com

Then replace that line in your code with this if you download the latest in the B4J additional lib: #AdditionalJar: sqlite-jdbc- 3.41.2.1

 

[ilan]

If you show this line in your B4J project: #AdditionalJar: sqlite-jdbc-3.7.2, then you need to download the latest version or version 24 or higher from this link:

SQLite JDBC - Library version updates

Hi everybody! This thread is supposed to get all SQLite JDBC library version links together so as to find and download them easier. As a heavy B4J+SQLite user, I'm always looking forward to have new JDBC versions. All information contained herein has been taken from Mr. Taro L. Saito (xerial)...

www.b4x.com

Then replace that line in your code with this if you download the latest in the B4J additional lib: #AdditionalJar: sqlite-jdbc- 3.41.2.1

thanx a lot, updating to sqlite-jdbc-3.39.2.0 solved the issue!

 

[LucaMs]

[Personal opinion - of course]

Since Upsert is not a standard SQL command but specific to SQLite, I wouldn't use it, for two reasons:

1 - it seems very convenient and you risk wanting to always use it, even when you have to work with other "DBMS";

2 - in the future you might want to reuse the queries, if not even the project source, for other "DBMS"s (in quotes, because I don't think SQLite is considered a real DBMS)

In the specific case the solution is obviously simple, write two queries, and then I do not write it.

 

[ilan]

In the specific case is the solution is obviously simple, write two queries and then do not write it.

i did it but if you have 3000 entries and you run 2 queries instead of 1 it is less effective.
after updating to sqlite-jdbc-3.39.2.0 it works fine.

 

[LucaMs]

I wouldn't use it

You... "can" do it

 

[udg]

@ilan
I use the following code to check about Sqlite version:

Dim sqliteversion As String =SQL1.ExecQuerysingleresult( "SELECT SQLite_version()")
Log("Sqlite: "&sqliteversion)

Note that if you're just testing with B4J but then you plan to move to B4A/B4i AFAIK the sqlite version that needs to support the feature is the one installed on the executing device not the one used to compile. I may be wrong on this, but it was what ringed in my head, so check about it.

 

[ilan]

is the one installed on the executing device not the one used to compile

really?

i was not aware about that. anyway i will use this app only for desktop b4j and only sqlite. it is a program i make for my company. will write a post about it when i am finished.

 

[udg]

really?

I am not 100% sure, but this is what came to my mind associated to UPSERT+SqLite.

Edit: now I know why my mind ringed...read here