How do you 'catch' a primary key constraint when using a stored procedure

Ignition
,
1

Disclaimer: My python/jython skills aren't great and the search engines aren't helping. ChatGPT didn't get me where I wanted.

I have the following script:

def LoadWrkTable():
	try:
		call = system.db.createSProcCall("dbo.Load_Wrk_Orders_ItemMast", db_name)
		call.registerInParam(1, system.db.DATE,  system.date.format(system.date.now(), 'yyyy-MM-dd HH:mm:ss'))
		call.registerInParam(2, system.db.VARCHAR,'Host')
		system.db.execSProcCall(call)
 	except java.sql.SQLIntegrityConstraintViolationException:
	    print("Primary key violation! Skipping row")
	except Exception as e:
	    print("Error details:", str(e))

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'PK_Wrk_ShopOrd_ItmMaster'. Cannot insert duplicate key in object 'dbo.Wrk_ShopOrd_ItmMaster'. The duplicate key value is (304976, Jan 30 2024 12:00AM).

This is going to happen and I want to be able to catch the error and skip it. ChatGPT said to use
java.sql.SQLIntegrityConstraintViolationException but that's not catching it.

Since I don't know enough about the programming language there's a couple things I'm asking.

  1. How do I learn how to 'translate' the error message and map the correct type of error to catch? Neither excepts are triggered.
  2. Any other tips/websites etc. ?
2

Since you're calling a stored procedure you could simply check to make sure the record doesn't exist before inserting it.

You can also use a try/catch statement and return something to Ignition that you can handle elegantly.

3 
from java.lang import Exception as JException

try:
	q = "SELECT 1 + 'a'"
	system.db.runQuery(q)
except Exception, ex:
	print 'Python Exception'
	print type(ex)
except JException, ex:
	print 'Java Exception!'
	print type(ex)

SQL errors don't seem to get caught by Exception.

4

You should use java.lang.Throwable, to catch java errors as well as exceptions. And you won't have to rename it.

The issue is that python's Exception is totally separate from java's Exception. Python exceptions are not java throwables at all.

Side note: Ignition's loggers will produce very nice backtraces if you give them a Throwable. For python exceptions, you can create a "fake" throwable with PythonAsJavaException from my later.py helper script.

2 Likes
5

Since Ignition doesn't catch the error, I'm going to do the right thing and modify the stored procedure (Insert based on a select statement 'table1' with an inner join 'table2' to 'table1') to ignore existing records and not throw an error.

6

You can catch the error in Ignition you are just trying to catch the wrong one it seems if it is still bubbling up.

ChatGPT said to use
java.sql.SQLIntegrityConstraintViolationException but that's not catching it.

Here's where you went wrong. Just inspect the error directly and figure out what it is for yourself, it's quickly done.

Do the following -

import java.lang.Throwable

def LoadWrkTable():
	try:
		call = system.db.createSProcCall("dbo.Load_Wrk_Orders_ItemMast", db_name)
		call.registerInParam(1, system.db.DATE,  system.date.format(system.date.now(), 'yyyy-MM-dd HH:mm:ss'))
		call.registerInParam(2, system.db.VARCHAR,'Host')
		system.db.execSProcCall(call)
 	except java.lang.Throwable:
	    print(str(e))
        print(str(e.cause))
	except Exception as e:
	    print("Error details:", str(e))

Now trigger the primary key issue. You should be able to check out the contents of e or e.cause and determine if it's an error you expect and don't mind or not.

Here is a simple example I use for catching if I violate a unique constraint

import java.lang.Exception

try:
	system.db.runUpdateQuery("INSERT INTO someTable(name) VALUES ('someName')")
except java.lang.Throwable, e:
	print str(e)
	print e.cause
	if 'Duplicate entry' in str(e.cause):
		print 'expected and ok'
	else:
		raise e

The above checks the error message, if it detects the warning is about a "Duplicate entry" which gets thrown from a uniqueness constraint violation, then it's ok with silencing that error, otherwise though it will raise the error and bubble it up as it's unexpected. Yours should be similar - but you will have to check what the contents of e.cause are so you can find the appropriate error you get so you can look for that and do a pass and otherwise raise.

7

The error you want to catch is literally right there.

That said, one complication that could be arising is that if you're calling this script in the client or designer, you're not getting the "real" exception; you're getting a fake exception we dress up for you, since the real exception is only available within the JDBC driver classloader on the gateway. So catching it is slightly more convoluted.

1 Like
8

And that's where my surface level knowledge ends. I'm assuming I need to Import
com.microsoft.sqlserver.jdbc in my code in order to be able to reference it?

The original code is written in VB and the more I get into it the more I find that several people have written their own versions of handling data.

  • delete everything in the table before re-populating it
  • use a select statement then iterate through the recordset and delete the records
  • Call a stored procedure to delete the records that will be inserted
  • Insert the records and catch the primary key errors and ignore

My decision going forward is to re-write using simple and easier to understand logic. I'm using named queries where possible and fixing the possibly troublesome stored procedures by preventing the PK collisions in the SP or abandon the SP and create a named query.

This is so other facilities can import the code and use it as is.

Thank you everyone for their input. I have some new knowledge that I can using going forward.

9

Just to clear up what @PGriffith mentioned with an example - I am using the following code that will fail the query due to a a UNIQUNESS constraint.

def throwsError():
	import java.lang.Throwable
	logger = system.util.getLogger("TEST")
	try:
		system.db.runUpdateQuery("INSERT INTO listcustomers (name, companyTypeId) VALUES ('Atlas', 1)")
	except java.lang.Throwable, e:
		logger.error(str(e))
		logger.error(str(e.cause))

When I run it on the gateway via a message handler or a tag change even I see the actual error fro the JBDC (the database connection library) -

image
image1173×74 6.26 KB

This is what you saw - so you must be running that function on gateway context via a timer script or tag change or message handler etc. In this scenario, yes you should be able to do something like

import com.microsoft.sqlserver.jdbc.SQLServerException
try:
    #something
except com.microsoft.sqlserver.jdbc.SQLServerException:
     pass #we don't mind this type of error

There is a catch here. If you now take this function, put it on a button in vision and try to run it, you'll get an import error that there is no microsoft package. Because that doesn't exist in the local client scope. The error you get from running it locally looks like this -

14:26:49.164 [SwingWorker-pool-1-thread-1] ERROR TEST - java.lang.Exception: Error executing system.db.runUpdateQuery(INSERT INTO listcustomers (name, companyTypeId) VALUES ('Atlas', 1), , , false)
14:26:49.164 [SwingWorker-pool-1-thread-1] ERROR TEST - com.inductiveautomation.ignition.client.gateway_interface.GatewayException: SQL error for "INSERT INTO listcustomers (name, companyTypeId) VALUES ('Atlas', 1)": (conn=661) Duplicate entry 'Atlas' for key 'Name'

Note that this time the error comes through as com.inductiveautomation.ignition.client.gateway_interface.GatewayException. So even if you could import com.microsoft.blah.blah you wouldn't catch it on your button press in vision.

That's why my suggested solution is to use java.lang.Throwable. All java errors (which all db thrown errors are) are sublcasses of java.lang.Throwable so except java.lang.Throwable, e will catch it regardless of it is in your gateway scope or your vision\designer scope. Notice too that the error text (outside of the error name) is identical in the e.cause - SQL error for "INSERT INTO listcustomers (name, companyTypeId) VALUES ('Atlas', 1)": (conn=661) Duplicate entry 'Atlas' for key 'Name'.

So doing something like

def LoadWrkTable():
	try:
		call = system.db.createSProcCall("dbo.Load_Wrk_Orders_ItemMast", db_name)
		call.registerInParam(1, system.db.DATE,  system.date.format(system.date.now(), 'yyyy-MM-dd HH:mm:ss'))
		call.registerInParam(2, system.db.VARCHAR,'Host')
		system.db.execSProcCall(call)
 	except java.lang.Throwable, e:
        if "Cannot insert duplicate key in object" in str(e.cause):
            pass
        else:
            raise e
	except Exception as e:
	    print("Error details:", str(e))

The above will now work regardless of where you call it from because you're not relying on the jbdc error which you only get on the gateway, but by inspecting the error text itself which is identical in both scopes, and your java.lang.Throwable catches the error in both scopes.

3 Likes
10

That works. I did not know you could essentially search in the error string. So java.lang.Throwable acts like a catch all for 'external' errors?

11

Throwable is the base class of all Java exceptions (caught or uncaught, programming errors or program errors).
Jython doesn't (can't, for performance reasons) automatically adapt Java exceptions into Python exceptions. If you were working in pure Python/Jython, this wouldn't matter.
Unfortunately for you, Ignition's codebase is all written in Java, and long, long, long ago the mistake was made of directly throwing Java exceptions to scripts instead of adapting them to nicer-to-work-with Python exceptions.

So now, in lots and lots of different places in Ignition, you have to catch both even for expected failures.

1 Like
12

I disagree that it is a mistake. Having un-coerced java exceptions is necessary for loggers and for following cause chains. I'd rather you didn't amputate that functionality.

1 Like