Updating mulitple rows in a table

Updating mulitple rows in a table
 1# This script shows how to use the IDatabaseCommand object to update multiple rows in a table using
 2# an SQL update statement. It shows how to iterate rows retrieved from a select statement and only
 3# update those records that require updating. It also shows how to assign parameters for the update
 4# statement and use a transaction to either update all records or none. If any of the records fail
 5# to update then all changes to the database are rolled back.
 6
 7# While parameterless SQL is ok it is not recommended. It is always better to use parameters especially
 8# for user generated string values, which require character escaping and are prone to SQL injection.
 9# Also when using parameters the DBMS can more easily optimise SQL statements that are executed
10# multiple times but with different values
11
12ui = impact.gui
13ot = ui.output_toolbox
14
15db = impact.active_database
16
17ot.clear()
18
19# IDatabaseConnection for current impact database
20c = db.connection
21
22ot.add("Connection Name: " + str(c.connection_name))
23ot.add("Connection Type: " + str(c.connection_type))
24ot.add("DBMS Type: " + str(c.dbms_type))
25ot.add("DBMS version: " + str(c.dbms_version.as_string))
26
27q = c.create_query()
28
29# assign the SQL for the records we will be iterating
30q.sql = "select T_KEY, T_TEST, T_DATE, T_TIME from TEST where T_KEY > 0"
31
32cmd = c.create_command()
33
34# assign the SQL including for updating records including parameter placeholders
35cmd.sql = "update TEST set T_TEST = :name, T_DATE = :date, T_TIME = :time where T_KEY = :key"
36
37if q.open():
38    ot.add("Successfully opened query: '" + str(q.sql) + "'")
39
40    # these parameters won't change for each record (current date and time)
41    cmd.parameter("date").value = datetime.date.today()
42    cmd.parameter("time").value = datetime.datetime.now().time()
43
44    # start a transaction so all updates are committed together
45    c.begin_transaction()
46
47    count = 0
48
49    # retrieving the columns outside of the while loop improves performance
50    key_c = q.column("T_KEY")
51    str_c = q.column("T_TEST")
52
53    success = True
54
55    # iterate all rows retreived by the IDatabaseQuery
56    while not q.is_eof:
57        count = count + 1
58
59        # decide which rows need updating (arbitary)
60        if str_c.value == "12":
61
62            # assign parameters to command
63            cmd.parameter("name").value = "Updated record " + str(key_c.value)
64            cmd.parameter("key").value = key_c.value
65
66            # execute update SQL statement
67            if cmd.execute_sql():
68                ot.add("Successfully updated record with T_KEY=" + str(key_c.value))
69
70            else:
71                ot.add("Unable to execute command")
72                success = False
73                break
74
75        # move to next row
76        if not q.move_next():
77            ot.add("Failed to move to next record")
78            success = False
79            break
80
81    ot.add("Successfully read " + str(count) + " rows")
82
83    if q.close():
84        ot.add("Successfully closed query")
85
86    # if all records successfully updated then commit to database
87    if success:
88        if c.commit():
89            ot.add("Successfully committed all records to database")
90
91    else:
92        if c.rollback():
93            ot.add("Rolled back all changes to database")
94
95else:
96    ot.add("Unable to open query")
97