Oracle 8 convert to unicode problem when Column type is inferred from ForeignKey

On certain (at least many to many) relationships, the oracle dialect fails to convert unicode to string on oracle 8 if the Column type is not specified, but rather inferred from ForeignKey.

This causes ORA-12704: character set mismatch

Script:

#SHOW unicode problem with oracle 8 when inferring Column type

from sqlalchemy import *
from sqlalchemy.orm import *

engine = create_engine('oracle://user:pass@ip:1521/sid',echo=True)
engine.connect()
print "*****************************"
print "server_version_info: ", engine.dialect.server_version_info
print "_supports_char_length: ", engine.dialect._supports_char_length
print "supports_unicode_binds: ", engine.dialect.supports_unicode_binds
print "use_ansi:", engine.dialect.use_ansi
print "*****************************"

metadata = MetaData()
Session = sessionmaker(bind=engine)
DBSession = Session()

orders_table = Table("orders", metadata,
    Column("orderid", Unicode(255), primary_key=True),
    Column("shipzipcode", ForeignKey('zipcodes.zipcode'))
)


zipzones_table = Table("zipzones", metadata,
    Column("zoneid", ForeignKey('zones.zoneid'), primary_key=True),
    Column("zipcode", Unicode(255))
)

zones_table = Table("zones", metadata,
    Column("zoneid", Unicode(255), primary_key=True)
)

zipcodes_table = Table('zipcodes', metadata,
    Column("zipcode", Unicode(9), primary_key=True)
)

class Order(object):
    pass

class Zone(object):
    pass

mapper(Zone, zones_table)

mapper(Order, orders_table, 
    properties={'defaultzones': relationship(Zone,
        secondary=zipzones_table,
        primaryjoin=orders_table.c.shipzipcode==zipzones_table.c.zipcode,
        foreign_keys=[zones_table.c.zoneid](zipzones_table.c.zipcode,),
        order_by=zones_table.c.zoneid)}
)

ord=DBSession.query(Order).filter_by(orderid=u'0109009OICY').one()

ord.defaultzones

output:

2010-09-07 22:13:01,923 INFO sqlalchemy.engine.base.Engine.0x...0890 SELECT USER FROM DUAL
2010-09-07 22:13:01,928 INFO sqlalchemy.engine.base.Engine.0x...0890 {}
*****************************
server_version_info:  (8, 1, 7, 4, 0)
_supports_char_length:  False
supports_unicode_binds:  False
use_ansi: False
*****************************
2010-09-07 22:13:02,426 INFO sqlalchemy.engine.base.Engine.0x...0890 BEGIN
2010-09-07 22:13:02,427 INFO sqlalchemy.engine.base.Engine.0x...0890 SELECT orders.orderid AS orders_orderid, orders.shipzipcode AS orders_shipzipcode
FROM orders
WHERE orders.orderid = :orderid_1
2010-09-07 22:13:02,427 INFO sqlalchemy.engine.base.Engine.0x...0890 {'orderid_1': '0109009OICY'}
2010-09-07 22:13:02,552 INFO sqlalchemy.engine.base.Engine.0x...0890 SELECT zones.zoneid AS zones_zoneid
FROM zones, zipzones
WHERE :param_1 = zipzones.zipcode AND zones.zoneid = zipzones.zoneid ORDER BY zones.zoneid
2010-09-07 22:13:02,552 INFO sqlalchemy.engine.base.Engine.0x...0890 {'param_1': u'33015'}
Traceback (most recent call last):
  File "ora8.py", line 59, in <module>
    ord.defaultzones
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/orm/attributes.py", line 163, in __get__
    instance_dict(instance))
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/orm/attributes.py", line 382, in get
    value = callable_(passive=passive)
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/orm/strategies.py", line 629, in __call__
    result = q.all()
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/orm/query.py", line 1470, in all
    return list(self)
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/orm/query.py", line 1582, in __iter__
    return self._execute_and_instances(context)
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/orm/query.py", line 1587, in _execute_and_instances
    mapper=self._mapper_zero_or_none())
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py", line 760, in execute
    clause, params or {})
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1157, in execute
    params)
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1237, in _execute_clauseelement
    return self.__execute_context(context)
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1268, in __execute_context
    context.parameters[0](0), context=context)
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1367, in _cursor_execute
    context)
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1360, in _cursor_execute
    context)
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/engine/default.py", line 299, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12704: character set mismatch
 'SELECT zones.zoneid AS zones_zoneid \nFROM zones, zipzones \nWHERE :param_1 = zipzones.zipcode AND zones.zoneid = zipzones.zoneid ORDER BY zones.zoneid' {'param_1': u'33015'}

Comments (3)