update_query = """
UPDATE formulas_inprogress_copy
SET serial_number = ?,
amount_dispensed = ?,
ingredient_lot_number = ?,
ingredient_name = ?,
doser_id = ?
WHERE container_number = ?
AND formula_lot_number = ?
AND SUBSTRING(item_code FROM 1 FOR 10) = SUBSTRING(?, 1, 10);
"""
# Set the parameters for the update query
update_params = [
serial_number, # Ensure no extra spaces
amount_dispensed, # Properly formatted as a string
ingredient_lot_number, # Cleaned for any extra spaces
ingredient_name, # Ensure proper truncation and format
doser_id, # Ensure this is the correct type (integer)
container_number, # Ensure this is the correct type (integer)
formula_lot_number, # Cleaned for any extra spaces
item_code[:10] # Ensure correct substring and cleaned
]
# Log the SQL query and parameters for debugging purposes
print("SQL Query:", update_query)
print("Update Parameters:", update_params)
data = system.db.runPrepUpdate(update_query, args = update_params, project = "Doser_Machines_R11")
Where are you getting all your update parameter values from? I'm not seeing a call to read any tags or rows from a db. It looks like this is only part of the script. Post the full script, replacing/redacting any proprietary data as necessary.
Second, you are touching the db, DO NOT do this on a tag change event, use a Gateway Tag Change Event script.
Tag change event scripts only have access to the defined global scripting project, and will not be able to reach the scripting library of other projects, whereas a Gateway Tag Change Event script will be able to access the library of the project it is defined in.
If you are relying on a script in the library of the project you have open in the designer while testing this in the script console, it will fail when you try to run it in a normal tag change event.
It should be noted that the Script Console is not the same as a Tag Change Event (of either type).
Both of those run in a Gateway Scope, while the Script Console runs in Vison Client Scope.
This means that functions such as system.db.runPrepUpdate() have different calling signatures. This is due to things that can be assumed in a vision client (default database) not being assumed in the gateway.
You should never test scripts intended to be run in a gateway context, in the script console (unless you've gone through the work to insure it is run in a gateway context).
As @ryan.white has said, this should not be done in a Tag Value Change event, those events should execute in single digit milliseconds and reaching into a database even for the simplest of queries is just not capable of such performance. A Gateway Tag Change Event, not only has the project references that you need but is also immune to this requirement.
I am using perspective module, but running this script to check whether the value is written to DB or not. I was previously using named query for the same but having issue with missing data on the DB.
That doesn't answer the question. Show a screenshot of where you're actually putting this script, or learn and use standard terminology, e.g. tag change vs tag event script:
That is a tag value change script. That is independent of the perspective module.
Consider moving this entire script into a function inside of the scripting library of the project you have open, and calling that function from the gateway tag change event you set up for this.
Looking further at your code you are likely running into a scoping issue with system.db.runPrepUpdate. Gateway scoping (tag value change and Gateway Tag Change events run here) requires the specification of which db connection to execute the query against.
I'm surprised the prepUpdate call even works, project is not a defined parameter for that function. Have you checked your gateway's logs for any error messages after configuring the tag value change event?
Actually, no, not for Gateway Tag Change Events defined in the project. The various system.db.* functions that aren't named query calls are implemented such that the database parameter's presence is optional in any project scope, and only required in non-project gateway scope. The docs are wrong, as should be obvious from the placement of Perspective Scope (an extension of Gateway Scope).
The NQ API puts the project parameter first in the function signature in gateway scope, preventing it from being intelligently optional, and preventing non-gateway scopes from ever using cross-project NQs via such an optional parameter. And precludes deprecation for a better function signature.
I don't recall any IA staff member outing themself as responsible for this boo-boo.
I'd be delighted to see a new function, system.db.namedQuery() perhaps, replace system.db.runNamedQuery, with the argument order and optional processing fixed.
