I have written a short script which builds an SQL query and displays the results in a table. This works fine when I hardcode the full name of the table into the script. However, when I set this up with a custom property I an error. Can any one explain why this doesn’t work please?
I have two functions using the exact same code/popups etc. with the only difference being the table names in the database. I was hoping to use custom properties to point the templates at different sets of tables.
Thanks
This works:
ptableTime = event.source.parent.getComponent('timeTable')
tableTime = "hamjet_report.dbo.FAB_HT_Time_Data"
queryMain = "SELECT * FROM " + tableTime + " ORDER BY BatchNbr DESC"
results = system.db.runPrepQuery(queryMain)
ptableTime.data = results
This doesn’t:
ptableTime = event.source.parent.getComponent('timeTable')
# This variable below is a string with the value "hamjet_report.dbo.FAB_HT_Time_Data"
tableTime = event.source.parent.tableTime
queryMain = "SELECT * FROM " + tableTime + " ORDER BY BatchNbr DESC"
results = system.db.runPrepQuery(queryMain)
ptableTime.data = results
Diagnostics
Invalid object name 'hamjet_report.dbo.FAB_HT_Data'.
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayResponse$GatewayThrowable: Invalid object name 'hamjet_report.dbo.FAB_HT_Data'.
Thanks for the reply. I pasted the wrong section of the diagnostic. Just to check, I re-ran the test and achieved the same error message. The correct diagnostic info is shown below.
Is the problem related to the speech marks in the SQL query? I have tried entering the string in the custom property with and without speech marks but it makes no difference to the outcome.
I’m baffled. Is there another way to achieve the desired functionality? At the moment I have two sets of popup windows, all identical with the exception of the database names buried in the scripts. This is not ideal.
Cheers
Traceback (most recent call last):
File "<event:actionPerformed>", line 41, in <module>
java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepQuery(SELECT * FROM "hamjet_report.dbo.FAB_HT_Time_Data" ORDER BY BatchNbr DESC, , [null], )
caused by Exception: Error executing system.db.runPrepQuery(SELECT * FROM "hamjet_report.dbo.FAB_HT_Time_Data" ORDER BY BatchNbr DESC, , [null], )
caused by GatewayException: Invalid object name 'hamjet_report.dbo.FAB_HT_Time_Data'.
caused by SQLServerException: Invalid object name 'hamjet_report.dbo.FAB_HT_Time_Data'.
Ignition v8.0.5 (b2019101516)
Java: Azul Systems, Inc. 11.0.4
SQLServer doesn’t follow the SQL language specification for quoted identifiers (double quotes for identifier, single quotes for values) unless ANSI_QUOTES is configured. You must use square brackets around identifiers by default.
When quoting identifiers in SQL, you must quote each piece separately, like so:
"hamjet_report"."dbo"."FAB_HT_Time_Data"
or
[hamjet_report].[dbo].[FAB_HT_Time_Data]
Quoted identifiers are typically case sensitive. Based on your hard-coded example, you probably shouldn’t be quoting.
I suspect you are quoting inside the .tableTime property. I don’t think you should be doing that. Quotes are used in python to delimit strings from code. A string property’s content is not code.
As a quick test, I pasted the hardcoded lines into the ignition database query browser.
As expected, this does not work - the source does not exist SELECT * FROM "hamjet_report.dbo.FAB_HT_Time_Data" ORDER BY BatchNbr DESC
This works perfectly.
SELECT * FROM hamjet_report.dbo.FAB_HT_Time_Data ORDER BY BatchNbr DESC
As does this.
SELECT * FROM [hamjet_report].[dbo].[FAB_HT_Time_Data] ORDER BY BatchNbr DESC
and this.
SELECT * FROM “hamjet_report”.“dbo”.“FAB_HT_Time_Data” ORDER BY BatchNbr DESC
So, I configured my popup with a new custom property as a string. I set the value to be the full path (with no double quotes) and everything is now working. I can only assume that I had something setup incorrectly…
Just curious, for the event.source.parent.tableTime property value are you including the quotes in the property value? If you are, try removing the quotes from that property so that it is just hamjet_report.dbo.FAB_HT_Time_Data