Updating a table

[mjohnson0238]

I have 2 Named queries that generate tables, here they are:

SELECT 
	  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'
FROM 
	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
WHERE 
	(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

AND

Select *
From  SM_CheckIn

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?

Thank you

[JordanCClark]

I’m assuming Perspective, based on your script.

Since the script runs on the gateway, you’ll need to specify the db connection in runPrepUpdate()

[mjohnson0238]

As in: dbo.SM_CheckIN ?

[lrose]

The name of the DB connection in Ignition.

Gateway>Databases>Connections

system.db.runPrepUpdate(query,params,dbConnection)

[craigb]

@mjohnson0238

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])

Are you inserting rows or modifying rows.

system.db.runPrepUpdate("INSERT INTO order_requests (Reason, InternalDescription) VALUES (?,?)", [Reason, InternalDescription])

[mjohnson0238]

Oh ok, since my dbconnection is IgnitionHud it would be:

system.db.runPrepUpdate("UPDATE SM_CheckIn SET WHERE Time = ?, Name = ?, ID = ?",[Time, Name, ID], IgnitionHud)

[mjohnson0238]

the SM_CheckIn is an empty table at the start then when a row from the 1st table is selected and the button pressed it moves a row into the SM_CheckIn table

[craigb]

Then to me it should be an insert query.

system.db.runPrepUpdate("INSERT INTO SM_CheckIn (Time, Name, ID) VALUES (?,?,?)", [Time, Name, ID])

[craigb]

Also, none of my perspective update queries have the DB connection in. I assume if none is specified it uses the default ?

[lrose]

Perspective scripts can use the projects default database connection, assuming that one has been provided. See this thread for confirmation of that.

Perspective Workstation architecture question

Perspective is running inside the context of a particular project (by definition), so on the backend, we pass the project’s default database in to the running script’s context automatically. The individual scripting function implementations “know” to look for this context and extract it, so both things are true: All Perspective scripts run on the gateway Perspective scripts can assume the default database. See this thread if you’re interested in how that mechanism actually works.

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)

[mjohnson0238]

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)

[JordanCClark]

There should be a 'Caused by' line somewhere in the error.

@craigb

Also, none of my perspective update queries have the DB connection in. I assume if none is specified it uses the default ?

I've gotten into the habit of always specifying. That way there is no confusion over the scope.

[craigb]

@mjohnson0238

system.db.runPrepUpdate("UPDATE SM_CheckIn SET WHERE Time = ?, Name = ?, ID = ?",[Time, Name, ID], IgnitionHud)

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.

[craigb]

@JordanCClark

I’ve gotten into the habit of always specifying. That way there is no confusion over the scope.

I'm learning that there is one way to do things. I'll rephrase that, there is many ways to do things but only one way to do it properly. I'll start doing things the proper way

[mjohnson0238]

here is the caused by line:

Caused by: org.python.core.PyException: 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:36:42 CDT 2022, Holland, Chester C, 0002546828], IgnitionHud, , false, false)

... 36 common frames omitted

Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate(UPDATE IgnitionHud.dbo.SM_CheckIn SET WHERE Time = ?, Name = ?, ID = ?, [Mon Jul 18 13:36:42 CDT 2022, Holland, Chester C, 0002546828], IgnitionHud, , false, false)

... 35 common frames omitted

[pturmel]

That SQL is not valid. There should be your value assignments between SET and WHERE. The conditions after WHERE need to uniquely identify the existing row in the DB that you wish to alter.

You should look at the documentation for your database for the proper syntax for an UPDATE statement.

[craigb]

@craigb

system.db.runPrepUpdate("INSERT INTO SM_CheckIn (Time, Name, ID) VALUES (?,?,?)", [Time, Name, ID])

That should have worked, in theory.. I see in the error log it looks like its passing 4 variables as the name is comma separated, would that throw it off ?

[Mon Jul 18 13:36:42 CDT 2022, Holland, Chester C, 0002546828]

[pturmel]

@craigb

as the name is comma separated, would that throw it off ?

No, those are properly protected as query parameters.

[lrose]

As @pturmel pointed out, the SQL syntax you used is invalid. That being said, since it appears that you may need to INSERT or UPDATE IF EXISTS it seems that you may need a bit more complex of a query.

Like the MERGE INTO statement for MSSQL or ON DUPLICATE KEY UPDATE for MySQL/MariaDB (sorry don’t know the equivalent for postgres).

[pturmel]

@lrose

don’t know the equivalent for postgres

"ON CONFLICT"