Getting better exception description from SQL java.lang.Exception?

Ignition
1

Is there any way to get useful information from a SQL exception, other than just "your query didn't work :/"?

This is what I am getting at the moment, and while I've worked out that I removed one of the fields but forgot to remove a ? as well, it took me a little while to find it. It would have been nice to see some more descriptive error, but is this possible?

Traceback (most recent call last): File "<module:project.gui>", line 47, in add_part_calib Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO dat_calibration_log (measured_length, expected_length, machine_id, t_stamp) VALUES (?,?,?,?,?), [124, 123, 1, Thu Jul 20 16:52:01 ACST 2023], SQLServer, , false, false)
2

In gateway scopes, you can follow the chain of causes back to the error that actually came from the JDBC driver. In Vision/Designer scope, the chain is partly there too, but the JDBC classes don't exist, so the gateway interface stringifies those.

1 Like
3

From a client/designer (I guess this may not work as well via the gateway per Phils point?) point of view if you catch the error in a java.lang.Throwable the e.cause tend to give you a good idea of what went wrong. Copying your example here's what I get if my columns and ? mismatch -

import java.lang.Throwable

try:
	system.db.runPrepQuery("INSERT INTO TestingTable (field1) VALUES (?,?)",['hi','bye'])
except java.lang.Throwable, e:
	print str(e)
	print str(e.cause)

prints

java.lang.Exception: Error executing system.db.runPrepQuery(INSERT INTO TestingTable (field1) VALUES (?,?), , [hi, bye], )
com.inductiveautomation.ignition.client.gateway_interface.GatewayException: (conn=91) Column count doesn't match value count at row 1

Now I see my column count doesn't match.

If you do it without a try/except, the last line is what you see -
java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepQuery(INSERT INTO TestingTable (field1) VALUES (?,?), , [hi, bye], ) which isn't helpful but the caused by: lines above it do give more context as to what actually went wrong -

Caused by: java.lang.Exception: Error executing system.db.runPrepQuery(INSERT INTO TestingTable (field1) VALUES (?,?), , [hi, bye], )
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: (conn=91) Column count doesn't match value count at row 1
Caused by: java.sql.SQLException: (conn=91) Column count doesn't match value count at row 1
4 Likes
4

Awesome! This is the solution that I was looking for.