Perspective named queries DELETE FROM and foreign key conflict

Ignition
, ,
1

Hi,

I've created a named query on a table with a foreign key.
My problem is when I want to delete a row which is used in the other table, the query fails and the script stops. The log in the gateway is the only place where I can see if the query was done or not.
When the foreign key is respected during a the delete query, everythings works well.
How can I get if the query was finished successfully or not in the script ?

The script is in a message handler on a component.

def onMessageReceived(self, payload):
	if payload["value"] >= 0:
		system.db.runNamedQuery(path = "Categories/DeleteCategoryById", parameters = {"id": payload["value"]})
		system.perspective.sendMessage(messageType = "refreshCategoriesList")

Thank you

2

When working with named queries in Ignition that involve operations on tables with foreign key constraints, specifically SQL foreign key errors. These are typically Java exceptions, so you'll need to handle them using Java's exception classes rather than Python's.

Here is an Example:

from java.lang import Exception as JavaException

try:
Result = system.db.runNamedQuery('Query')
except JavaException as e:
system.perspective.print(str(e))

After you capture it, you can put whatever code you need in the exception part to handle the error or log it appropriately. So if the exception block is never reached then its assumed it was successful.

You can take it a step further to setup error catching on the database side and return specific strings or integers depending on the error and use that instead. So you could setup a string response like "Cannot Delete Item because of Foreign Key Conflict" and show that on a popup for the user.

1 Like
3

Thanks !