Android Question Android and Sqlite version

udg · Jan 4, 2023

Hi all,

in one of my recent apps I have the opportunity to use Sqlite UPSERT option to elegantly solve the INSERT or Update problem.
As shown in other threads (one of which, showing different solutions, is this one), Sqlite version depends on the Android API installed on the device.
For UPSERT (ON CONFLICT..) it need version 3.24+, so at least Android API 30 (i.e. Android 11 device).

So, what did you use in your apps? In Manifest I go as low as API 24 (Android 7, i.e. my own device..)

BTW, it wll be preferable a solution working identical on Android and iOS (starting from iOS 8).

TIA

udg · Jan 4, 2023

Since it was new to me (and I like to learn new stuff whenever I can), I succesfully tried the following (adapted to my DB):

B4X:

'-- Try To update any existing row
'UPDATE players
'SET age=32
'WHERE user_name='steven';
'-- If no update happened (i.e. the row didn't exist) then insert one
'INSERT INTO players (user_name, age)
'Select 'steven', 32
'WHERE (Select Changes() = 0);

My Android's sqlite version is 3.9.2 and the above worked. Now it's time to see what happens under iOS...

aeric · Jan 4, 2023

How about

SQL:

INSERT OR REPLACE INTO table(column_list)
VALUES(value_list)

Reference: https://www.sqlitetutorial.net/sqlite-replace-statement/

LucaMs · Jan 4, 2023

aeric said:
How about

SQL:

INSERT OR REPLACE INTO table(column_list) VALUES(value_list)

Reference: https://www.sqlitetutorial.net/sqlite-replace-statement/

But it is not an UPDATE OR REPLACE and is only useful in case of key conflict (it's better to do it "by hand")

udg · Jan 4, 2023

@aeric : it's one of the possible ways, but as noted by @LucaMs is not exactly an update.
AFAIK, it modifies autoincrement values, row id and something else (I mean, there are points to take in account). It could be ok in some circumstances and leading to unwanted results in others.
Obviously, even "changes" has its own quirks to consider. So we are expected to pick up the "right" option for the specific problem at hand.
A common, universal UPSERT would be a real gift, but having a few alternatives is acceptable too.

BTW, since sqlite it's at the core of Android for many functionalities, why they don't update it for all, version after version? We're obliged to update our apps even for marginally important "new ideas", while they seems so lazy when it comes to their own chores..
Well, ok. Their platform, their rules..

Mahares · Jan 4, 2023

udg said:
elegantly solve the INSERT or Update

If you want to enforce the UPSERT syntax, this would work after checking the SDK and SQLite versios:

B4X:

Dim ph As Phone  'need phone lib
    Dim sqliteversion As String =SQL.ExecQuerysingleresult( "SELECT SQLite_version()")
    Dim sv() As String =Regex.Split("\.", sqliteversion)
    If ph.SdkVersion >=30  And sv(1) >= 24  Then
        Log( "I can use UPSERT")
        Dim strQuery As String = $"INSERT INTO players (user_name, age) VALUES('steven',32)
        ON CONFLICT(user_name) DO UPDATE SET age=excluded.age"$
        SQL.ExecNonQuery(strQuery)
    Else
        Log( "I cannot use UPSERT, but I can use the old way")
    End If

Android Question Android and Sqlite version

udg

Expert

udg

Expert

aeric

Expert

LucaMs

Expert

udg

Expert

Mahares

Expert

Similar Threads

Android Question Android and Sqlite version

udg

Expert

udg

Expert

aeric

Expert

LucaMs

Expert

udg

Expert

Mahares

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency