Inserting a row into a table

Inserting a row into a table
 1# This script shows how to use the IDatabaseCommand object to insert a single row into a table using
 2# an SQL insert statement within a transaction. It also shows how to generate a unique key and assign
 3# parameters for the insert statement.
 4
 5# While parameterless SQL is ok it is not recommended. It is always better to use parameters especially
 6# for user generated string values, which require character escaping and are prone to SQL injection.
 7# Also when using parameters the DBMS can more easily optimise SQL statements that are executed
 8# multiple times but with different values
 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 including parameter placeholders
28cmd.sql = "insert into TEST (T_KEY, T_TEST, T_BOOL, T_REAL, T_INT, T_DIST, T_DATE, T_TIME) values (:key, :name, :bool, :real, :int, :dist, :date, :time)"
29
30# determine the number of parameters required
31ot.add("Expected Parameters: " + str(cmd.parameter_count))
32
33# when inserting you should always create a transaction, this will ensure NextUniqueKey
34# locks the table to prevent multiple users trying to insert records at the same time
35c.begin_transaction()
36
37# determine next unique key for TEST table primary key
38key = c.next_unique_key("TEST", "")
39
40ot.add("Next Unique Key: " + str(key))
41
42# assign parameters to command
43cmd.parameter("key").value = key
44cmd.parameter("name").value = "A new row"
45cmd.parameter("bool").value = True
46cmd.parameter("real").value = 150.50
47cmd.parameter("int").value = 60
48cmd.parameter("dist").is_null = True
49cmd.parameter("date").value = datetime.date(2010, 2, 25)
50cmd.parameter("time").value = datetime.time(23, 2, 30)
51
52# execute the SQL statement and commit to database
53if cmd.execute_sql():
54    ot.add("Successfully inserted record with TEST.T_KEY=" + str(key))
55
56    if c.commit():
57        ot.add("Successfully committed record to database")
58
59else:
60    ot.add("Unable to execute command")
61
62    if c.rollback():
63        ot.add("Rolled back all changes to database")
64
65