system.db.runUpdateQuery

This function is used in Python Scripting.

Description

Runs a query against a database connection, returning the number of rows affected. Typically this is an UPDATE, INSERT, or DELETE query. If no database is specified, or the database is the empty-string "", then the current project's default database connection will be used.

Note that you may want to use the runPrepUpdate query if your query is constructed with user input (to avoid the user's input from breaking your syntax) or if you need to insert binary or BLOB data.

Client Permission Restrictions

Permission Type: Legacy Database Access

Client access to this scripting function is blocked to users that do not meet the role/zone requirements for the above permission type. This function is unaffected when run in the Gateway scope.

Syntax

system.db.runUpdateQuery(query, database, tx, getKey, skipAudit)

Parameters

Type	Parameter	Description
String	query	A SQL query, usually an INSERT, UPDATE, or DELETE query, to run.
String	database	The name of the database connection to execute against.
String	tx	A transaction identifier. If omitted, the update will be executed in its own transaction.
Boolean	getKey	A flag indicating whether or not the result should be the number of rows returned (getKey=0) or the newly generated key value that was created as a result of the update (getKey=1). Not all databases support automatic retrieval of generated keys.
Boolean	skipAudit	A flag which, if set to true, will cause the update query to skip the audit system. Useful for some queries that have fields which won't fit into the audit log.

Returns

Integer - The number of rows affected by the query, or the key value that was generated, depending on the value of the getKey flag.

Scope

Gateway

Syntax

system.db.runUpdateQuery(query, database, tx, getKey, skipAudit)

Parameters

Type	Parameter	Description
String	query	A SQL query, usually an INSERT, UPDATE, or DELETE query, to run.
String	database	The name of the database connection to execute against. If omitted or "", the project's default database connection will be used.
String	tx	A transaction identifier. If omitted, the update will be executed in its own transaction.
Boolean	getKey	A flag indicating whether or not the result should be the number of rows returned (getKey=0) or the newly generated key value that was created as a result of the update (getKey=1). Not all databases support automatic retrieval of generated keys.
Boolean	skipAudit	A flag which, if set to true, will cause the update query to skip the audit system. Useful for some queries that have fields which won't fit into the audit log.

Returns

Integer - The number of rows affected by the query, or the key value that was generated, depending on the value of the getKey flag.

Scope

Vision Client

Code Examples

Example #1

# This code would acknowledge all unacknowledged alarms # and show the user how many alarms were acknowledged.
rowsChanged = system.db.runUpdateQuery("UPDATE alarmstatus SET unacknowledged = 0")
system.gui.messageBox("Acknowledged %d alarms" % rowsChanged)

Example #2

# This code would insert a new recipe step into a recipe table, after asking the user how many gallons of syrup should be added on this recipe step.
inputText = system.db.inputBox("How many gallons?", "12.3")
# Make sure the user didn't hit cancel
if inputText != None:
   # Make sure the input is a number
   gallons = float(inputText)
   # Detect the next step number by adding 1 to the last step number
   nextStepNum = system.db.runScalarQuery("SELECT MAX(StepNum) + 1 FROM RecipeSteps")
   # Insert recipe step
   system.db.runUpdateQuery("INSERT INTO RecipeSteps (StepNum, Gallons) VALUES (%d, %f)" % (nextStepNum, gallons))
   insertQuery = "INSERT INTO RecipeSteps (StepNum, Gallons) VALUES (%d, %f)"
   system.db.runUpdateQuery(insertQuery % (nextStepNum, gallons))

Example #3

# This example inserts a new user and gives it the 'admin' role.  Demonstrates the ability to retrieve a newly created key value.
# get the username/password
name = event.source.parent.getComponent('Name').text
desc = event.source.parent.getComponent('Description').text
building = event.source.parent.getComponent('Building').selectedValue
  
# insert the value
id = system.db.runUpdateQuery("INSERT INTO machines (machine_name, description) " + "VALUES ('%s', '%s')" %(name, desc), getKey=1)
  
# add a row to the user role mapping table
system.db.runUpdateQuery("INSERT INTO machine_building_mapping " + "(machine_id, building) VALUES (%d, %d)" %(id, building))