Android Question SQLite version...

Kevin Hartin

Active Member
Licensed User
I have an SQL query that runs fine on my SQLite database when I use SQLiteStudio 3.2.1 on my PC, but it fails when it is run on the same database in an android app. I believe it may be Version related

The error returned refers to the "ON CONFLICT" aspect of the query.
SQL Error:
android.database.sqlite.SQLiteException: near "ON": syntax error (code 1 SQLITE_ERROR): , while compiling: INSERT INTO meeting_users (ID,MeetingID,UserID,MeetingUserStatus,TS) VALUES ('100001','100001','10002','ACTIVE','2021-02-13 19:15:37') ON CONFLICT(ID) DO UPDATE SET ID = '100001',MeetingID = '100001',UserID = '10002',MeetingUserStatus = 'ACTIVE',TS = '2021-02-13 19:15:37';

When I query the SQLite Database version in the App I get 3.22.0, but on the PC it returns 3.24.0

I see references to slite-jdbc-3.34.0.jar in the forum, but cannot see how I can specify what jar or version to use.

Thank,
Kev
 

Alex_197

Well-Known Member
Licensed User
Longtime User
I have an SQL query that runs fine on my SQLite database when I use SQLiteStudio 3.2.1 on my PC, but it fails when it is run on the same database in an android app. I believe it may be Version related

The error returned refers to the "ON CONFLICT" aspect of the query.
SQL Error:
android.database.sqlite.SQLiteException: near "ON": syntax error (code 1 SQLITE_ERROR): , while compiling: INSERT INTO meeting_users (ID,MeetingID,UserID,MeetingUserStatus,TS) VALUES ('100001','100001','10002','ACTIVE','2021-02-13 19:15:37') ON CONFLICT(ID) DO UPDATE SET ID = '100001',MeetingID = '100001',UserID = '10002',MeetingUserStatus = 'ACTIVE',TS = '2021-02-13 19:15:37';

When I query the SQLite Database version in the App I get 3.22.0, but on the PC it returns 3.24.0

I see references to slite-jdbc-3.34.0.jar in the forum, but cannot see how I can specify what jar or version to use.

Thank,
Kev
Did you read the error message? It says that you already have a record with ID '100001'

So just change your query to something like this

B4X:
MySQL="insert into tblUsers (UserID,UserName,Password,RegKey, Created,Active) select ?,?,?,?,?,? "
MySQL=MySQL & "where not exists(select * from tblUsers where UserID=" & UserID
MySQL=MySQL & " and  UserName='" & UserName & "' and Password='" & Password & "' and RegKey='" & RegKey & "' and Created='" & Created & "' and Active=" & True & ") "
 
Upvote 0

Kevin Hartin

Active Member
Licensed User
Did you read the error message? It says that you already have a record with ID '100001'

So just change your query to something like this

B4X:
MySQL="insert into tblUsers (UserID,UserName,Password,RegKey, Created,Active) select ?,?,?,?,?,? "
MySQL=MySQL & "where not exists(select * from tblUsers where UserID=" & UserID
MySQL=MySQL & " and  UserName='" & UserName & "' and Password='" & Password & "' and RegKey='" & RegKey & "' and Created='" & Created & "' and Active=" & True & ") "
Thanks, but I already have a working solution, I was more interested in being able to use the full SQL command set available in the later versions and why B4A uses an older version...
 
Upvote 0

Alex_197

Well-Known Member
Licensed User
Longtime User
Thanks, but I already have a working solution, I was more interested in being able to use the full SQL command set available in the later versions and why B4A uses an older version...
The error message tells you that the record with the same ID is already exists.
 
Upvote 0
Top