Hi everybody! I’m trying to set up a Comment Panel component in my project with the Three-Table Configuration, and although I have the three tables correct, the console shows me an error about two data types relations, like I show here:
08:44:09.784 [AWT-EventQueue-0] ERROR Vision.Components.PMICommentsPanel2 - Error invoking extension method.
org.python.core.PyException: Traceback (most recent call last):
File "<extension-method insertNote>", line 37, in insertNote
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:258)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.base/java.lang.reflect.Method.invoke(Unknown Source)
java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO notes (note, username, tstamp, attachment, filename, sticky) VALUES (?, ?, CURRENT_TIMESTAMP, ?, ?, ?), [hola, gema.corbi, , , false], PostgreSQL_BD1, , true, true)
at org.python.core.Py.JavaError(Py.java:552)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:258)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.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)
at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:538)
at org.python.core.PyObject.__call__(PyObject.java:413)
at org.python.pycode._pyx0.insertNote$1(<extension-method insertNote>:44)
at org.python.pycode._pyx0.call_function(<extension-method insertNote>)
at org.python.core.PyTableCode.call(PyTableCode.java:171)
at org.python.core.PyBaseCode.call(PyBaseCode.java:308)
at org.python.core.PyFunction.function___call__(PyFunction.java:471)
at org.python.core.PyFunction.__call__(PyFunction.java:466)
at org.python.core.PyFunction.__call__(PyFunction.java:456)
at org.python.core.PyFunction.__call__(PyFunction.java:451)
at com.inductiveautomation.vision.api.client.components.model.ExtensionFunction.invoke(ExtensionFunction.java:151)
at com.inductiveautomation.factorypmi.application.components.PMICommentsPanel2.addNewNote(PMICommentsPanel2.java:248)
at com.inductiveautomation.factorypmi.application.components.PMICommentsPanel2$AddNotePanel.actionPerformed(PMICommentsPanel2.java:672)
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: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO notes (note, username, tstamp, attachment, filename, sticky) VALUES (?, ?, CURRENT_TIMESTAMP, ?, ?, ?), [hola, gema.corbi, , , false], PostgreSQL_BD1, , true, true)
... 55 common frames omitted
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: SQL error for "INSERT INTO notes (note, username, tstamp, attachment, filename, sticky) VALUES (?, ?, CURRENT_TIMESTAMP, ?, ?, ?)": ERROR: no existe la columna �username� en la relaci�n �notes�
Position: 26
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:351)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:325)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:278)
at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runPrepStmt(ClientDBUtilities.java:282)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:256)
... 53 common frames omitted
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayResponse$GatewayThrowable: ERROR: no existe la columna �username� en la relaci�n �notes�
Position: 26
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:120)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.executeUpdate(SRConnectionWrapper.java:1016)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.RunPrepStmt.runUpdateQuery(RunPrepStmt.java:65)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractUpdateQuery.run(AbstractUpdateQuery.java:25)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:68)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:405)
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$NotAsyncServlet.service(ServletHolder.java:1391)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:760)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:547)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:590)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1607)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1297)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:485)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1577)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1212)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:322)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59)
at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:146)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.server.Server.handle(Server.java:500)
at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:383)
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:547)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:375)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:270)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129)
at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:388)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:806)
at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:938)
at java.lang.Thread.run(null)
These are my tables in postgres:
I have these in a SQL Query inside Data Property:
SELECT
notes.id,
usuarios.username as whoid,
notes.tstamp,
notes.note,
notes.filename,
notes.sticky
FROM
notes
JOIN usuarios ON notes.whoid = usuarios.id
ORDER BY
notes.tstamp DESC
And this is the script I’m using:
# Use this code to insert the entered note into your database table
# determine the ID for the logged in user
user = system.db.runScalarPrepQuery("SELECT id from usuarios where username = ?", [system.security.getUsername()])
# determine if a file is being attached
if filename is None:
attachmentBytes = None
else:
attachmentBytes = system.file.readFileAsBytes(filename) # fetch attachment data
# splits the file name from the file path. This way we can show just the file name on the component
# Using '\' as a delimiter, but python requires two since it's an escape character
pathAndFile = filename.rsplit('\\', 1)
filename = pathAndFile[1]
#build the query
# insert the note and attachment data
#query = "INSERT INTO notes (note, username, tstamp, attachment, filename, sticky)" + \
# "VALUES (?, ?, CURRENT_TIMESTAMP, ?, ?, ?)"
query = "INSERT INTO notes (note, username, tstamp, attachment, filename, sticky) VALUES (?, ?, CURRENT_TIMESTAMP, ?, ?, ?)"
args = [note, system.security.getUsername(), attachmentBytes, filename, sticky]
noteID = system.db.runPrepUpdate(query, args, database="PostgreSQL_BD1", skipAudit=True, getKey=True)
# add an additional entry into a note/account mapping table
# this second section is not needed if you are using only one table to store notes
accountID = 1234 #fill in your account value here
query = "INSERT INTO itemnotes (accountid, noteid) VALUES (?, ?)"
args = [accountID, noteID]
system.db.runPrepUpdate(query, args, database="PostgreSQL_BD1", skipAudit=True)
Reading the error, I suppose the problem is in ‘usuarios.username as whoid’, but I have written everything as it appears in the manual.
¿Does anyone know what is happening and how to fix it?
Thank you in advance to read my question.
Regards,
Gema