Inserting a row into a table

Inserting a row into a table
 1import datetime
 2
 3# This script shows how to use the IDatabaseCommand object to insert a single row into a table using
 4# an SQL insert statement within a transaction. It also shows how to generate a unique key and assign
 5# parameters for the insert statement.
 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
27cmd = c.create_command()
28
29# assign the SQL including parameter placeholders
30cmd.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)"
31
32# determine the number of parameters required
33ot.add("Expected Parameters: " + str(cmd.parameter_count))
34
35# when inserting you should always create a transaction, this will ensure NextUniqueKey
36# locks the table to prevent multiple users trying to insert records at the same time
37c.begin_transaction()
38
39# determine next unique key for TEST table primary key
40key = c.next_unique_key("TEST", "")
41
42ot.add("Next Unique Key: " + str(key))
43
44# assign parameters to command
45cmd.parameter("key").value = key
46cmd.parameter("name").value = "A new row"
47cmd.parameter("bool").value = True
48cmd.parameter("real").value = 150.50
49cmd.parameter("int").value = 60
50cmd.parameter("dist").is_null = True
51cmd.parameter("date").value = datetime.datetime(2010, 2, 25, 0, 0, 0)
52cmd.parameter("time").value = datetime.datetime.combine(datetime.date.today(), datetime.time(23, 2, 30))
53
54# execute the SQL statement and commit to database
55if cmd.execute_sql():
56    ot.add("Successfully inserted record with TEST.T_KEY=" + str(key))
57
58    if c.commit():
59        ot.add("Successfully committed record to database")
60
61else:
62    ot.add("Unable to execute command")
63
64    if c.rollback():
65        ot.add("Rolled back all changes to database")
66
67