Android Question Updated SQLite version into project

[AKJammer]

Hey All, Sorry, probably a newb question, but I haven't been working on this project much lately.

I have a B4A app that does some internal data store before the data is written to the mySQL database upon submission by the user.
The internal database I've always assumed was SQLite, it's being defined as SQL type.
I wanted to use the UPSERT feature that became available in 2018 with version 3.24. In my libraries folder I found I had version 3.7, so I downloaded the latest version from the forum here (3.45.2) and put it into the libraries folder. Recompiling didn't seem to pick it up, still errors on the sql statement. The libraries tab in the editor doesn't even show SQLite so I'm assuming it's internally built in.

android.database.sqlite.SQLiteException: near "ON": syntax error (code 1): , while compiling: insert into scores (

What steps do I need to do to get B4A to pick up the new library?

 

[AKJammer]

Ah, under similar threads I found this. https://www.b4x.com/android/forum/threads/update-sqlite-version.148286/
Looks like I'm out of luck. I'll just do a Try insert Catch update. Thanks

 

[MicroDrie]

What steps do I need to do to get B4A to pick up the new library?

If you:

1. Open SQLite JDBC - Library version updates
2. Scroll to the end of Post #1 to (currently) Sqlite-jdbc version 3.39.2.0 (2021.08.25) available for download HERE
3. Click on here and store the jar at your external library directory
4. Add jar reference in Main module
   #AdditionalJar: sqlite-jdbc-3.39.2.0
5. Repalce/add
6. Private Sub Button1_Click
       xui.SetDataFolder("sql example") ' --- required in B4J
       Log(xui.DefaultFolder)
       If File.Exists(xui.DefaultFolder, "SQLUPSERT.db") Then
           File.Delete(xui.DefaultFolder, "SQLUPSERT.db")
       End If
   #IF B4J
       m3SQL.InitializeSQLite(xui.DefaultFolder, "SQLUPSERT.db", True)
   #END IF
   #IF B4A
       m3SQL.Initialize(xui.DefaultFolder, "SQLUPSERT.db", True)
   #End If
       queryStr = $"SELECT sqlite_version()"$
       Dim resultStr As String = $"SQL version = ${m3SQL.ExecQuerySingleResult(queryStr)} "$
       xui.MsgboxAsync(resultStr, "B4X")
       m3SQL.Close
   End Sub
7. The popup message will be: "SQL version = 3.39.2"

Most likely, in the absence of a small sample program and , you are making an incorrect SQLite query query. Can you add additional information on how to use the SQLite UPSERT command and what the table information looks like?

 

[aeric]

I wanted to use the UPSERT feature

Have you wondered how programmers achieve it without using UPSERT?
UPSERT = UPDATE + INSERT

For me, it is just a lazy way of writing SQL query. I never use it.

What I usually do is
1. Query the record with SELECT.
2. If the record existed, execute an UPDATE command.
3. If the record is not exist, execute an INSERT command.

And usually, I will show a message to the user before updating (overwriting) in the case of a record is already exist.
Unless you are developing a non-UI app which do the execution without human intervention. If I am not mistaken there is also a REPLACE keyword.

 

[OliverA]

What steps do I need to do to get B4A to pick up the new library?

Two possible solutions (JdbcSQL, SQLCipher) to your issue are mentioned in this thread: https://www.b4x.com/android/forum/threads/how-create-a-wraper-sqlite-3430000.150276/

 

[OliverA]

Ah, under similar threads I found this. https://www.b4x.com/android/forum/threads/update-sqlite-version.148286/
Looks like I'm out of luck. I'll just do a Try insert Catch update. Thanks

Sadly, I missed that thread, else I would have posted the same possible solution as above