B4J Question SQLite database ON CONFLICT useage

A

Adrian Jansen

Member
Licensed User
Longtime User
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

Table creation: 
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 to table: 
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
B4X: 
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: 
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)
 
Last edited:
Sort by date Sort by votes
A

Adrian Jansen

Member
Licensed User
Longtime User
Reply to my own question
The correct syntax for this brand of SQLite is:
Insert or IGNORE: 
INSERT OR IGNORE INTO tblWords (word,Length) VALUES (""test5"",5)
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Adrian Jansen said:
B4X: 
INSERT INTO tblWords (word,Length) VALUES ("test5",5) ON CONFLICT DO NOTHING
Click to expand...

Adrian Jansen said:
Then I get a huge pile of Java errors:
Click to expand...

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.
B4X: 
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.,
 
Last edited:
Upvote 0
A

Adrian Jansen

Member
Licensed User
Longtime User
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:
Added INSERT MAPS Sub: 
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.
 
Upvote 0
You must log in or register to reply here.

Similar Threads

P
  • Question
B4J Question B4XTable SQLite version (syntax errors)
Replies
2
Views
1K
proinko
P
  • Question
B4J Question In Memory SQLite Database
Replies
9
Views
3K
klaus
  • Question
B4J Question Concurrent access
Replies
1
Views
1K
Erel
  • Question
B4J Question Use raw SQL or DBUTILS & SQL?
Replies
2
Views
1K
Erel
M
  • Question
B4J Question [SOLVED] [Server][WebSockets] SQLite clarifications
Replies
6
Views
2K
keirS
K
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…