I have 2 Named queries that generate tables, here they are:
VP.PERSONNUM AS 'BadgeNum'
, FORMAT( VP.INPUNCHDTM, 'hh:mm') AS 'ClockINTime'
, VP.PERSONFULLNAME AS 'Name'
, HOMELABORLEVELNM5 AS 'Shift'
, HOMELABORLEVELNM6 AS 'Dept'
, HOMELABORLEVELDSC7 AS 'JobTitle'
, HOMELABORLEVELDSC4 AS 'Supervisor'
VP_EMPCUREARNTIME AS VP
LEFT OUTER JOIN VP_EMPLOYEEV42 ON VP.PERSONNUM = VP_EMPLOYEEV42.PERSONNUM
LEFT OUTER JOIN TIMEZONE ON VP_EMPLOYEEV42.TIMEZONENAME = TIMEZONE.ENGLISH
(VP.LABORLEVELNAME3 = '0065')
AND VP.INPUNCHDTM is not Null
AND VP.LABORLEVELNAME5 NOT IN ('9', 'S')
AND (VP.STARTDTM > DATEADD(d, - 1, GETDATE()))
AND (VP.GMTSTARTDTM <= DATEADD(S, - TIMEZONE.GMTOFFSET, GETDATE()))
AND (VP.GMTENDDTM IS NULL OR VP.GMTENDDTM >= DATEADD(S, - TIMEZONE.GMTOFFSET, GETDATE()))
order by HOMELABORLEVELNM6, HOMELABORLEVELDSC7
The plan is to have the data from the first table be moved into the second by selecting a row and pressing a button with this script on it:
Time = system.date.now()
Name = self.session.custom.Name
ID = self.session.custom.Badge_Num
system.db.runPrepUpdate("UPDATE SM_CheckIn SET WHERE Time = ?, Name = ?, ID = ?",[Time, Name, ID])
It is not doing that, so I think something is wrong with this script. If so, can someone help point it out?
Perspective scripts can use the projects default database connection, assuming that one has been provided. See this thread for confirmation of that.
However, I don’t like to assume that the system will know the correct connection, (because multiple connections, multiple people working on the same project, etc…). So I always provide the db connection because then it will always work as expected (assuming the query and params are correct)
So I have tried adding the db connection and INSERT TO command. I still dont get the row moved into the other table. In the log i get this error:
com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File “”, line 6, in runAction at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:258) at jdk.internal.reflect.GeneratedMethodAccessor44.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.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(UPDATE IgnitionHud.dbo.SM_CheckIn SET WHERE Time = ?, Name = ?, ID = ?, [Mon Jul 18 13:06:03 CDT 2022, Macht III, Howard A, 0019378826], IgnitionHud, , false, false)
The way I read that statement and I could be wrong, wouldn’t be the first or last time. Your asking to update the database with a value “SET”. Your also asking it to only set that value WHERE the following is true. Your table is empty initially so nothing is going to match that statement.