Having done some investigation, I can shed further light on the problem. Within an interactive Python session I connected to the database using mxODBC: the variable csr is an mxODBC cursor object. I made the following assignments: sql = 'insert into mytable (seq_num, data) values (?, ?)' data = '\x00\x00\x00' i.e. data is three NULLs. I then executed the command csr.execute(sql, (1, data)) The insert succeeded, but when I selected length(data) back from the table (using pgAdmin III) it was zero. I tried again, using data = '\x01\x01\x00\x01' and seq_num 2. This time, when I selected back the length of the data it was 2, namely the first two bytes. So it seems that at some point the string is being parsed as though it were a C-style NULL-terminated string. Having checked the dates on all my files, I think I may have downloaded a new mxODBC version since I last successfully did a binary insert, so it may be that mxODBC is the culprit, but it would be helpful if you could confirm that the Postgres parsing has not changed. I recall that I came across a similar problem when I tried to write a PL/Python function which returned binary data to a PL/pgSQL function: in order to get it to work I had to convert the data to base64Binary in the Python function and then convert it back in the PL/pgSQL function. That option is not open to me in this case.
|