B4J Question SQLite database ON CONFLICT useage

[Adrian Jansen]

Hi,
Im am building a dictionary database using SQLite under B4J, and having problems getting an SQL statement usingt an ON CONFLICT clause to work.
Still getting used to B4J, and SQLite too, but it looks like a good pair of tools.

Note that in the code below I am just using SQL, then running it under B4J with the SQL command SQL.ExecNonQuery(query)
I know how to create the SQL strings using StringBuilder and the utilities in the DBUtils module

CREATE TABLE "tblWords" (
    "wordID"    INTEGER,
    "word"    TEXT,
    "Length"    INTEGER,
    "Plural"    INTEGER,
    "Proper"    INTEGER,
    UNIQUE("word"),
    PRIMARY KEY("wordID")
);

That works fine to create the table, note the UNIQUE command to limit insertions to unique words
Then if I do:

INSERT INTO tblWords (word,Length) VALUES ("test5",5)

That works fine.
But of course if I do the same INSERT again, it fails because of the UNIQUE constraint - that is what I want
However if I use

INSERT INTO tblWords (word,Length) VALUES ("test5",5) ON CONFLICT DO NOTHING

Then I get a huge pile of Java errors:
Table size 4
*** p_word: B4XPage_Appear [mainpage]
*** p_word: B4XPage_Resize [mainpage, p_word]
INSERT INTO tblWords (word,Length) VALUES ("test5",5) ON CONFLICT DO NOTHING
Error occurred on line: 884 (m_word)
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 wordle.database.m_word._btntest_click(m_word.java:753)
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:234)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
at jdk.internal.reflect.GeneratedMethodAccessor2.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$1.run(BA.java:236)
at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:428)
at java.base/java.security.AccessController.doPrivileged(Native Method)

I tried the same SQL on a little SQLIte DBBrowser program, and it works perfectly. But I know there are many flavours of SQLIte. Does the version used by the B4J system allow this ?
I know I can use a variation like this:

INSERT OR REPLACE INTO tblWords (word,Length) VALUES ("test5",5)

and it works. But it has the drawback that it actually removes the old record, then creates a new one, with a new recordID. When doing this repeatedly on a large collection of long word lists, it takes extra processing time, which seems to me a waste when the ON CONFLICT clause should just skip the INSERT.

SQLite library jSQL version 1.61
B4J version 9.80

Any comments would be appreciated.



at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$11(PlatformImpl.java:427)
at javafx.graphics/com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:96)
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)

 

[Adrian Jansen]

Reply to my own question
The correct syntax for this brand of SQLite is:

INSERT OR IGNORE INTO tblWords (word,Length) VALUES (""test5"",5)

 

[Mahares]

Reply to my own question

Did you try:

INSERT INTO tblWords (word,Length) VALUES ("test5",5) ON CONFLICT(Word) DO NOTHING

 

[Mahares]

INSERT INTO tblWords (word,Length) VALUES ("test5",5) ON CONFLICT DO NOTHING

Then I get a huge pile of Java errors:

Actually, the problem may stem from the fact you are using #AdditionalJar: sqlite-jdbc-3.7.2 in main module
If you were using: #AdditionalJar: sqlite-jdbc-3.24.0 or higher the below code will work.

INSERT INTO tblWords (word,Length) VALUES ("test5",5) ON CONFLICT DO NOTHING

I use #AdditionalJar: sqlite-jdbc-3.36.0.3 and it works for me. The jar file has to be downloaded to B4J additional library folder.,

 

[Adrian Jansen]

Indeed, it works on a later JAR. I had other issues with that earlier version interpreting some other fairly complex SQL, and replaced it with what seems to be the latest version

sqlite-jdbc-3.40.0.0.jar.

That works with the ON CONFLICT cause too.
But I did see a note that the ON CONFLICT clause appears to be non-standard https://www.sqlite.org/search?s=d&q=on+conflict
whereas the INSERT OR IGNORE construction appears to be standard. That worked with my original jar, and also with the latest one I am running. I think I will stick with the INSERT OR IGNORE clause. I just added another sub into the DButils code I am using:

Public Sub InsertMapsND(SQL As SQL, TableName As String, ListOfMaps As List)
    'added check for UNIQUE constraint using IGNORE to avoid duplications
    Dim sb, columns, values As StringBuilder
    'Small check for a common error where the same map is used in a loop
    If ListOfMaps.Size > 1 And ListOfMaps.Get(0) = ListOfMaps.Get(1) Then
        Log("Same Map found twice in list. Each item in the list should include a different map object.")
        Return
    End If
    SQL.BeginTransaction
    Try
        For i1 = 0 To ListOfMaps.Size - 1
            sb.Initialize
            columns.Initialize
            values.Initialize
            Dim listOfValues As List
            listOfValues.Initialize
            sb.Append("INSERT OR IGNORE INTO [" & TableName & "] (")
            Dim m As Map
            m = ListOfMaps.Get(i1)
            For i2 = 0 To m.Size - 1
                Dim col As String
                Dim value As Object
                col = m.GetKeyAt(i2)
                value = m.GetValueAt(i2)
                If i2 > 0 Then
                    columns.Append(", ")
                    values.Append(", ")
                End If
                columns.Append(EscapeField(col))
               
                values.Append("?")
                listOfValues.Add(value)
            Next
            sb.Append(columns.ToString)
            sb.Append(") VALUES (  ")
           
            sb.Append(values.ToString)
           
           
            sb.Append(")")
           
           
   
'            Log("Append " & sb)
           
            '        If i1 = 0 Then Log("InsertMaps (first query out of " & ListOfMaps.Size & "): " & sb.ToString)
            SQL.ExecNonQuery2(sb.ToString, listOfValues)
        Next
        SQL.TransactionSuccessful
    Catch
        Log(LastException)
        SQL.Rollback
    End Try
   
   
   
End Sub

This keeps the original version. In some cases you need to be able to add duplicates, like the original one does.
Thanks for the comments though.