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()