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