Android Question SQLite version...

[Kevin Hartin]

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.

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]

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.

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

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

 

[Kevin Hartin]

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

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

 

[Mahares]

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

 

[Alex_197]

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.

 

[OliverA]

@Mahares is correct. For a possible workaround see https://www.b4x.com/android/forum/threads/sqlite3-fts5.110369/post-688824

 

[Kevin Hartin]

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

Ok, so sqlite is an OS thing rather than a B4A thing....

 

[Kevin Hartin]

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

Yes, that's why I was using an Upsert query, which works on a windows desktop, but not on android.

 

[calloatti]

There are other options, see https://stackoverflow.com/questions/15277373/sqlite-upsert-update-or-insert