Comments Panel Setup

steven.cox · September 24, 2020, 8:27pm

Hi All,

I am new to Ignition and working to build a project to learn with. I am trying to get a comments panel up and working but cannot seem to find enough information.

I am using Ignition 8.0.16 and it seems much of the information outside of the user manual if for the legacy component.

Is there any additional information on setting this up that might help?

I have created the 3 tables required but don’t know where to go from here.

Thanks, Steven

steven.cox · September 28, 2020, 8:38pm

Hi all,

Does the Comments Panel work with SQLite?

Thanks, Steven

PGriffith · September 28, 2020, 9:27pm

You definitely could get the comments panel working with a SQLite DB. I’m guessing you’re not particularly familiar with SQL-as-a-language; you’ll want to read up on select and insert queries specifically.
https://docs.inductiveautomation.com/display/DOC80/Vision+-+Comments+Panel
To get the bare minimum functionality, you need to implement the insertNote extension function; there’s two examples on the manual page, if you’ve already got your table created with the required columns. You will probably want to use named queries, instead of runPrepUpdate, as the documentation example is; but all you really need to do is copy the query written in the manual into a named query and add the parameters.

steven.cox · September 29, 2020, 3:14pm

@PGriffith I would like to understand the default setup as described in the manual. I seem to be missing something.

I have created the 3 tables as described in the manual.

I have Enabled the insertNote extension function in component scripting.

I changed the column names in the query to match the tables I created.

I have tried it with lines 25 to the end commented out.

either way I get no results or errors.

In the Console, I get this entry when I try to add a Note.

One thing I have noticed is the information in the manual does not match the component. Maybe I have missed something?

Thanks so much for the help,
Steven

steven.cox · September 29, 2020, 3:18pm

I forgot to add the Console entry.

10:11:19.107 [AWT-EventQueue-0] WARN com.inductiveautomation.factorypmi.application.components.PMICommentsPanel2 - system.db.refresh() was unable to find a refresh-compatible property binding on Comments Panel.data

Thanks, Steven

PGriffith · September 29, 2020, 3:22pm

So, the manual is missing/not filling in a step - you also need to have a query binding on the 'Data' property, that should be selecting columns with the following names from your database:

Columns are: ID, Username, Timestamp, Note, Filename, IsSticky

So something like:

SELECT 
  id as ID,
  Users.username as Username,
  TStamp as Timestamp,
  Note,
  Filename,
  Sticky as IsSticky 
FROM Notes 
LEFT JOIN Users ON Notes.WhoId = Users.id

steven.cox · September 29, 2020, 6:28pm

@PGriffith now this makes since! However, I was getting this error…

On: Main Window.Root Container.Comments Panel.data
caused by GatewayException: [SQLITE_ERROR] SQL error or missing database (ambiguous column name: id)
caused by SQLiteException: [SQLITE_ERROR] SQL error or missing database (ambiguous column name: id)

so I added the DB to the Notes.id path. So I have this…

SELECT
Notes.id as ID,
Users.username as username,
TStamp as Timestamp,
Note as notetext,
Filename as attachmentname,
Sticky as isSticky
FROM Notes
LEFT JOIN Users ON Notes.WhoId = Users.id

I then got this error …

I changed the datatype in the Notes table to datetime but now I get this error…

I reading up on SQLite it appears that it does not use a datetime datatype…
https://www.tutorialspoint.com/sqlite/sqlite_data_types.htm

BTW, I disabled the extension function to isolate the errors.

Any ideas?

Thanks, Steven

PGriffith · September 29, 2020, 6:48pm

Try making the column a LONG/BIGINT/etc in SQlite, although now that you ran into it I think I had to fix an issue with SQLite datatypes for 8.1…

steven.cox · September 29, 2020, 7:19pm

@PGriffith I found the SQLQuery example in the manual and used it…

SELECT
Notes.id,
Users.username as WhoId,
Notes.TStamp,
Notes.Note,
Notes.Filename,
Notes.Sticky

FROM
Notes
JOIN Users
ON Notes.WhoId = Users.id

ORDER BY
Notes.TStamp DESC

This does not produce any errors.

I re-enabled the insert note extension function and now I do not get errors but I get this in the Console…

14:15:16.796 [AWT-EventQueue-0] ERROR Vision.Components.PMICommentsPanel2 - Error invoking extension method.
org.python.core.PyException: Traceback (most recent call last):
File “”, line 17, in insertNote
at java.base/java.io.File.(Unknown Source)
followed by a LOT of other similar lines.

Also do not get any results in the Comments panel.

Thanks, Steven

jlandwerlen · September 29, 2020, 8:29pm

I was under the impression SQLite doesn’t care about data types… you can basically shove whatever you want wherever you want.

PGriffith · September 29, 2020, 8:31pm

SQLite doesn’t care, but the comments panel does
We’re (via the JDBC driver) trying to coerce whatever SQLite gives us into a specific datatype, which looks like what’s failing.

@steven.cox,
The full details on that exception you last posted would be helpful - it’s hard to say what’s actually causing it without a stacktrace.

steven.cox · September 29, 2020, 8:40pm

@PGriffith ok. So this is the insert note extension function.

def insertNote(self, note, filename, sticky):
“”"
Called when a note is inserted into this panel. It is up to the
implementation of this function to alter the underlying data that drives
the panel.

Arguments:
	self: A reference to the component that is invoking this function.
	note: The text contents of the note
	filename: The filename of the attachment provided with this note. 
	          Note that the filename is a full path to the file and can be used to
	          fetch file data.
	sticky: A boolean indicating whether this note is stickied
"""
## Use this code to insert the entered note into your database table
## fetch attachment data
attachmentBytes = system.file.readFileAsBytes(filename)

## insert the note and attachment data
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="SQLite", 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 ItemNoteMapping (account_id, note_id) VALUES (?, ?)"
args = [accountID, noteID]
system.db.runPrepUpdate(query, args, database="", skipAudit=True)

This is from the console when I enter data and press Add Note…

15:36:52.478 [AWT-EventQueue-0] INFO designer.update-and-save - Save finished in 86ms.
15:37:14.999 [AWT-EventQueue-0] ERROR Vision.Components.PMICommentsPanel2 - Error invoking extension method.
org.python.core.PyException: Traceback (most recent call last):
File “”, line 17, in insertNote
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)
java.lang.NullPointerException: java.lang.NullPointerException

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 org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:206)
at org.python.core.PyObject.__call__(PyObject.java:480)
at org.python.core.PyObject.__call__(PyObject.java:484)
at org.python.pycode._pyx43.insertNote$1(<extension-method insertNote>:30)
at org.python.pycode._pyx43.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.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:188)
… 49 common frames omitted

Hope this helps.

Thanks, Steven

PGriffith · September 29, 2020, 8:42pm

Okay, so, the manual example is a little optimistic. The filename parameter is null because you’re trying to insert a note without a file attached, which is causing the null pointer exception when the system tries to read a file at a nonexistent path.
So, you need to branch your logic on filename is None; something like:

if filename is not None:
	attachmentBytes = system.file.readFileAsBytes(filename)
else:
	attachmentBytes = None

At the beginning of the script block.

steven.cox · September 30, 2020, 8:22pm

@PGriffith ok I added the IF statement and changes a couple of reference to table names and that stopped all the errors.

I am still not getting the data onto the comment panel but it is getting into the Notes Table.

The Designer does not like the image file…designer comes to a halt when I try to query the Notes Table.

However, the file upload and the Sticky work.

Thanks, Steven

steven.cox · September 30, 2020, 8:26pm

@PGriffith,

I tried, BIGINT, INTEGER, TEXT, DATE, and VARCHAR.

VARCHAR alows the data to get to the table, but the Select query does not like it.

This looks like it might be the last issue to make it work.

Thanks for all the help,

Steven

eet · October 17, 2020, 6:15am

Hi there!
Did someone already done binding this accountid?
I try to passed parameter but doesn’t work
acountid = event.source.accountID

# insert a row onto the itemNotes table

# replace 'MYID' with the proper code - this is based on how you are dividing the notes.

# this ID could be an area, page, or machine code, or anything else that you may want to organize on.

acountid = event.source.accountID

system.db.runPrepUpdate( "INSERT INTO ItemNotes (AccountId, NoteId) VALUES (?, ?)" , [acountid , insertId])