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

**URL:** <https://forum.inductiveautomation.com/t/using-try-except-to-dummy-proof-check-if-sql-tables-columns-exist/26140>\
**Category:** Ignition\
**Created:** [June 27, 2019, 1:15pm UTC](https://forum.inductiveautomation.com/t/using-try-except-to-dummy-proof-check-if-sql-tables-columns-exist/26140 "2019-06-27T13:15:44Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![ammermanbrittanyf](https://avatars.discourse-cdn.com/v4/letter/a/45deac/32.png) [@ammermanbrittanyf](https://forum.inductiveautomation.com/u/ammermanbrittanyf)\
**Post date:** [June 27, 2019, 1:15pm UTC](https://forum.inductiveautomation.com/t/using-try-except-to-dummy-proof-check-if-sql-tables-columns-exist/26140/1 "2019-06-27T13:15:44Z")

</div>

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.

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

```

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

```

---

<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:** [June 27, 2019, 1:21pm UTC](https://forum.inductiveautomation.com/t/using-try-except-to-dummy-proof-check-if-sql-tables-columns-exist/26140/2 "2019-06-27T13:21:10Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ammermanbrittanyf](https://avatars.discourse-cdn.com/v4/letter/a/45deac/32.png) [@ammermanbrittanyf](https://forum.inductiveautomation.com/u/ammermanbrittanyf)\
**Post date:** [June 27, 2019, 1:28pm UTC](https://forum.inductiveautomation.com/t/using-try-except-to-dummy-proof-check-if-sql-tables-columns-exist/26140/3 "2019-06-27T13:28:48Z")

</div>

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?

---

<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:** [June 27, 2019, 1:43pm UTC](https://forum.inductiveautomation.com/t/using-try-except-to-dummy-proof-check-if-sql-tables-columns-exist/26140/4 "2019-06-27T13:43:59Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![ammermanbrittanyf](https://avatars.discourse-cdn.com/v4/letter/a/45deac/32.png) [@ammermanbrittanyf](https://forum.inductiveautomation.com/u/ammermanbrittanyf)\
**Post date:** [June 27, 2019, 1:45pm UTC](https://forum.inductiveautomation.com/t/using-try-except-to-dummy-proof-check-if-sql-tables-columns-exist/26140/5 "2019-06-27T13:45:07Z")

</div>

I’ll try this. Thank you!

---

<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:** [June 27, 2019, 1:49pm UTC](https://forum.inductiveautomation.com/t/using-try-except-to-dummy-proof-check-if-sql-tables-columns-exist/26140/6 "2019-06-27T13:49:17Z")

</div>

> [@ammermanbrittanyf](#):
>
> File "module:project.SQL", line 99, in deleteCurrentTable

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

---

<div class="post-metadata">

**Author:** ![ammermanbrittanyf](https://avatars.discourse-cdn.com/v4/letter/a/45deac/32.png) [@ammermanbrittanyf](https://forum.inductiveautomation.com/u/ammermanbrittanyf)\
**Post date:** [June 27, 2019, 1:51pm UTC](https://forum.inductiveautomation.com/t/using-try-except-to-dummy-proof-check-if-sql-tables-columns-exist/26140/7 "2019-06-27T13:51:05Z")

</div>

ah yes. thank you for catching that as well