<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">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.</p>
<p dir="auto">This causes <strong>ORA-12704: character set mismatch</strong></p>
<p dir="auto">Script:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="#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 &quot;*****************************&quot;
print &quot;server_version_info: &quot;, engine.dialect.server_version_info
print &quot;_supports_char_length: &quot;, engine.dialect._supports_char_length
print &quot;supports_unicode_binds: &quot;, engine.dialect.supports_unicode_binds
print &quot;use_ansi:&quot;, engine.dialect.use_ansi
print &quot;*****************************&quot;

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

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


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

zones_table = Table(&quot;zones&quot;, metadata,
    Column(&quot;zoneid&quot;, Unicode(255), primary_key=True)
)

zipcodes_table = Table('zipcodes', metadata,
    Column(&quot;zipcode&quot;, 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"><pre class="notranslate"><code class="notranslate">#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
</code></pre></div>
<p dir="auto"><strong>output:</strong></p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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 &quot;ora8.py&quot;, line 59, in &lt;module&gt;
    ord.defaultzones
  File &quot;/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/orm/attributes.py&quot;, line 163, in __get__
    instance_dict(instance))
  File &quot;/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/orm/attributes.py&quot;, line 382, in get
    value = callable_(passive=passive)
  File &quot;/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/orm/strategies.py&quot;, line 629, in __call__
    result = q.all()
  File &quot;/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/orm/query.py&quot;, line 1470, in all
    return list(self)
  File &quot;/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/orm/query.py&quot;, line 1582, in __iter__
    return self._execute_and_instances(context)
  File &quot;/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/orm/query.py&quot;, line 1587, in _execute_and_instances
    mapper=self._mapper_zero_or_none())
  File &quot;/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py&quot;, line 760, in execute
    clause, params or {})
  File &quot;/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py&quot;, line 1157, in execute
    params)
  File &quot;/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py&quot;, line 1237, in _execute_clauseelement
    return self.__execute_context(context)
  File &quot;/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py&quot;, line 1268, in __execute_context
    context.parameters[0](0), context=context)
  File &quot;/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py&quot;, line 1367, in _cursor_execute
    context)
  File &quot;/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py&quot;, line 1360, in _cursor_execute
    context)
  File &quot;/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.3.2dev-py2.6-linux-x86_64.egg/sqlalchemy/engine/default.py&quot;, 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'}"><pre class="notranslate"><code class="notranslate">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 &lt;module&gt;
    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'}
</code></pre></div>
<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">(original reporter: ged) Is this intended? It used to work in 0.5.xxx...</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/1765/test_fk.py">test_fk.py</a> | <a href="../wiki/imported_issue_attachments/1765/1765.patch">1765.patch</a></p>