# SQL query in script - custom property error

**URL:** <https://forum.inductiveautomation.com/t/sql-query-in-script-custom-property-error/32691>\
**Category:** Ignition\
**Created:** [February 28, 2020, 11:46am UTC](https://forum.inductiveautomation.com/t/sql-query-in-script-custom-property-error/32691 "2020-02-28T11:46:27Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![john.eichler](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/john.eichler/32/24632_2.png) [@john.eichler](https://forum.inductiveautomation.com/u/john.eichler)\
**Post date:** [February 28, 2020, 11:46am UTC](https://forum.inductiveautomation.com/t/sql-query-in-script-custom-property-error/32691/1 "2020-02-28T11:46:27Z")

</div>

Hi All

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:

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

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

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

```

---

<div class="post-metadata">

**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\
**Post date:** [February 28, 2020, 11:58am UTC](https://forum.inductiveautomation.com/t/sql-query-in-script-custom-property-error/32691/2 "2020-02-28T11:58:19Z")

</div>

Looks like your string is wrong, missing “time”.

---

<div class="post-metadata">

**Author:** ![john.eichler](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/john.eichler/32/24632_2.png) [@john.eichler](https://forum.inductiveautomation.com/u/john.eichler)\
**Post date:** [February 28, 2020, 9:12pm UTC](https://forum.inductiveautomation.com/t/sql-query-in-script-custom-property-error/32691/3 "2020-02-28T21:12:26Z")

</div>

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

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

```

---

<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:** [February 28, 2020, 9:25pm UTC](https://forum.inductiveautomation.com/t/sql-query-in-script-custom-property-error/32691/4 "2020-02-28T21:25:57Z")

</div>

Some possibilities:

1. 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.

2. When quoting identifiers in SQL, you must quote each piece separately, like so:

```auto
"hamjet_report"."dbo"."FAB_HT_Time_Data"

```

or

```auto
[hamjet_report].[dbo].[FAB_HT_Time_Data]

```

1. 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.

---

<div class="post-metadata">

**Author:** ![john.eichler](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/john.eichler/32/24632_2.png) [@john.eichler](https://forum.inductiveautomation.com/u/john.eichler)\
**Post date:** [February 28, 2020, 10:36pm UTC](https://forum.inductiveautomation.com/t/sql-query-in-script-custom-property-error/32691/5 "2020-02-28T22:36:31Z")

</div>

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…

Thanks both for your help!

---

<div class="post-metadata">

**Author:** ![WillMT10](https://avatars.discourse-cdn.com/v4/letter/w/7feea3/32.png) [@WillMT10](https://forum.inductiveautomation.com/u/WillMT10)\
**Post date:** [February 28, 2020, 11:21pm UTC](https://forum.inductiveautomation.com/t/sql-query-in-script-custom-property-error/32691/6 "2020-02-28T23:21:03Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![john.eichler](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/john.eichler/32/24632_2.png) [@john.eichler](https://forum.inductiveautomation.com/u/john.eichler)\
**Post date:** [March 1, 2020, 10:13pm UTC](https://forum.inductiveautomation.com/t/sql-query-in-script-custom-property-error/32691/7 "2020-03-01T22:13:29Z")

</div>

Thanks for the reply. See above. I did exactly that and all is working.

---

<div class="post-metadata">

**Author:** ![WillMT10](https://avatars.discourse-cdn.com/v4/letter/w/7feea3/32.png) [@WillMT10](https://forum.inductiveautomation.com/u/WillMT10)\
**Post date:** [March 2, 2020, 1:42am UTC](https://forum.inductiveautomation.com/t/sql-query-in-script-custom-property-error/32691/8 "2020-03-02T01:42:54Z")

</div>

Whoops! Sorry about that. Missed the last bit in your post.