Insert data from the button component

https://docs.inductiveautomation.com/display/DOC80/Inserting+Data+into+a+Database#InsertingDataintoaDatabase-Example-InsertingValues

I am trying to get the button to submit. I followed the instructions and this is the error:

Traceback (most recent call last):
  File "<event:actionPerformed>", line 7, in <module>
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:341)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:315)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:268)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:917)
	at com.inductiveautomation.ignition.designer.db.namedquery.DesignerNamedQueryManager.execute(DesignerNamedQueryManager.java:62)
	at com.inductiveautomation.ignition.designer.db.namedquery.DesignerNamedQueryManager.execute(DesignerNamedQueryManager.java:44)
	at com.inductiveautomation.ignition.client.script.ClientDBUtilities.runNamedQuery(ClientDBUtilities.java:115)
	at jdk.internal.reflect.GeneratedMethodAccessor1665.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ':'.

	at org.python.core.Py.JavaError(Py.java:552)
	at org.python.core.Py.JavaError(Py.java:543)
	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:190)
	at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:518)
	at org.python.core.PyObject.__call__(PyObject.java:497)
	at org.python.core.PyObject.__call__(PyObject.java:501)
	at org.python.pycode._pyx114.f$0(<event:actionPerformed>:7)
	at org.python.pycode._pyx114.call_function(<event:actionPerformed>)
	at org.python.core.PyTableCode.call(PyTableCode.java:171)
	at org.python.core.PyCode.call(PyCode.java:18)
	at org.python.core.Py.runCode(Py.java:1614)
	at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:761)
	at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:187)
	at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:278)
	at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:57)
	at com.sun.proxy.$Proxy63.actionPerformed(Unknown Source)
	at java.desktop/javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
	at java.desktop/javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
	at java.desktop/javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
	at java.desktop/javax.swing.DefaultButtonModel.setPressed(Unknown Source)
	at java.desktop/javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
	at java.desktop/java.awt.Component.processMouseEvent(Unknown Source)
	at java.desktop/javax.swing.JComponent.processMouseEvent(Unknown Source)
	at java.desktop/java.awt.Component.processEvent(Unknown Source)
	at java.desktop/java.awt.Container.processEvent(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
	at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
	at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Window.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.EventQueue.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.EventQueue$4.run(Unknown Source)
	at java.desktop/java.awt.EventQueue$4.run(Unknown Source)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.desktop/java.awt.EventQueue$5.run(Unknown Source)
	at java.desktop/java.awt.EventQueue$5.run(Unknown Source)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.desktop/java.awt.EventQueue.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.run(Unknown Source)
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ':'.
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:341)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:315)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:268)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:917)
	at com.inductiveautomation.ignition.designer.db.namedquery.DesignerNamedQueryManager.execute(DesignerNamedQueryManager.java:62)
	at com.inductiveautomation.ignition.designer.db.namedquery.DesignerNamedQueryManager.execute(DesignerNamedQueryManager.java:44)
	at com.inductiveautomation.ignition.client.script.ClientDBUtilities.runNamedQuery(ClientDBUtilities.java:115)
	at jdk.internal.reflect.GeneratedMethodAccessor1665.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:188)
	... 48 more
Caused by: java.lang.Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ':'.
	at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:414)
	at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor.execute(NamedQueryExecutor.java:200)
	at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.execute(GatewayNamedQueryManager.java:108)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.NamedQueryFunctions.executeNamedQuery(NamedQueryFunctions.java:72)
	at jdk.internal.reflect.GeneratedMethodAccessor142.invoke(null)
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(null)
	at java.lang.reflect.Method.invoke(null)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.AbstractGatewayFunction.invoke(AbstractGatewayFunction.java:225)
	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:411)
	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:86)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:852)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:535)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:190)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1595)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:188)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1253)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:168)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:473)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1564)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:166)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1155)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:61)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
	at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:335)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:61)
	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:126)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
	at org.eclipse.jetty.server.Server.handle(Server.java:530)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:347)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:256)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:279)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:102)
	at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:124)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:247)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.produce(EatWhatYouKill.java:140)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131)
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:382)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:708)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:626)
	at java.lang.Thread.run(null)

Ignition v8.0.6 (b2019111216)
Java: Azul Systems, Inc. 11.0.4

yes, i added administrator for legacy database access

Show your complete actionPerformed event code with three grave marks on a line above and below:
```
Paste code here.
```
It looks like the problem is a colon in line 7.


# Grab the area number and machine name from the components we added to the window.
areaNum = event.source.parent.getComponent('Dropdown').selectedValue
machineName = event.source.parent.getComponent('Text Field').text
 
# A call to our Named Query, inserting the two parameters using dictionary syntax.
system.db.runNamedQuery("Insert Data", {"machineName":machineName, "areaNumber":areaNum})

from tutorial
https://docs.inductiveautomation.com/display/DOC80/Inserting+Data+into+a+Database#InsertingDataintoaDatabase-Example-InsertingValues

EDIT: Below no longer applies; thanks for cleaning up posted error and code.

I’m not sure what happened pasting your code into the forum, but all you want are three grave marks on a line by themselves above the code and three again on a line below all the code–none in the middle. This isn’t the exact code that threw the error as the error was on line 7 and this only has 5 lines. Try dropping the space before each colon.

“machineName”:machineName, “areaNumber”:areaNum
…there are no spaces

Please edit your posts to include the formatting markers per witman’s instructions.

1 Like

Caused by: java.lang.Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ‘:’.
at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:414)
at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor.execute(NamedQueryExecutor.java:200)
at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.execute(GatewayNamedQueryManager.java:108)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.NamedQueryFunctions.executeNamedQuery(NamedQueryFunctions.java:72)
at jdk.internal.reflect.GeneratedMethodAccessor142.invoke(null)
at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(null)
at java.lang.reflect.Method.invoke(null)
at com.inductiveautomation.ignition.gateway.servlets.gateway.AbstractGatewayFunction.invoke(AbstractGatewayFunction.java:225)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:411)
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:86)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:852)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:535)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:190)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1595)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:188)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1253)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:168)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:473)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1564)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:166)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1155)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:61)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:335)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:61)
at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:126)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
at org.eclipse.jetty.server.Server.handle(Server.java:530)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:347)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:256)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:279)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:102)
at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:124)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:247)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.produce(EatWhatYouKill.java:140)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131)
at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:382)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:708)
at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:626)
at java.lang.Thread.run(null)

Ignition v8.0.6 (b2019111216)
Java: Azul Systems, Inc. 11.0.4

Please use the formatting markers for pasted tracebacks, too. Please don’t repaste–just edit your original posts to include the formatting markers.

i appreciate the help, i do not understand grave markers or formatting markers, i will cleanup, please explain

1 Like

code

Click this button.

3 Likes

This forum normally tries to display whatever you post as paragraphs of text. Code, log files, error tracebacks, and other plain text doesn’t format nicely. You tell the forum software that a chunk of pasted text should be shown as-is by putting three reverse single quotes (aka the “accent grave” character) by themselves on a line above your pasted text, and again below the pasted material. As @witman tried to show you.

2 Likes

I don’t immediately see any errors in your script, assuming the component names and relative locations are correct (Dropdown and Text Field on the same level in component tree as the button with this script). Is your named query any different than the example?

INSERT INTO machines (machine_name, area_number) VALUES (:machineName, :areaNumber)
1 Like

TY all, case is resolved I had a typo in my namedQuery, areaNumber is now correct


success

1 Like

It worked in Vision Client, when I tried a Perspective page same syntax, it does not write when I click on the button.

‘’’
def runAction(self, event):

areaNum = event.source.parent.getComponent('Dropdown').selectedValue
machineName = event.source.parent.getComponent('Text Field').text
 
# A call to our Named Query, inserting the two parameters using dictionary syntax.
system.db.runNamedQuery("Insert Data", {"machineName":machineName, "areaNumber":areaNum})

‘’’

,
areaNum = self.getChild(“Dropdown”).props.value
machineName = self.getChild(“Text Field”).props.text
‘’’

Duh, perspective and vision have different compents!