Comments Panel adding unique itemid in itemnotes not working

Brian_Cockrell · December 19, 2024, 9:50pm

i am using postgres with 3 tables. Everything works as expected when using the default insertnotes with the extension function insertnote disabled.

I have an additional column in the itemnotes table that is called itemid which is a varchar.

when i enable insertnote fuction with

def insertNote(self, note, filename, sticky):
	## Use this code to insert the entered note into your database table
	## fetch attachment data
	attachmentBytes = system.file.readFileAsBytes(filename)
	itemid = self.itemid
	## insert the note and attachment data
	query = "INSERT INTO notes (note, username, t_stamp, attachment, filename, sticky)" + \
			"VALUES (?, ?, CURRENT_TIMESTAMP, ?, ?, ?)"
	args = [note, system.security.getUsername(), attachmentBytes, filename, sticky]
	noteID = system.db.runPrepUpdate(query, args, database="", getKey=True)
	***query = "INSERT INTO itemnotes (itemid) VALUES (?)"***
***	args = [itemid]***
***	itemnotes = system.db.runPrepUpdate(query,args,database="")***
	## 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 ItemNoteMapping (account_id, note_id) VALUES (?, ?)"
	args = [accountID, noteID]
	system.db.runPrepUpdate(query, args, database="", skipAudit=True)

I get this error

15:39:50.069 [AWT-EventQueue-0] ERROR Vision.Components.PMICommentsPanel2 -- Error invoking extension method.
org.python.core.PyException: java.lang.NullPointerException: java.lang.NullPointerException
at org.python.core.Py.JavaError(Py.java:545)
at org.python.core.Py.JavaError(Py.java:536)
at org.python.core.PyReflectedFunction.call(PyReflectedFunction.java:192)
at org.python.core.PyReflectedFunction.call(PyReflectedFunction.java:208)
at org.python.core.PyObject.call(PyObject.java:461)
at org.python.core.PyObject.call(PyObject.java:465)
at org.python.pycode.pyx79.insertNote$1(:20)
at org.python.pycode.pyx79.call_function()
at org.python.core.PyTableCode.call(PyTableCode.java:173)
at org.python.core.PyBaseCode.call(PyBaseCode.java:306)
at org.python.core.PyFunction.function___call(PyFunction.java:474)
at org.python.core.PyFunction.call(PyFunction.java:469)
at org.python.core.PyFunction.call(PyFunction.java:459)
at org.python.core.PyFunction.call(PyFunction.java:454)
at com.inductiveautomation.vision.api.client.components.model.ExtensionFunction.invoke(ExtensionFunction.java:152)
at com.inductiveautomation.factorypmi.application.components.PMICommentsPanel2.addNewNote(PMICommentsPanel2.java:246)
at com.inductiveautomation.factorypmi.application.components.PMICommentsPanel2$AddNotePanel.actionPerformed(PMICommentsPanel2.java:670)
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(Unknown Source)
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(Unknown Source)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.desktop/java.awt.EventQueue.dispatchEvent(Unknown Source)
at com.inductiveautomation.snap.swing.RibsEventQueue.dispatchEvent(RibsEventQueue.java:99)
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.NullPointerException: null
at java.base/java.io.File.(Unknown Source)
at com.inductiveautomation.ignition.common.script.builtin.FileUtilities.readFileAsBytes(FileUtilities.java:137)
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:190)
... 50 common frames omitted

I am just trying to add a unique id to the comment so it can be pulled for specific items instead of all the comments.

Any help would be greatly appreciated.

Thank you

PGriffith · December 19, 2024, 10:04pm

filename will be None, Python's special singleton object representing null, if there was no attachment on the note. You need to handle that in your code.

Brian_Cockrell · December 20, 2024, 12:10am

I will try that.

Thank you

Brian_Cockrell · December 20, 2024, 2:19pm

So i was able to get it tonwork but the noteid is not incrementing in the itemnotes datatable.

I am using the example excatly as shown just adding a 3rd argument in the itemnotes query called itemid that is a varchar.

Everything is working but the noteid no incrementing which is causing the dataset for data to not work properly since noteid is not changing

Thank you for your help

PGriffith · December 20, 2024, 4:14pm

Do you have an autoincrementing primary key in your notes column?

Brian_Cockrell · December 20, 2024, 4:17pm

Yes the id column is updating everytime a new note is added but the itemnotes noteid is not. When i use the default insertnote it works as expected but not when using the insertnote extension function.