B4J Question On Conflict - SQLITE [SOLVED]

ilan

Expert
Licensed User
Longtime User
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:

SQL: 
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:



it looks like the ON statement is making the issue. any help please?
 
Solution
M
ilan said:
check if it supports ON CONFLICT?
Click to expand...
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
Sort by date Sort by votes

udg

Expert
Licensed User
Longtime User
Did you check if your SQLite version supports ON CONFLICT?
"UPSERT syntax was added to SQLite with version 3.24.0"
 
Upvote 0

ilan

Expert
Licensed User
Longtime User
udg said:
Did you check if your SQLite version supports ON CONFLICT?
"UPSERT syntax was added to SQLite with version 3.24.0"
Click to expand...
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?
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
ilan said:
check if it supports ON CONFLICT?
Click to expand...
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
 
Upvote 0
Solution

ilan

Expert
Licensed User
Longtime User
thanx a lot, updating to sqlite-jdbc-3.39.2.0 solved the issue! ?
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
[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.
 
Upvote 0

ilan

Expert
Licensed User
Longtime User
LucaMs said:
In the specific case is the solution is obviously simple, write two queries and then do not write it.
Click to expand...
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.
 
Upvote 0

udg

Expert
Licensed User
Longtime User
@ilan
I use the following code to check about Sqlite version:
B4X: 
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.
 
Upvote 0

ilan

Expert
Licensed User
Longtime User
udg said:
is the one installed on the executing device not the one used to compile
Click to expand...
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.
 
Upvote 0

udg

Expert
Licensed User
Longtime User
really?
Click to expand...
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
 
Upvote 0
You must log in or register to reply here.

Similar Threads

Bug? [EDIT: NO BUGS HERE] ExecQuerySingleResult (SQLite)
2
Replies
37
Views
4K
LucaMs
A
  • Question
B4J Question SQLite database ON CONFLICT useage
Replies
4
Views
2K
Adrian Jansen
A
  • Solved
B4J Question [SOLVED] Maddening list bug
2 3
Replies
41
Views
4K
JackKirk
  • Question
B4J Question [SOLVED] OnTouchListener event
Replies
4
Views
3K
Star-Dust
  • Question
B4J Question [SOLVED] Non-UI app AppClose event ?
Replies
1
Views
2K
lemonisdead
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…