Using Try/Except to Dummy Proof & Check if SQL Tables/Columns Exist

Hello. I am currently working on Dummy Proofing a program that I am writing. I am trying to check if a SQL table exists. If it doesn’t exist, I want to create the table. If the table does exist, I want to check to make sure all the required columns exist in the table. I am trying to do this with the following code, but when I run the code when I know they SQL table doesn’t exist, I get a Python error.

Thanks in advance.

def checkTables(fileList, departmentList, individualTables, masterTables, database, masterAttributes):
	exit = False
	if individualTables:
		for department in departmentList:
			TABLENAME = 'FA_FCA_' + department
			try:
				result = system.db.runQuery("SELECT * FROM " + TABLENAME + " WHERE 1 = 0")
				system.gui.messageBox("Error: " + attribute + " table does not exist in SQL")
				exit = True
			except:
				for attribute in masterAttributes.attributes:
					try:
						result = result.getColumnIndex("hello")
						system.gui.messageBox("Error: " + attribute + " column is not found in the table")
						exit = True
					except:
						pass
			if exit == True:
				sys.exit()
	if masterTables:
		TABLENAME = 'FA_FCA_MASTER'
		result = system.db.runQuery("SELECT * FROM " + TABLENAME)
		for attribute in masterAttributes.attributes:
			try:
				result.getColumnIndex(attribute)
				system.gui.messageBox("Error: " + attribute + " column is not found in the table")
				exit = True
			except:
				pass
			if exit == True:
				sys.exit()
Error: Traceback (most recent call last):

  File "<event:actionPerformed>", line 62, in <module>

  File "<module:project.SQL>", line 99, in deleteCurrentTable



java.lang.Exception: java.lang.Exception: Error executing system.db.runUpdateQuery(DELETE FROM FA_FCA_ferret, , , false)


	caused by Exception: Error executing system.db.runUpdateQuery(DELETE FROM FA_FCA_ferret, , , false)
	caused by GatewayException: SQL error for "DELETE FROM FA_FCA_ferret": Invalid object name 'FA_FCA_ferret'.
	caused by SQLServerException: Invalid object name 'FA_FCA_ferret'.

Ignition v7.9.6 (b2018012914)
Java: Oracle Corporation 1.8.0_211

See the “caused by” in the error message? You’ll have to follow that chain to find the SQLServerException with the details. Java Exceptions have a .getCause() method that retrieves the next “inner” exception object. Unfortunately, serialization of these exceptions in the path from gateway to client collapses the details to just the strings. You’ll need to run this code in the gateway (possibly via sendRequest) in order to examine the full content of the inner exceptions.

Thank you for that explanation, though I am still confused. I don’t really care what the error is. I just don’t want the error box to pop up, hence the “try” in my code. Am I still able to do this?

Ah, missed that. A bare “except” block doesn’t necessarily catch java exceptions. Use a structure like this:

import java.lang.Exception

try:
    # some code that can throw either java exceptions or python exceptions
except java.lang.Exception, e:
    # Handle the java exception in 'e', possibly using e.cause to drill into the nesting
except Exception, e:
    # Handle the python exception in 'e'.  Note that python doesn't need to import its native exception

I’ll try this. Thank you!

Are you missing a try-catch block at this location? This function is not the one you posted.

ah yes. thank you for catching that as well