Hi Oleg, Thanks for your quick response.

In writing a short test program I think I may have come across the problem, although again I'm not familiar enough with SQLObject to know where to from here... It seems that if I set the value of a blob column by setting a varialbe on my object, it gets written to the db just fine, but when I do it inside a transaction using sqlbuilder I have the error. Please see the code below.

Also, I'm at a complete loss as to what the server/client encodings are or even what version of sqlobject I'm using (it could be a couple of years old)... Obviously (based on the test program) I'm not setting the client encoding to anything other than the default... I've done a standard install of Postgres 8.3 and just went with the defaults there too... and I've had a look through the sqlobject code and can't find a version number anywhere. Could you give me a hint about where to look if this is important?

Test Code:

from sqlobject import *
from array import *

sqlhub.processConnection = connectionForURI('postgres://user:pass@localhost/mydb')

class MyObject(SQLObject):
    myInt =  IntCol(default=42)
    myBlob = BLOBCol(default=None) 

#This works:
myObject = MyObject()
myObject.myInt = 43
myObject.myBlob = array('H',range(256)).tostring()

#This doesn't work:
record = {'my_int':44, 'my_blob':array('H',range(256)).tostring()}
conn = sqlhub.getConnection()
tx = conn.transaction()
tx.query(conn.sqlrepr(sqlbuilder.Insert(MyObject, [record])))

#Traceback (most recent call last):
#  File "E:\SSL\Biometix\Code\Performix\trunk\demoBinaryAddError.py", line 28, in ?
#    tx.query(conn.sqlrepr(sqlbuilder.Insert(MyObject, [record])))
#  File "E:\SSL\Biometix\Code\Performix\trunk\sqlobject\dbconnection.py", line 846, in query
#    return self._dbConnection._query(self._connection, s)
#  File "E:\SSL\Biometix\Code\Performix\trunk\sqlobject\dbconnection.py", line 339, in _query
#    self._executeRetry(conn, conn.cursor(), s)
#  File "E:\SSL\Biometix\Code\Performix\trunk\sqlobject\dbconnection.py", line 334, in _executeRetry
#    return cursor.execute(query)
#psycopg2.DataError: invalid byte sequence for encoding "UTF8": 0x80
#HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".

On Thu, Jul 23, 2009 at 9:45 PM, Oleg Broytmann <phd@phd.pp.ru> wrote:
On Thu, Jul 23, 2009 at 04:54:11PM +1200, Aaron Robinson wrote:
> I'm wanting to write some binary data to the database - specifically the
> data is produced by something like:
>     array.array('H',[1,2,3]).tostring()
> and the database is Postgres 8.3 with a sqlobject.BLOBCol(default=None)
> column.
> Currently when I try this I receive this error:
>     DataError: invalid byte sequence for encoding "UTF8": 0x83
>     HINT:  This error can also happen if the byte sequence does not match
> the encoding expected by the server, which is controlled by
> "client_encoding".

  Ouch. Can you write a short test program that demonstrates the problem?
What version of SQLObject do you use? what are the encodings of client and

> I ran into a vaguely similar post, and the suggestion was to
> useUnicodeCol(), which I have tried and received the same result.

  UnicodeCol is for text, not binary data.

> Basically the main focus here is on keeping the storage size in the DB as
> low as possible, as we want to store many groups of numbers (all below
> 65536), and there is no need (or desire) to store them in individual
> columns.

  There is also PickleCol - a subtype of BLOBCol that can store any
pickleable data - the column (un)pickles data as necessary.
  A problem with the column (and your approach with array.tostring) is
that you cannot search or sort results by values in these columns.

    Oleg Broytmann            http://phd.pp.ru/            phd@phd.pp.ru
          Programmers don't die, they just GOSUB without RETURN.

sqlobject-discuss mailing list