Save information automatically at the end of the count

Hello everyone

I have an Expression TAG that keeps me counting how much a Boolean Opc tag is activated and for when it is deactivated.

and I require that at the time of deactivation I save the information in a SQL database

image

This seems like a task for a transaction group. You can set them to trigger on an event (tag deactivation in your case).

User manual pages for transaction groups: SQL Bridge (Transaction Groups) - Ignition User Manual 8.1 - Ignition Documentation

2 Likes

I can see 3 issues with your code, but what was your question?

The issues I see are:

  1. you need to exclude initialChange events in your first if conditions
  2. you’re supplying the query with a QualifiedValue (Contador). You need to add .value to the end to read the value of the tag
  3. you’re executing the insert statement whenever the tag changes value, not only when it is False

ok, I understand and how do you go to make the change on and off that the label does

do you have any example?

If I understand your question correctly,

if currentValue.value == False and currentValue.value != previousValue.value and not initialChange:

send me the following error “mismatched input " expecting DEDENT”

Python syntax requires all your line indents to be right. Your if statement is on a different indent to the function’s description string above. You need to push the if statement back to the same column as the line above

1 Like

I have a new error

([default]CONTEO DE FALLA/Falla safety fance open 1, valueChanged) Error executing tag event script: Traceback (most recent call last): File “tagevent:valueChanged”, line 8, in valueChanged 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 sun.reflect.GeneratedMethodAccessor52.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO PAROS (Linea, Maquina, TipoParo, TiempoParo, Fecha) VALUES (?, ?, ?, ?, ?,?), [Item, Prueba, Prueba, 0.0, Fri Apr 09 17:25:51 CDT 2021], TOPY_MW, , false, false)

You have an extra question mark in your query.

I have a new error

if currentValue.value == False and currentValue.value != previousValue.value and not initialChange:
					INS = "INSERT INTO PAROS ( Linea, Maquina, TipoParo, TiempoParo, Fecha) VALUES ( ?, ?, ?, ?, ?)"
								
					Contador = system.tag.read("[.]MemTimeDiff")
					Fecha = system.date.now()
								
					system.db.runPrepUpdate(INS,[ "Item", "Prueba", "Prueba", Contador.value, Fecha],"TOY");

([default] FAIL COUNT / Fail safety fance open 1, valueChanged) Error executing tag event script: Traceback (last most recent call): File "<tagevent: valueChanged>", line 8, in valueChanged in com .inductiveautomation.ignition .common.script.builtin.AbstractDBUtilities.error (AbstractDBUtilities.java:362) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate (AbstractDBUtilities.java:258) at sun.java:258 : 258) Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke (Unknown source) at java.lang.reflect.Method.invoke (Unknown source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO PAROS ( Linea, Maquina, TipoParo, TiempoParo, Fecha) VALUES ( ?, ?, ?, ?, ?), [Item, Prueba, Prueba, 0.21695000330607098, Mon Apr 12 13:45:24 CDT 2021], TOY, , false, false)

Post your code and the full error (little blue “full” link in the error popup window). Please use the “Preformatted Text” button (or ``` before and after your code/error snippets) to format your code/errors so that they are easier to read/decipher.

The error to the PrepUpdate you’re posting doesn’t match your code.

Your code:

INSERT INTO PAROS ( Linea, Maquina, TipoParo, TiempoParo, Fecha) VALUES ( ?, ?, ?, ?, ?)

The Error:

INSERT INTO STOP (Line, Machine, StopType, StopTime, Date) VALUES (?,?,?,?,?)
2 Likes

Just a heads up that we do not get notifications when you edit your replies so you’re a lot less likely to get responses since we have to click on the topic to see if you’ve made any edits. You should really put your responses in a new reply. This will move your topic to the top of the list for us to see and will make it much easier for anyone else who reads this topic in the future to follow the history of replies.

It looks like your error still doesn’t match your script. Your script has “TOY” for the db connection in the runPrepUpdate() function while your error has “TOPY_MW”. Also, that still doesn’t look like the full error trace…

I am creating an event where for a timer, where when stopping the timer it saves the data in my database.

but at the moment of calling the insertion of the data it marks the following error.

Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO PAROS ( Linea, Maquina, TipoParo, TiempoParo, Fecha) VALUES ( ?, ?, ?, ?, ?), [Item, Prueba, Prueba, 0.21695000330607098, Mon Apr 12 13:45:24 CDT 2021], TOY, , false, false)

This is my code:

if currentValue.value == False and currentValue.value != previousValue.value and not initialChange:
                    INS = "INSERT INTO PAROS ( Linea, Maquina, TipoParo, TiempoParo, Fecha) VALUES ( ?, ?, ?, ?, ?)"
                                
                    Contador = system.tag.read("[.]MemTimeDiff")
                    Fecha = system.date.now()
                                
                    system.db.runPrepUpdate(INS,[ "Item", "Prueba", "Prueba", Contador.value, Fecha],"TOY")

Delete the semi-colon at the very end of your script.

I think I already know that my “counter” variable captures a string and wants to save it as time in the database

If you send the full error message, we can offer more help. Earlier, I forgot you are running this script from a tag event (earlier instructions were for error popups when running the script in the designer/client). To get the full error message from the gateway log you need to go to your Gateway Webpage >> Status >> Logs, find your error, and click the “+” sign to the right of the error message and copy/paste that full error message here.

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 8, in valueChanged 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 sun.reflect.GeneratedMethodAccessor52.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO PAROS (Linea, Maquina, TipoParo, TiempoParo, Fecha) VALUES (?, ?, ?, ?, ?), [Item, Prueba, Prueba, 0.03338332970937093, Mon Apr 12 16:58:51 CDT 2021], TOY, , false, false)

at org.python.core.Py.JavaError(Py.java:495)

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 sun.reflect.GeneratedMethodAccessor52.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.python.core.PyReflectedFunction.call(PyReflectedFunction.java:186)

at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.call(ScriptManager.java:431)

at org.python.core.PyObject.call(PyObject.java:422)

at org.python.core.PyObject.call(PyObject.java:426)

at org.python.pycode._pyx96381138.valueChanged$1(:8)

at org.python.pycode._pyx96381138.call_function()

at org.python.core.PyTableCode.call(PyTableCode.java:165)

at org.python.core.PyBaseCode.call(PyBaseCode.java:301)

at org.python.core.PyFunction.function___call__(PyFunction.java:376)

at org.python.core.PyFunction.call(PyFunction.java:371)

at org.python.core.PyFunction.call(PyFunction.java:361)

at org.python.core.PyFunction.call(PyFunction.java:356)

at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:649)

at com.inductiveautomation.ignition.common.sqltags.scripts.TagScriptManager$FunctionInvokerImpl.run(TagScriptManager.java:493)

at com.inductiveautomation.ignition.common.sqltags.scripts.AbstractTagScript.invoke(AbstractTagScript.java:33)

at com.inductiveautomation.ignition.common.sqltags.scripts.TagScriptManager$Task.invoke(TagScriptManager.java:442)

at com.inductiveautomation.ignition.common.sqltags.scripts.TagScriptManager$TagScriptDispatcher.run(TagScriptManager.java:405)

at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$ThrowableCatchingRunnable.run(BasicExecutionEngine.java:518)

at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.util.concurrent.FutureTask.run(Unknown Source)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(Unknown Source)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.lang.Thread.run(Unknown Source)

Caused by: org.python.core.PyException: Traceback (most recent call last): File "", line 8, in valueChanged 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 sun.reflect.GeneratedMethodAccessor52.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO PAROS (Linea, Maquina, TipoParo, TiempoParo, Fecha) VALUES (?, ?, ?, ?, ?), [Item, Prueba, Prueba, 0.03338332970937093, Mon Apr 12 16:58:51 CDT 2021], TOY, , false, false)

... 31 common frames omitted

Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO PAROS (Linea, Maquina, TipoParo, TiempoParo, Fecha) VALUES (?, ?, ?, ?, ?), [Item, Prueba, Prueba, 0.03338332970937093, Mon Apr 12 16:58:51 CDT 2021], TOY, , false, false)

... 30 common frames omitted

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Operand type clash: float is incompatible with time

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:306)

at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.executeUpdate(SRConnectionWrapper.java:955)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepUpdate(SRConnectionWrapper.java:180)

at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._runPrepStmt(GatewayDBUtilities.java:132)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:256)

... 28 common frames omitted

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Operand type clash: float is incompatible with time

You do have a type compatibility issue but it’s not due to a string, but rather a float trying to write to a Time column. I’m assuming your TiempoParo column is a Time datatype and you are trying to write a float value (0.03338…) to it.

Edit: Looks like you already figured this out based on your new topic. You really should keep these together so that people have context, and those trying to help you (like me) don’t spend time replying when you clearly already know the problem…

FYI You really need to put anything that requires formatting to read (eg python or errors) into preformatted blocks, otherwise it’s incredibly difficult to read. Kudos to @WillMT10 for finding what he did!

3 Likes