My comment panel is successful with everything ‘null’ except the attachment feature. I ran my insert function and if something is attached I don’t get an exception. However, I have also noticed that if I remove the column from my insert statement and subsequent arguments, the query runs fine. I check the data table and it has a “null” value in that column because of the omission. I’ve narrowed it down to how I’m handling the argument. What am I doing wrong here:
if filename is None:
attachmentBytes = None
else:
attachmentBytes = system.file.readFileAsBytes(filename)
pathAndFile = filename.rsplit('\\',1)
filename = pathAndFile[1]
## insert the note and attachment data
query = "INSERT INTO notes (attachment)" + \
" VALUES (?)"
args = [attachmentBytes]
print query
print args
noteId = system.db.runPrepUpdate(query, args, database="", skipAudit=True, getKey=True)
print noteId
## 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 = self.parent.panelID #fill in your account value here
query = "INSERT INTO ItemNotes (accountId, noteId) VALUES (?, ?)"
args = [accountID, noteId]
print query
print args
system.db.runPrepUpdate(query, args, database="", skipAudit=True)
It seems the exception reason provided by the diagnostics is:
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: SQL error for "INSERT INTO notes (attachment) VALUES (?)": Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.
But I’ve tried changing the value statement to include CONVERT but I’m unclear on how to implement it. Is it in the value statement string? Or do I include it in the argument statement? I tried the argument statement but it wouldn’t even compile saying CONVERT isn’t defined.
The only thing holding up the comment panel is a null value in the attachment argument IF I’m the one sending the null. If it’s from being absent of the INSERT statement then null is fine in the data table.