Issue 1:
Migrated issue, originally created by Anonymous

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'}

Issue 2:
Migrated issue, originally created by Anonymous

(original reporter: ged) Is this intended? It used to work in 0.5.xxx...

Attachments: test_fk.py | 1765.patch