# Catch SQL exceptions in code

**URL:** <https://forum.inductiveautomation.com/t/catch-sql-exceptions-in-code/17793>\
**Category:** Ignition\
**Created:** [April 21, 2018, 12:09am UTC](https://forum.inductiveautomation.com/t/catch-sql-exceptions-in-code/17793 "2018-04-21T00:09:37Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![Claus\_Nielsen](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Claus\_Nielsen](https://forum.inductiveautomation.com/u/Claus_Nielsen)\
**Post date:** [April 21, 2018, 12:09am UTC](https://forum.inductiveautomation.com/t/catch-sql-exceptions-in-code/17793/1 "2018-04-21T00:09:37Z")

</div>

Is it just me, or is all exceptions from `system.db.runXXXXX` methods raised as `java.lang.Exception`? Would be really nice to be able to catch e.g. exceptions like constraint violation exceptions without catching SQL errors.

When running queries from the query browser they are raised properly, just seems like in scripts they are all the same.

---

<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:** [April 21, 2018, 3:13am UTC](https://forum.inductiveautomation.com/t/catch-sql-exceptions-in-code/17793/2 "2018-04-21T03:13:33Z")

</div>

If you follow the chain of “caused by” you’ll get to the underlying SQL Exception. See [.getCause()](https://docs.oracle.com/javase/8/docs/api/java/lang/Throwable.html#getCause--).

---

<div class="post-metadata">

**Author:** ![Claus\_Nielsen](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Claus\_Nielsen](https://forum.inductiveautomation.com/u/Claus_Nielsen)\
**Post date:** [April 21, 2018, 10:31am UTC](https://forum.inductiveautomation.com/t/catch-sql-exceptions-in-code/17793/3 "2018-04-21T10:31:55Z")

</div>

Thank you, tried that. However, this boils down to a `com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException` which I can not compare to, as trying to import this in my script gives me the error

> import error, no module named 'mysql'.

I know there must be something I can do to be able to import this, just can't keep thinking that's a lot of work done trying to catch a simple error?

---

<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:** [April 21, 2018, 3:39pm UTC](https://forum.inductiveautomation.com/t/catch-sql-exceptions-in-code/17793/4 "2018-04-21T15:39:20Z")

</div>

You can’t import these. Even in the gateway, they’re in isolated classloaders. Fortunately, JDBC requires all driver exceptions to extend SQLException, which you can catch or find in the cause hierarchy, and then examine the exception’s reason and simple classname for further decisions.

---

<div class="post-metadata">

**Author:** ![Claus\_Nielsen](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Claus\_Nielsen](https://forum.inductiveautomation.com/u/Claus_Nielsen)\
**Post date:** [April 21, 2018, 6:22pm UTC](https://forum.inductiveautomation.com/t/catch-sql-exceptions-in-code/17793/5 "2018-04-21T18:22:00Z")

</div>

Must be tired, can’t seem to figure this out 🙂

Would really appreciate if you would make an example catching e.g. the constraint violation exception.  
Tried catching the SQLException, this is just ignored and catching the java.lang.Exception just boils down to the MySQLIntegrityConstraintViolationException when looking at the causes.

Writing some SQL deleting from a table which can be referenced from a lot of other tables and really don’t want to write a query that checks these constraints when I know I can just catch an 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:** [April 21, 2018, 6:36pm UTC](https://forum.inductiveautomation.com/t/catch-sql-exceptions-in-code/17793/6 "2018-04-21T18:36:39Z")

</div>

Something like this:

```auto
import java.lang.Exception
from java.sql import SQLException

try:
  # whatever
  pass
except java.lang.Exception, e:
  # Find SQL cause
  sqle = e
  while sqle:
    if SQLException.isInstance(sqle):
      break
    sqle = sqle.cause
  if sqle:
    # Look closer at the SQL Exception
    print sqle.reason, sqle.getClass().simpleName
  else:
    # rethrow non-sql exceptions
    raise e

```

EDIT:  
Fixed `.isinstance()` =\> `.isInstance()`. Java method for [class objects](file:///usr/share/doc/java-sdk-docs-1.8/html/api/java/lang/Class.html), fwiw.

---

<div class="post-metadata">

**Author:** ![Claus\_Nielsen](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Claus\_Nielsen](https://forum.inductiveautomation.com/u/Claus_Nielsen)\
**Post date:** [April 21, 2018, 6:44pm UTC](https://forum.inductiveautomation.com/t/catch-sql-exceptions-in-code/17793/7 "2018-04-21T18:44:55Z")

</div>

My code

```auto
try:
	
	system.db.runPrepUpdate("DELETE FROM test WHERE id = ?", [tid])
			
except (java.lang.Exception), e:
	sqle = e
	while sqle:
		if isinstance(sqle, SQLException):
			break
		sqle = sqle.cause
	if sqle:
		print "THE REASON:", sqle.reason, "THE CLASS:", sqle.getClass().simplename
	else:
		raise e

```

This reraises the exception at `raise e`. Seems like SQLException isn’t found.

In your example you used `SQLException.isinstance(sqle)`, this gives SQLException doesn’t have attribute ‘isinstance’ error.

EDIT: Fixed copye paste indent errors

---

<div class="post-metadata">

**Author:** ![Claus\_Nielsen](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Claus\_Nielsen](https://forum.inductiveautomation.com/u/Claus_Nielsen)\
**Post date:** [April 21, 2018, 7:25pm UTC](https://forum.inductiveautomation.com/t/catch-sql-exceptions-in-code/17793/8 "2018-04-21T19:25:28Z")

</div>

Seems like even though looping the causes of the raised java.lang.Exception ends with the MySQLIntegrityConstraintViolationException, which, according to [docs](http://www.docjar.com/docs/api/com/mysql/jdbc/exceptions/MySQLIntegrityConstraintViolationException.html), should be a subclass of SQLException, any comparison I can think of (isinstance(e, SQLException), issublcass(e.getClass(), SQLException)) results in False result.

---

<div class="post-metadata">

**Author:** ![Claus\_Nielsen](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Claus\_Nielsen](https://forum.inductiveautomation.com/u/Claus_Nielsen)\
**Post date:** [April 21, 2018, 7:40pm UTC](https://forum.inductiveautomation.com/t/catch-sql-exceptions-in-code/17793/9 "2018-04-21T19:40:47Z")

</div>

Only thing that seems to work is

```auto
if "MySQLIntegrityConstraintViolationException" in str(sqle):

```

EDIT: Below seems to solve my problem until I figure out the right way to catch that error (probably never unless someone provide a solution 🙂)

```auto
try:
	system.db.runPrepUpdate("DELETE FROM test WHERE id = ?", [tid])
			
except (java.lang.Exception), e:
	while e:
		if "MySQLIntegrityConstraintViolationException" in str(e):
			break
		e = e.getCause()
	else:
		raise

```

---

<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:** [April 21, 2018, 8:14pm UTC](https://forum.inductiveautomation.com/t/catch-sql-exceptions-in-code/17793/10 "2018-04-21T20:14:04Z")

</div>

> [@Claus\_Nielsen](#):
>
> In your example you used SQLException.isinstance(sqle), this gives SQLException doesn't have attribute 'isinstance' error.

Whoops. Typo. Should be `SQLException.isInstance(sqle)`. Sorry.

---

<div class="post-metadata">

**Author:** ![Claus\_Nielsen](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Claus\_Nielsen](https://forum.inductiveautomation.com/u/Claus_Nielsen)\
**Post date:** [April 21, 2018, 8:17pm UTC](https://forum.inductiveautomation.com/t/catch-sql-exceptions-in-code/17793/11 "2018-04-21T20:17:12Z")

</div>

Also returns False when comparing to the Mysql…very.long.exception.name…ViolationException

---

<div class="post-metadata">

**Author:** ![cesrom](https://avatars.discourse-cdn.com/v4/letter/c/ea666f/32.png) [@cesrom](https://forum.inductiveautomation.com/u/cesrom)\
**Post date:** [May 2, 2018, 7:59pm UTC](https://forum.inductiveautomation.com/t/catch-sql-exceptions-in-code/17793/12 "2018-05-02T19:59:54Z")

</div>

In my test I am using MS SQL Server, and although `com.microsoft.sqlserver.jdbc.SQLServerException` extends `java.sql.SQLException`, the exception in Ignition is never an instance of `SQLException`.

Sample code:

```auto
from java.lang import Exception as JException

try:
    # Something to try.
except JException, e:
    sqle = e
    while sqle:
        print 'Class: %s. Cause: %s' % (sqle. __class__. __name__ , sqle.cause)
        sqle = sqle.cause

```

The output:

```auto
Class: Exception. Cause: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Parameter x was not defined for stored procedure y.
Class: GatewayException. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Parameter x was not defined for stored procedure y.
Class: GatewayResponse$GatewayThrowable. Cause: None

```

In my case, the inner most exception is never an instance of `SQLException` but an instance of the inner class `GatewayThrowable`. Not sure if this is by design or if this is unintended behavior.

It would be nice, though, to be able to retrieve the actual instance of the 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:** [May 2, 2018, 8:11pm UTC](https://forum.inductiveautomation.com/t/catch-sql-exceptions-in-code/17793/13 "2018-05-02T20:11:28Z")

</div>

> [@cesrom](#):
>
> In my case, the inner most exception is never an instance of SQLException but an instance of the inner class GatewayThrowable. Not sure if this is by design or if this is unintended behavior.

That's interesting. The true cause exception probably can't be deserialized outside the gateway since the JDBC driver classes aren't loaded elsewhere.

---

<div class="post-metadata">

**Author:** ![Claus\_Nielsen](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Claus\_Nielsen](https://forum.inductiveautomation.com/u/Claus_Nielsen)\
**Post date:** [May 2, 2018, 8:12pm UTC](https://forum.inductiveautomation.com/t/catch-sql-exceptions-in-code/17793/14 "2018-05-02T20:12:29Z")

</div>

Only solution I found that is able to catch the exception is (using MySQL off course)

```auto
while e:
	if "MySQLIntegrityConstraintViolationException" in str(e):
		break
	e = e.getCause()
else:
	raise

```

Also, the only way I found to get the actual exception type is using str(e) which will resolve into, in my case, the MySQL exception, just printing/checking against the class name never shows any of the causes as a MySQL exception, just gateway exceptions.

---

<div class="post-metadata">

**Author:** ![mori.luca](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mori.luca/32/13424_2.png) [@mori.luca](https://forum.inductiveautomation.com/u/mori.luca)\
**Post date:** [February 25, 2020, 2:22pm UTC](https://forum.inductiveautomation.com/t/catch-sql-exceptions-in-code/17793/15 "2020-02-25T14:22:41Z")

</div>

Thanks @cesrom this worked for me.