SQLite ON CONFLICT query

Ignition
, , ,
1

Hi all,

for customer trial purpose, i used SQLite for Database

I have problem with INSERT ON CONFLICT DO UPDATE with SQLite, when there is no conflict ,then the query works normally, but when conflict happen because of unique constraint, the ON CONFLICT does'nt work and throw an error..

    import java.lang.Exception
	
	txId = system.db.beginTransaction(timeout=5000)
	
	try:
		excelFile = event.file.getBytes()
		res = system.dataset.fromExcel(excelFile, headerRow=0, sheetNumber=0)
		data = Convert.toJson(res)
		
		query = """INSERT INTO myTable(orders, earl_start_date) 
		VALUES(?, ?)
		ON CONFLICT (orders)
		DO UPDATE SET 
		orders=?, earl_start_date=?"""
		
		for item in data:
			args = [item["Order"], system.date.toMillis(item["Earl.start date"])]*2
		    system.db.runPrepUpdate(query, args, tx=txId)
			
		system.db.commitTransaction(txId)	
	except java.lang.Exception, err:
		system.perspective.sendMessage(
		    'toastMessage', 
		    payload={"text": "There is problem with database query \n\n" + str(err), "level": "error"}, 
		    scope='session'
		)
		system.db.rollbackTransaction(txId)
	
	else:
		self.getSibling("Table").props.data = data
	finally:
		system.db.closeTransaction(txId)

any idea?

Regards

2

Why not try your insert, catch the error if there is one since you have a unique constraint and do your update in there? You can follow this logic of this post

3

Don't use SQLite.

  • It is not suitable for production use, due to its single-threaded implementation, so any "trial" isn't valid.
  • It doesn't support SQL and various SQL datatypes as well as any of the other common choices.
  • It is not the only free DB you can use. PostgreSQL and MariaDB are much better choices for any trial.

(PostgreSQL, in particular, has the best ON CONFLICT implementation on the market. You should use it.)

1 Like
4

for SQLite, there is no "Duplicate" word in the error message..

5

using PostgreSQL is to overkill for PoC, its just small application, for production i will use PostgreSQL or MS SQL for sure

6

What error message do you get from trying to insert a duplicate?

I would tend to agree with @pturmel here - setting up a real database doesn't take more than 15 minutes of time and you will get a lot more for it. If you continue with SQLLite syntax like INSERT ... ON CONFLICT UPDATE which SQL Server does not have, you will end up needing to rewrite your queries too - probably more work than just making an instance of SQL Server Express.

7

Well, that is one of the many reasons to not use SQL Server. (Particularly since it has no deterministic alternative.)

No excuse. You can spin up a docker instance of Postgres in seconds.

8

for sure i will use Postgesql, but i got it working, dont know why this is working compared to the first code

    import java.lang.Exception
	
	txId = system.db.beginTransaction(timeout=5000)
	
	excelFile = event.file.getBytes()
	res = system.dataset.fromExcel(excelFile, headerRow=0, sheetNumber=0)
	data = Convert.toJson(res)
	
	query = """INSERT INTO myTable(orders, earl_start_date) 
	VALUES(?, ?)
	ON CONFLICT (orders)
	DO UPDATE SET 
	orders=?, earl_start_date=?"""
	
	try:
		for item in data:
			args = [item["Order"], system.date.toMillis(item["Earl.start date"])] * 2
			tester = system.db.runPrepUpdate(query, args, tx=txId)

		system.db.commitTransaction(txId)	
	except java.lang.Exception, err:
		system.perspective.sendMessage(
		    'toastMessage', 
		    payload={"text": "There is problem with database query \n\n" + str(err), "level": "error"}, 
		    scope='session'
		)
		system.db.rollbackTransaction(txId)
	else:
		self.getSibling("Table").props.data = data
	finally:
		system.db.closeTransaction(txId)

move most of the code out of the try catch block solved the issue..