# Error When I query number from Database

**URL:** <https://forum.inductiveautomation.com/t/error-when-i-query-number-from-database/10348>\
**Category:** Ignition\
**Created:** [August 14, 2015, 6:44pm UTC](https://forum.inductiveautomation.com/t/error-when-i-query-number-from-database/10348 "2015-08-14T18:44:31Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![jreynolds](https://avatars.discourse-cdn.com/v4/letter/j/58956e/32.png) [@jreynolds](https://forum.inductiveautomation.com/u/jreynolds)\
**Post date:** [August 14, 2015, 6:44pm UTC](https://forum.inductiveautomation.com/t/error-when-i-query-number-from-database/10348/1 "2015-08-14T18:44:31Z")

</div>

This is pretty much line by line out of the manual, so I’m missing something. Operation is when a button is clicked it runs the query to match the part number they typed into the numbox to the part list in the DB

[code]partNum = event.source.parent.getComponent(‘numPartNumber\_In’).longValue

results = system.db.runScalarQuery("SELECT part\_num FROM ifa\_parts WHERE " + “part\_num = ?”, [partNum])

event.source.parent.getComponent(‘Text Field’).text = results[/code]

[quote]Traceback (most recent call last):  
File “event:actionPerformed”, line 3, in   
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)

```
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runScalarQuery(AbstractDBUtilities.java:356)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

```

java.lang.Exception: java.lang.Exception: Error executing system.db.runScalarQuery(SELECT part\_num FROM ifa\_parts WHERE part\_num = ?, [5500004602L], )

```
at org.python.core.Py.JavaError(Py.java:495)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runScalarQuery(AbstractDBUtilities.java:356)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.python.core.PyReflectedFunction. __call__ (PyReflectedFunction.java:186)
at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction. __call__ (ScriptManager.java:428)
at org.python.core.PyObject. __call__ (PyObject.java:404)
at org.python.core.PyObject. __call__ (PyObject.java:408)
at org.python.pycode._pyx186.f$0(<event:actionPerformed>:5)
at org.python.pycode._pyx186.call_function(<event:actionPerformed>)
at org.python.core.PyTableCode.call(PyTableCode.java:165)
at org.python.core.PyCode.call(PyCode.java:18)
at org.python.core.Py.runCode(Py.java:1275)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:624)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:168)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:265)
at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:55)
at com.sun.proxy.$Proxy19.actionPerformed(Unknown Source)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$500(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

```

Caused by: java.lang.Exception: Error executing system.db.runScalarQuery(SELECT part\_num FROM ifa\_parts WHERE part\_num = ?, [5500004602L], )  
… 56 more  
Caused by: com.inductiveautomation.ignition.client.gateway\_interface.GatewayException: Connection Error: Datasource “[5500004602L]” does not exist in this Gateway.  
at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.newGatewayException(GatewayInterface.java:328)  
at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.sendMessage(GatewayInterface.java:302)  
at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.sendMessage(GatewayInterface.java:259)  
at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:719)  
at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.runQuery(GatewayInterface.java:683)  
at com.inductiveautomation.ignition.client.script.ClientDBUtilities.\_runQuery(ClientDBUtilities.java:185)  
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runScalarQuery(AbstractDBUtilities.java:349)  
… 54 more  
Caused by: java.sql.SQLException: Datasource “[5500004602L]” does not exist in this Gateway.  
at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnection(DatasourceManagerImpl.java:121)  
at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.getConnection(GatewayDBUtilities.java:66)  
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:60)  
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:343)  
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)  
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)  
at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)  
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:769)  
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:585)  
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)  
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:577)  
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:223)  
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1125)  
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)  
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)  
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1059)  
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)  
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)  
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)  
at org.eclipse.jetty.server.Server.handle(Server.java:497)  
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311)  
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:248)  
at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540)  
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:610)  
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:539)  
at java.lang.Thread.run(null)

Ignition v7.7.4 (b2015033012)  
Java: Oracle Corporation 1.8.0\_51  
[/quote]

---

<div class="post-metadata">

**Author:** ![nmudge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nmudge/32/3394_2.png) [@nmudge](https://forum.inductiveautomation.com/u/nmudge)\
**Post date:** [August 14, 2015, 7:13pm UTC](https://forum.inductiveautomation.com/t/error-when-i-query-number-from-database/10348/2 "2015-08-14T19:13:35Z")

</div>

Wrong SQL function.

Use this one: system.db.runScalarPrepQuery

Best,

---

<div class="post-metadata">

**Author:** ![Tim](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim/32/3361_2.png) [@Tim](https://forum.inductiveautomation.com/u/Tim)\
**Post date:** [August 17, 2015, 12:38pm UTC](https://forum.inductiveautomation.com/t/error-when-i-query-number-from-database/10348/3 "2015-08-17T12:38:47Z")

</div>

try

```auto
results = system.db.runScalarQuery("SELECT part_num FROM ifa_parts WHERE part_num = %d" % partNum)
```

---

<div class="post-metadata">

**Author:** ![jreynolds](https://avatars.discourse-cdn.com/v4/letter/j/58956e/32.png) [@jreynolds](https://forum.inductiveautomation.com/u/jreynolds)\
**Post date:** [August 17, 2015, 9:46pm UTC](https://forum.inductiveautomation.com/t/error-when-i-query-number-from-database/10348/4 "2015-08-17T21:46:35Z")

</div>

nmudge

I received this error when I changed the code to:

`partNum = event.source.parent.getComponent('numPartNumber_In').longValue
results = system.db.runScalarPrepQuery("SELECT part_num FROM ifa_parts WHERE " + "part_num = ?", [partNum])
#results = system.db.runScalarQuery("SELECT part_num FROM ifa_parts WHERE part_num = %d" % partNum)
event.source.parent.getComponent('Text Field').text = results`

Error:

[code]Traceback (most recent call last):

File “event:actionPerformed”, line 3, in

java.lang.Exception: java.lang.Exception: Error executing system.db.runScalarPrepQuery(SELECT part\_num FROM ifa\_parts WHERE part\_num = ?, , [5500004602], )

```
caused by Exception: Error executing system.db.runScalarPrepQuery(SELECT part_num FROM ifa_parts WHERE part_num = ?, , [5500004602], )
caused by GatewayException: The conversion from UNKNOWN to UNKNOWN is unsupported.
caused by SQLServerException: The conversion from UNKNOWN to UNKNOWN is unsupported.

```

Ignition v7.7.4 (b2015033012)  
Java: Oracle Corporation 1.8.0\_51[/code]

---

<div class="post-metadata">

**Author:** ![jreynolds](https://avatars.discourse-cdn.com/v4/letter/j/58956e/32.png) [@jreynolds](https://forum.inductiveautomation.com/u/jreynolds)\
**Post date:** [August 17, 2015, 9:48pm UTC](https://forum.inductiveautomation.com/t/error-when-i-query-number-from-database/10348/5 "2015-08-17T21:48:00Z")

</div>

Tim,

I received this error when I changed the code to this:

`partNum = event.source.parent.getComponent('numPartNumber_In').longValue
#results = system.db.runScalarPrepQuery("SELECT part_num FROM ifa_parts WHERE " + "part_num = ?", [partNum])
results = system.db.runScalarQuery("SELECT part_num FROM ifa_parts WHERE part_num = %d" % partNum)
event.source.parent.getComponent('Text Field').text = results`

Error:

[code]Traceback (most recent call last):

File “event:actionPerformed”, line 4, in

TypeError: can’t convert 5500004602L to java.lang.String

Ignition v7.7.4 (b2015033012)  
Java: Oracle Corporation 1.8.0\_51  
[/code]

---

<div class="post-metadata">

**Author:** ![jreynolds](https://avatars.discourse-cdn.com/v4/letter/j/58956e/32.png) [@jreynolds](https://forum.inductiveautomation.com/u/jreynolds)\
**Post date:** [August 17, 2015, 9:49pm UTC](https://forum.inductiveautomation.com/t/error-when-i-query-number-from-database/10348/6 "2015-08-17T21:49:35Z")

</div>

Does anyone know what it’s adding the “L” to the end of the number?

---

<div class="post-metadata">

**Author:** ![nmudge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nmudge/32/3394_2.png) [@nmudge](https://forum.inductiveautomation.com/u/nmudge)\
**Post date:** [August 17, 2015, 10:15pm UTC](https://forum.inductiveautomation.com/t/error-when-i-query-number-from-database/10348/7 "2015-08-17T22:15:33Z")

</div>

[quote=“jreynolds”]Does anyone know what it’s adding the “L” to the end of the number?[/quote]Because it is a long integer. In Python long integer literals have an L at the end of them to differentiate them from integers.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [August 18, 2015, 12:15pm UTC](https://forum.inductiveautomation.com/t/error-when-i-query-number-from-database/10348/8 "2015-08-18T12:15:05Z")

</div>

It just means you have to coerce the long integer before sending it to the text field:

```auto
event.source.parent.getComponent('Text Field').text = str(results)
```

---

<div class="post-metadata">

**Author:** ![jreynolds](https://avatars.discourse-cdn.com/v4/letter/j/58956e/32.png) [@jreynolds](https://forum.inductiveautomation.com/u/jreynolds)\
**Post date:** [August 18, 2015, 1:57pm UTC](https://forum.inductiveautomation.com/t/error-when-i-query-number-from-database/10348/9 "2015-08-18T13:57:53Z")

</div>

Thanks, for your help. Coercing it to a string solved the error.

---

<div class="post-metadata">

**Author:** ![LandrethEdward](https://avatars.discourse-cdn.com/v4/letter/l/7bcc69/32.png) [@LandrethEdward](https://forum.inductiveautomation.com/u/LandrethEdward)\
**Post date:** [February 5, 2020, 4:34pm UTC](https://forum.inductiveautomation.com/t/error-when-i-query-number-from-database/10348/10 "2020-02-05T16:34:49Z")

</div>

Hello, I am having a similar issue with querying in a tag event script. When running the below line in a button action performed script I have no issues, but when the same query is placed into the tag event script or gateway script I receive the below errors. I’ve attempted several query types including basic runQuery and prep queries, but none of those have worked either. I appreciate any help with this thanks.

Script trying to run:  
ID = system.db.runScalarQuery(“SELECT TOP 1 ID FROM [HX\_OEE].[dbo].[LWGT\_Load\_Times\_Temp]”)

Error executing script.  
Traceback (most recent call last):  
File “tagevent:valueChanged”, line 4, in valueChanged  
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)

```
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runScalarQuery(AbstractDBUtilities.java:356)

at sun.reflect.GeneratedMethodAccessor572.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

```

java.lang.Exception: java.lang.Exception: Error executing system.db.runScalarQuery(SELECT TOP 1 ID FROM [HX\_OEE].[dbo].[LWGT\_Load\_Times\_Temp], , )

---

<div class="post-metadata">

**Author:** ![Tim](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim/32/3361_2.png) [@Tim](https://forum.inductiveautomation.com/u/Tim)\
**Post date:** [February 5, 2020, 5:02pm UTC](https://forum.inductiveautomation.com/t/error-when-i-query-number-from-database/10348/11 "2020-02-05T17:02:08Z")

</div>

In the gateway scope you need to supply the datasource name, in the project scope it will default to the projects default datasource.

> system.db.runScalarQuery(“SELECT TOP 1 ID FROM LWGT\_Load\_Times\_Temp ORDER BY ID DESC”, 'HX\_OEE')

---

<div class="post-metadata">

**Author:** ![LandrethEdward](https://avatars.discourse-cdn.com/v4/letter/l/7bcc69/32.png) [@LandrethEdward](https://forum.inductiveautomation.com/u/LandrethEdward)\
**Post date:** [February 5, 2020, 5:30pm UTC](https://forum.inductiveautomation.com/t/error-when-i-query-number-from-database/10348/12 "2020-02-05T17:30:48Z")

</div>

Awesome that worked thank you! I have a follow up question now though.

In another server I usually format my gateway event script queries in the way I originally listed with the database inside the query string, and they have worked with that syntax. That server is on v7.9.10 while this one I had an issue with is on v7.9.12. Would there be any differences in these minor revs that could affect syntax for the queries?

---

<div class="post-metadata">

**Author:** ![LandrethEdward](https://avatars.discourse-cdn.com/v4/letter/l/7bcc69/32.png) [@LandrethEdward](https://forum.inductiveautomation.com/u/LandrethEdward)\
**Post date:** [February 5, 2020, 5:32pm UTC](https://forum.inductiveautomation.com/t/error-when-i-query-number-from-database/10348/13 "2020-02-05T17:32:24Z")

</div>

Oh please ignore what I just commented, I forgot gateway events are still listed under project scripts I understand. Thanks again.