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.
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 - Gateway​
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. [optional] |
| 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. [optional] |
| 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. [optional] |
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 - Vision and Perspective​
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. [optional] |
| String | tx | A transaction identifier. If omitted, the update will be executed in its own transaction. [optional] |
| 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. [optional] |
| 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. [optional] |
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, Perspective Session
Code Examples​
# This code acknowledges all unacknowledged alarms # and shows the user how many alarms were acknowledged.
rowsChanged = system.db.runUpdateQuery("UPDATE alarmstatus SET unacknowledged = 0")
system.gui.messageBox("Acknowledged %d alarms" % rowsChanged)
# This example inserts name, description, and building information into a table called 'machines'. Demonstrates the ability to retrieve a newly created key value.
# Get the name, description, and building information.
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 'machine_building_mapping' table.
system.db.runUpdateQuery("INSERT INTO machine_building_mapping " + "(machine_id, building) VALUES (%d, %d)" %(id, building))