Android Question SQLite version...

Kevin Hartin said:

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

Click to expand...

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 & ") "

Alex_197 said:

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 & ") "

Click to expand...

Kevin Hartin said:

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...

Click to expand...

Mahares said:

B4A has nothing to do with Sqlite version. It accesses whatever version came with your device OS

Click to expand...

Alex_197 said:

The error message tells you that the record with the same ID is already exists.

Click to expand...