Deleting a row from a table

Deleting a row from a table
 1# This script shows how to use the IDatabaseCommand object to delete a single row from a table using
 2# an SQL delete statement. An example is shown using both a parameterless and parameterised 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
 9
10ui = impact.gui
11ot = ui.output_toolbox
12
13db = impact.active_database
14
15ot.clear()
16
17# IDatabaseConnection for current impact database
18c = db.connection
19
20ot.add("Connection Name: " + str(c.connection_name))
21ot.add("Connection Type: " + str(c.connection_type))
22ot.add("DBMS Type: " + str(c.dbms_type))
23ot.add("DBMS version: " + str(c.dbms_version.as_string))
24
25cmd = c.create_command()
26
27# assign the SQL without any parameters
28cmd.sql = "delete from TEST where T_KEY = 30"
29
30# when executing a simple SQL statement you don't need a transaction - one will automatically be used
31if cmd.execute_sql():
32    ot.add("Successfully deleted record")
33
34else:
35    ot.add("Unable to execute command")
36
37# assign the SQL including a parameter placeholder for T_KEY
38cmd.sql = "delete from TEST where T_KEY = :key"
39
40# assign the parameter
41cmd.parameter(1).value = 31
42
43# when executing a simple SQL statement you don't need a transaction - one will automatically be used
44if cmd.execute_sql():
45    ot.add("Successfully deleted record")
46
47else:
48    ot.add("Unable to execute command")
49
50