Catch SQL exceptions in code

Ignition
1

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.

2

If you follow the chain of “caused by” you’ll get to the underlying SQL Exception. See .getCause().

3

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?

4

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.

5

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

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.

6

Something like this:

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.

2 Likes
7

My code

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

8

Seems like even though looping the causes of the raised java.lang.Exception ends with the MySQLIntegrityConstraintViolationException, which, according to docs, should be a subclass of SQLException, any comparison I can think of (isinstance(e, SQLException), issublcass(e.getClass(), SQLException)) results in False result.

9

Only thing that seems to work is

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 :slight_smile:)

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
10

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

11

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

12

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:

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:

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.

3 Likes
13

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

14

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

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.

15

Thanks @cesrom this worked for me.