Updating a row in a table

Updating a row in a table
 1# This script shows how to use the IDatabaseCommand object to update a single row in a table using
 2# an SQL update statement. It also shows how to assign parameters for the update statement.
 3
 4# While parameterless SQL is ok it is not recommended. It is always better to use parameters especially
 5# for user generated string values, which require character escaping and are prone to SQL injection.
 6# Also when using parameters the DBMS can more easily optimise SQL statements that are executed
 7# multiple times but with different values
 8
 9ui = impact.gui
10ot = ui.output_toolbox
11
12db = impact.active_database
13
14ot.clear()
15
16# IDatabaseConnection for current impact database
17c = db.connection
18
19ot.add("Connection Name: " + str(c.connection_name))
20ot.add("Connection Type: " + str(c.connection_type))
21ot.add("DBMS Type: " + str(c.dbms_type))
22ot.add("DBMS version: " + str(c.dbms_version.as_string))
23
24cmd = c.create_command()
25
26# assign the SQL including parameter placeholders
27cmd.sql = "update TEST set T_TEST = :name, T_DATE = :date, T_TIME = :time where T_KEY = :key"
28
29# assign parameters to command
30cmd.parameter("name").value = "An updated record"
31cmd.parameter("date").value = datetime.date.today()
32cmd.parameter("time").value = datetime.datetime.now().time()
33cmd.parameter("key").value = 11
34
35# when executing a simple SQL statement you don't need a transaction - one will automatically be used
36if cmd.execute_sql():
37    ot.add("Successfully updated record")
38
39else:
40    ot.add("Unable to execute command")
41
42