Reading rows from a table with parameters

Reading rows from a table with parameters
  1def var_type(value):
  2    """VBScript VarType equivalent - returns numeric type code."""
  3    if value is None:
  4        return 1  # vbNull
  5    elif isinstance(value, bool):
  6        return 11  # vbBoolean
  7    elif isinstance(value, int):
  8        return 2  # vbInteger
  9    elif isinstance(value, float):
 10        return 5  # vbDouble
 11    elif isinstance(value, str):
 12        return 8  # vbString
 13    elif isinstance(value, (datetime.date, datetime.datetime)):
 14        return 7  # vbDate
 15    else:
 16        return 9  # vbObject
 17
 18
 19# This script shows how to use the IDatabaseQuery object to read rows from a table using an SQL
 20# select statement. It also shows how to assign parameters for the select statement, how to determine
 21# the data types of the columns, how to read multiple rows and read values from each column.
 22
 23# While parameterless SQL is ok it is not recommended. It is always better to use parameters especially
 24# for user generated string values, which require character escaping and are prone to SQL injection.
 25# Also when using parameters the DBMS can more easily optimise SQL statements that are executed
 26# multiple times but with different values
 27
 28ui = impact.gui
 29ot = ui.output_toolbox
 30
 31db = impact.active_database
 32
 33def show_column(c):
 34    show_value(c.name, c.value)
 35
 36def show_value(c, v):
 37
 38    # output a value
 39    s = "   " + str(c) + "="
 40
 41    if var_type(v) == 1:  # vb_null
 42    s =str(s) + "NULL"
 43elif var_type(v) == 8:  # vb_string
 44s =str(s) + "'" + str(v) + "'"
 45else:
 46    s = s + str(v)
 47
 48ot.add(str(s) + " (VarType=" + str(var_type(v)) + ")")
 49
 50ot.clear()
 51
 52# IDatabaseConnection for current impact database
 53c = db.connection
 54
 55ot.add("Connection Name: " + str(c.connection_name))
 56ot.add("Connection Type: " + str(c.connection_type))
 57ot.add("DBMS Type: " + str(c.dbms_type))
 58ot.add("DBMS version: " + str(c.dbms_version.as_string))
 59
 60# create an IDatabaseQuery
 61q = c.create_query()
 62
 63# assign the SQL including parameter placeholders
 64q.sql = "select * from TEST where T_KEY = :key or T_TEST like :test order by T_KEY"
 65
 66# assign the TableName so that the column types can be determined
 67q.table_name = "TEST"
 68
 69# determine the number of parameters required
 70ot.add("Expected Parameters: " + str(q.parameter_count))
 71
 72# find parameters by either integer or name
 73p1 = q.parameter(1)
 74p2 = q.parameter("test")
 75
 76# assign appropriate parameter values
 77p1.value = 1
 78p2.value = "A%"
 79
 80# check parameters have been set
 81ot.add("Parameter: " + p1.name + "=" + str(p1.value))
 82ot.add("Parameter: " + p2.name + "=" + str(p2.value))
 83
 84# open the SQL select statement
 85if q.open():
 86    ot.add("Successfully opened query: '" + str(q.sql) + "'")
 87
 88    # retreive information about the columns returned
 89    ot.add("Column Count: " + str(q.column_count))
 90
 91    for i in range(1, q.column_count  + 1):
 92        c = q.column(i)
 93
 94        # determine the name and type of each column
 95        ot.add("Column: '" + c.name + "', Type: " + str(c.type))
 96
 97    # retrieving the columns outside of the while loop improves performance
 98    key_c = q.column("T_KEY")
 99    str_c = q.column("T_TEST")
100    bool_c = q.column("T_BOOL")
101    real_c = q.column("T_REAL")
102    dist_c = q.column("T_DIST")
103
104    count = 0
105
106    # iterate all rows retreived by the IDatabaseQuery
107    while not q.is_eof:
108        count = count + 1
109
110        # output values from various columns for each row
111        ot.add("Row " + str(count) + ":")
112
113        show_column(key_c)
114        show_column(str_c)
115        show_column(bool_c)
116        show_column(real_c)
117        show_column(dist_c)
118
119        # use GetDateTime to combine separate date/time columns
120        show_value("T_DATE/T_TIME", q.get_date_time("T_DATE", "T_TIME"))
121
122        # move to the next row
123        if not q.move_next():
124            ot.add("Failed to move to next record")
125            break
126
127    ot.add("Successfully read " + str(count) + " rows")
128
129    # close the query
130    if q.close():
131        ot.add("Successfully closed query")
132
133else:
134    ot.add("Unable to open query")
135
136
137