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

**URL:** <https://forum.inductiveautomation.com/t/how-do-you-catch-a-primary-key-constraint-when-using-a-stored-procedure/84978>\
**Category:** Ignition\
**Tags:** database, scripting\
**Created:** [January 30, 2024, 8:43pm UTC](https://forum.inductiveautomation.com/t/how-do-you-catch-a-primary-key-constraint-when-using-a-stored-procedure/84978 "2024-01-30T20:43:24Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![dis](https://avatars.discourse-cdn.com/v4/letter/d/e99b99/32.png) [@dis](https://forum.inductiveautomation.com/u/dis)\
**Post date:** [January 30, 2024, 8:43pm UTC](https://forum.inductiveautomation.com/t/how-do-you-catch-a-primary-key-constraint-when-using-a-stored-procedure/84978/1 "2024-01-30T20:43:24Z")

</div>

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:

```auto
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. ?

---

<div class="post-metadata">

**Author:** ![Steve\_Laubach](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steve_laubach/32/60900_2.png) [@Steve\_Laubach](https://forum.inductiveautomation.com/u/Steve_Laubach)\
**Post date:** [January 30, 2024, 8:55pm UTC](https://forum.inductiveautomation.com/t/how-do-you-catch-a-primary-key-constraint-when-using-a-stored-procedure/84978/2 "2024-01-30T20:55:12Z")

</div>

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.

> **[TRY...CATCH (Transact-SQL) - SQL Server](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver16)**
>
> TRY...CATCH (Transact-SQL)

---

<div class="post-metadata">

**Author:** ![bspangler](https://avatars.discourse-cdn.com/v4/letter/b/53a042/32.png) [@bspangler](https://forum.inductiveautomation.com/u/bspangler)\
**Post date:** [January 30, 2024, 9:12pm UTC](https://forum.inductiveautomation.com/t/how-do-you-catch-a-primary-key-constraint-when-using-a-stored-procedure/84978/3 "2024-01-30T21:12:47Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [January 30, 2024, 9:31pm UTC](https://forum.inductiveautomation.com/t/how-do-you-catch-a-primary-key-constraint-when-using-a-stored-procedure/84978/4 "2024-01-30T21:31:49Z")

</div>

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](https://www.automation-pros.com/ignition/later.py) helper script.

---

<div class="post-metadata">

**Author:** ![dis](https://avatars.discourse-cdn.com/v4/letter/d/e99b99/32.png) [@dis](https://forum.inductiveautomation.com/u/dis)\
**Post date:** [January 31, 2024, 2:06pm UTC](https://forum.inductiveautomation.com/t/how-do-you-catch-a-primary-key-constraint-when-using-a-stored-procedure/84978/5 "2024-01-31T14:06:55Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [January 31, 2024, 3:30pm UTC](https://forum.inductiveautomation.com/t/how-do-you-catch-a-primary-key-constraint-when-using-a-stored-procedure/84978/6 "2024-01-31T15:30:12Z")

</div>

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 -

```auto
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

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

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [January 31, 2024, 4:09pm UTC](https://forum.inductiveautomation.com/t/how-do-you-catch-a-primary-key-constraint-when-using-a-stored-procedure/84978/7 "2024-01-31T16:09:02Z")

</div>

> [@dis](#):
>
> Caused by: com.microsoft.sqlserver.jdbc.SQLServerException

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.

---

<div class="post-metadata">

**Author:** ![dis](https://avatars.discourse-cdn.com/v4/letter/d/e99b99/32.png) [@dis](https://forum.inductiveautomation.com/u/dis)\
**Post date:** [January 31, 2024, 5:32pm UTC](https://forum.inductiveautomation.com/t/how-do-you-catch-a-primary-key-constraint-when-using-a-stored-procedure/84978/8 "2024-01-31T17:32:47Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [January 31, 2024, 7:38pm UTC](https://forum.inductiveautomation.com/t/how-do-you-catch-a-primary-key-constraint-when-using-a-stored-procedure/84978/9 "2024-01-31T19:38:41Z")

</div>

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

```auto
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/6/e632907c53ab51df855ef55143aa544bbfdeede2.png)

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

```auto
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 -

```auto
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

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

---

<div class="post-metadata">

**Author:** ![dis](https://avatars.discourse-cdn.com/v4/letter/d/e99b99/32.png) [@dis](https://forum.inductiveautomation.com/u/dis)\
**Post date:** [January 31, 2024, 8:09pm UTC](https://forum.inductiveautomation.com/t/how-do-you-catch-a-primary-key-constraint-when-using-a-stored-procedure/84978/10 "2024-01-31T20:09:33Z")

</div>

> [@bkarabinchak.psi](#):
>
> ```python
> 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))
> 
> ```

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?

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [January 31, 2024, 8:14pm UTC](https://forum.inductiveautomation.com/t/how-do-you-catch-a-primary-key-constraint-when-using-a-stored-procedure/84978/11 "2024-01-31T20:14:48Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [January 31, 2024, 8:44pm UTC](https://forum.inductiveautomation.com/t/how-do-you-catch-a-primary-key-constraint-when-using-a-stored-procedure/84978/12 "2024-01-31T20:44:46Z")

</div>

> [@paul-griffith](#):
>
> the mistake was made of directly throwing Java exceptions to scripts instead of adapting them to nicer-to-work-with Python exceptions.

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.