Updating and reading binary data

Updating and reading binary data
  1import win32com.client
  2
  3# This script shows how to use the IDatabaseQuery object to read BLOB data from a row and use an
  4# IDatabaseCommand object to update BLOB data in a table using an SQL update statement.
  5
  6blob_source_file = r"D:\image.jpg"
  7blob_destination_file = r"D:\image2.jpg"
  8
  9ui = impact.gui
 10ot = ui.output_toolbox
 11
 12db = impact.active_database
 13
 14ad_type_binary = 1
 15ad_save_create_over_write = 2
 16
 17
 18def read_binary_file(file_name):
 19
 20    # create stream object
 21    stream = win32com.client.Dispatch("ADODB.Stream")
 22
 23    # load the file data from disk to stream object
 24    stream.Open()
 25    stream.Type = ad_type_binary
 26    stream.LoadFromFile(file_name)
 27
 28    # get binary data from the object
 29    _result = stream.Read()
 30    return _result
 31
 32def save_binary_data(file_name, byte_array):
 33    stream = win32com.client.Dispatch("ADODB.Stream")
 34
 35    # open the stream and write binary data to the object
 36    stream.Open()
 37    stream.Type = ad_type_binary
 38
 39    # write binary data to the file
 40    stream.Write(byte_array)
 41    stream.SaveToFile(file_name, ad_save_create_over_write)
 42
 43def update_test_value():
 44    cmd = c.create_command()
 45
 46    # assign the SQL including parameter placeholders
 47    cmd.sql = "update TEST set T_DATA = :data where T_KEY = :key"
 48
 49    # assign parameters to command
 50    cmd.parameter("data").value = read_binary_file(blob_source_file)
 51    cmd.parameter("key").value = 1
 52
 53    # when executing a simple SQL statement you don't need a transaction - one will automatically be used
 54    if cmd.execute_sql():
 55        ot.add("Successfully updated record")
 56
 57    else:
 58        ot.add("Unable to execute command")
 59
 60def read_test_value():
 61    q = c.create_query()
 62
 63    q.sql = "select T_DATA, " + str(c.blob_length("T_DATA")) + " from TEST where T_KEY = :key"
 64    q.parameter("key").value = 1
 65
 66    if q.open():
 67        if not q.is_eof:
 68            blob_c = q.column("T_DATA")
 69            blob_len_c = q.column(2)
 70
 71            if blob_c.is_null:
 72                ot.add("TDATA=NULL")
 73            else:
 74                ot.add("TDATA BLOB Length=" + str(blob_len_c.value))
 75
 76                # we need the BLOB value as a VARIANT array of bytes
 77                blob_c.bytes_as_variant = False
 78
 79                save_binary_data(blob_destination_file, blob_c.value)
 80
 81                ot.add("Successfully saved BLOB to '" + str(blob_destination_file) + "'")
 82
 83        else:
 84            ot.add("Unable to locate T_TEST record")
 85
 86        if not q.close():
 87            ot.add("Unable to close query")
 88
 89    else:
 90        ot.add("Unable to open query")
 91
 92
 93ot.clear()
 94
 95
 96# IDatabaseConnection for current impact database
 97c = db.connection
 98
 99ot.add("Connection Name: " + str(c.connection_name))
100ot.add("Connection Type: " + str(c.connection_type))
101ot.add("DBMS Type: " + str(c.dbms_type))
102ot.add("DBMS Version: " + str(c.dbms_version.as_string))
103
104# save BLOB into TEST table
105update_test_value()
106
107# read BLOB from TEST table
108read_test_value()