Hi, I have found that when the data type of a SQL Server 2014 PK column ‘ndx’ is set to INT, the power table scripts work fine but if I change the column to BIGINT, then the power table scripts do not work.
Is there a work around for this?
Are you getting any kind of popup errors or errors in your logs when you try to use BIGINT? Can you share a screenshot of those potential errors?
Also, what version of Ignition are you using (including minor revision)?
I think the log errors will say for sure but also curious if it could be just a data type mapping issue.
The JDBC driver default settings in the manual show:
That makes me think it needs to be sent as a long, if the value is small enough in your table I could see it defaulting to an int. Can you force it to a long before sending it as your argument to see if it does anything? I’m sure @bau can tell me if I’m way off. I’m saying this without having a database in front of me to test it on right now.
1 Like
Hi, thank you for responding. I am using 7.9.12.
I ran a test just now and as soon as I changed the DB table PK to bigint, the script stopped working or at least the check boxes would no longer accept user input. They would change highlighted color but would not let me check/uncheck. Once I put the PK back to int, everything worked fine…
I could not see any obvious errors in the log (see attached pic).
Thank you for responding. I don’t know how to modify how the power table sends data to the SQL server. I just point it to the database and any modifications made for long / int are done within the database itself…
(I am signed up for a Inductive Automation full week course in May, so hope to get a lot better at this!)
You might need to update your JDBC driver. Some versions didn’t handle java BigInteger to DB bigint properly. (Jython long is java BigInteger, not java Long.)
To try what @bpreston is suggesting try changing args = [newValue, key] to args = [newValue, long(key)]
1 Like
Thank you, are you suggesting I pass some arguments from within the power table query?
No, I’m suggesting changing your onCellEdited Extension function from:
key = self.data.getValueAt(rowIndex, 'ndx')
args = [newValue,key]
query = 'UPDATE tbl_MMS_Exclued SET %s = ? WHERE ndx = ?' % (colName)
system.db.runPrepUpdate(query,args)
system.db.refresh(self,'data')
to something like:
key = self.data.getValueAt(rowIndex, 'ndx')
args = [newValue,long(key)]
query = 'UPDATE tbl_MMS_Exclued SET %s = ? WHERE ndx = ?' % (colName)
system.db.runPrepUpdate(query,args)
system.db.refresh(self,'data')
I wasn’t referring to the java version. The JDBC driver is from Microsoft, and needs to be selected to match your DB and java platform (JRE 8). See this Microsoft compatibility matrix:
Ah, my bad!
I tried both 'long' and 'biginteger' but neither worked with the PK set to biginteger
Thank you regardless
I am currently investigating the compatibility matrix, thank you very much