Reading rows from a table with parameters

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