Comments panel database

Ignition
1

Hello,

We are trying to get the comments panel working, but are having trouble with errors everytime the screen is run. We are using a MySQL DB. We’re under the assumption that the table is not automatically created (like in the transactions groups), so we created one, but errors saying: GatewayException: Gateway Error 301: SQL error for “INSERT INTO Notes (Note, WhoId, TStamp, Attachment, Filename, Sticky) VALUES (?, (SELECT Id FROM Users WHERE Username=‘admin’), CURRENT_TIMESTAMP, ?, ?, ?)”: Table ‘test.users’ doesn’t exist
caused by MySQLSyntaxErrorException: Table ‘test.users’ doesn’t exist

Can you tell me what we’re doing wrong?

2

Sure, the insert query property of the comments panel is set to the following:INSERT INTO Notes (Note, WhoId, TStamp, Attachment, Filename, Sticky) VALUES (?, (SELECT Id FROM Users WHERE Username='admin'), CURRENT_TIMESTAMP, ?, ?, ?)In that example the notes table has a user id not a username, so there is a sub-query that looks up the user id based on the username. If you just need the username change the query to the following:INSERT INTO Notes (Note, WhoId, TStamp, Attachment, Filename, Sticky) VALUES (?, '%s', CURRENT_TIMESTAMP, ?, ?, ?)This also means youur notes table must have the following fields:

Note
WhoId
TStamp
Attachment
Filename
Sticky

If the columns do not match change the query so they do. Hope this helps.

3

I am trying to set this up and running into what may be a similar problem.
I have my two tables set up and I can enter data into SQL Server and the notes entered directly into the tables show up fine in the comments panel. However, the add, unstick and delete buttons in the comments panel do not work. I debugged my query strings and they seem OK, but I am getting an error that the “Notes” object doesn’t exist:
GatewayException: Gateway Error 301: SQL error for “INSERT INTO Notes (Note, UserName, Timestamp, Attachment, AttachmentFilename, Stick) VALUES (?, ‘admin’, CURRENT_TIMESTAMP, ?, ?, ?)”: Invalid object name ‘Notes’.
caused by SQLServerException: Invalid object name ‘Notes’.

I am wondering where you set up the database connection that is used by these queries. I can set it for the query that generates the data set, but I don’t see anywhere to set it for the insert, update and delete queries. Do they automatically use the same connection? I think they may be defaulting to a database that doesn’t exist or doesn’t contain my notes table.

4

You are right, we are using the default database connection for the project for the insert and update queries. I will add a ticket to add a datasource property to the component.

5

I just tried using it with the default data source and it almost worked.
SQL server is rejecting the attachment. I have that column set up as a varbinary(max), which I think should correspond to a BLOB. I tried putting a convert statement into the insert query, but it didn’t like the syntax.
Any tips on getting this to work with SQL Server?

My original insert:
[color=#0000FF]INSERT INTO Notes (NoteText, UserName, Timestamp, Attachment, AttachmentFilename, Stick) VALUES (?, ‘%s’, CURRENT_TIMESTAMP, ?, ?, ?)[/color]
Gives this error:
[color=#FF0000]GatewayException: Gateway Error 301: SQL error for “INSERT INTO Notes (NoteText, UserName, Timestamp, Attachment, AttachmentFilename, Stick) VALUES (?, ‘admin’, CURRENT_TIMESTAMP, ?, ?, ?)”: Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.
caused by SQLServerException: Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

Ignition v7.2.5-beta3 (b63)
Java: Sun Microsystems Inc. 1.6.0_24[/color]

Then I tried this insert:
[color=#0000FF]INSERT INTO Notes (NoteText, UserName, Timestamp, Attachment, AttachmentFilename, Stick) VALUES (?, ‘%s’, CURRENT_TIMESTAMP, (CONVERT(varbinary(MAX),?), ?, ?)[/color]
which caused a syntax error.

Attachments aren’t really required in this application. Is there a way to turn that feature off so I don’t have to worry about it in the queries?

Thanks.

6

You do need to convert the data type. Just do the following:

INSERT INTO Notes (NoteText, UserName, Timestamp, Attachment, AttachmentFilename, Stick) VALUES (?, ‘%s’, CURRENT_TIMESTAMP, CAST(? AS varbinary(MAX)), ?, ?)

That should work!

7

That does the trick! Thanks a bunch.