1. Mike Bayer Avatar Mike Bayer
  2. Untitled project
  3. sqlalchemy_old
  4. Issues

cx_Oracle bug in svn when compiled WITH_UNICODE

Issue #1670 resolved
Former user created an issue 
virtualenv --no-site-packages cxo-bug
cd cxo-bug
. ./bin/activate
export WITH_UNICODE=1
pip install cx_Oracle
cd ../svn/sqlalchemy/

svn info

(Just to check which version I'm using)

Path: .
URL: http://svn.sqlalchemy.org/sqlalchemy/trunk
Repository Root: http://svn.sqlalchemy.org
Repository UUID: 8cd8332f-0806-0410-a4b6-96f4b9520244
Revision: 6707


python setup.py install
python -c "import sqlalchemy as sa; from sqlalchemy.orm import *; e = sa.create_engine(u'oracle://cts:kebije0f@TUCKER'); conn = e.connect()"

It dies on 

self.normalize_name(connection.execute('SELECT USER FROM DUAL').scalar())

I get the same error with:

python -c "import sqlalchemy as sa; from sqlalchemy.orm import *; e = sa.create_engine(u'oracle://cts:kebije0f@TUCKER'); e.dialect.supports_unicode_statements = True; conn = e.connect()"

in

SQLAlchemy-0.6beta1dev_r6707-py2.6.egg/sqlalchemy/dialects/oracle/base.py

Since you can't even connect to the database, this is a show-stopper when cx_Oracle has been compiled WITH_UNICODE.

Comments (25)

  1. Mike Bayer repo owner
    • changed milestone to 0.6.0

    the solution should be that we turn on supports_unicode_statements, either as an option or across the board if we see that cx_oracle 5.0 handles it regardless. then apply u'' to the remaining SQL strings that aren't under text().

  2. Mike Bayer repo owner

    also we likely have to do some kind of jig to work around unicode/non-unicode passed to connect.

  4. Former user Account Deleted

    Temporary workaround for me (pwaller), but not ideal.

    from sqlalchemy.dialects import oracle

def connect_fixup(self, *args, **kwargs):
    """
    Monkeypatch to work around issue 1670 in sqlalchemy
    http://www.sqlalchemy.org/trac/ticket/1670

    Should be fixed by 0.6.0
    """
    for key in kwargs: kwargs[key](key) = str(kwargs[key](key))
    return self.dbapi.connect(*args, **kwargs)

oracle.dialect.connect = connect_fixup
  6. Catherine Devlin

    The patch I just added should allow normal functioning if you specify with_unicode=True when creating connections. The patch doesn't break any tests, to the best of my novice testing ability.

  7. Mike Bayer repo owner

    cx_oracle has no "UNICODE" type if compiled with the UNICODE flag. so thats how to detect that.

  8. Mike Bayer repo owner

    so I can commit partial work on this but AFAICT the WITH_UNICODE mode doesn't work in Python 2. I can emit a few statements but I quickly get this during cursor.fetchone():

    (DatabaseError) OCI-22061: invalid format text [T None None

    that's cx_Oracle not talking to the database correctly. So I'll commit my partial progress but we'll be unable to complete this ticket.

  11. Mike Bayer repo owner
    • changed status to open
    • removed status

    I can run on my osx->linux VM, which is all 32 bit. A few more fixes coming up that allow all reasonable unit tests to run. I'm not going to try to get full test coverage for tests that don't really matter in this mode.

  12. Former user Account Deleted

    (pwaller) I still experience this in 11a8939f365114ca55a75ebb6f03008fdf04ee6b, without my monkeypatch. I'm on cx_Oracle 5.0.1, 32 bit

    sqlalchemy/11a8939f365114ca55a75ebb6f03008fdf04ee6b/lib/python2.5/site-packages/sqlalchemy/engine/default.pyc in connect(self, cargs, cparams) 197 198 def connect(self, cargs, cparams): --> 199 return self.dbapi.connect(*cargs, cparams) 200 201 def create_connect_args(self, url):

    DBAPIError: (TypeError) argument 1 must be str, not unicode None None

  15. Former user Account Deleted

    I don't have much of a choice whether or not cx_Oracle is compiled WITH_UNICODE, as it's installed that way by my company's IT Services group on all production servers.

  16. Mike Bayer repo owner

    IMHO they are installing cx_oracle using a special mode that is only intended for testing purposes, its not appropriate for production usage. If they think that cx_oracle doesn't work with unicode if the flag isn't set, they're incorrect. I am hypothesizing that they misunderstand this flag.

    nevertheless if you can please send me your failing connect string i can complete this ticket.

  17. Mike Bayer repo owner

    OK well all my Oracle tests pass with and without WITH_UNICODE, the issue of changing cx_oracle's behavior seems pretty much dead on the cx_oracle list, the above issue requiring "str()" makes no sense as that error would not be raised under WITH_UNICODE and no further info has been given, so we're done.

  18. Former user Account Deleted
    • removed status
    • changed status to open

    (pwaller)

    Sorry for not responding sooner. I didn't hit this for a while as I didn't realise there seem to be different environments where I'm running both cxo4 and cxo5. I didn't hit this issue in cxo4, for some reason, but now it is back again.

    sqlalchemy/engine/default.py(227)connect() 226 def connect(self, cargs, cparams): --> 227 return self.dbapi.connect(cargs, **cparams) 228

    ipdb> cparams {'dsn': u'XXXX', 'password': u'XXXX', 'user': u'XXXX', 'twophase': True, 'threaded': True} ipdb>

    My connection string:

    u'oracle://XXX:XXX@XXX/'

    P.S. I didn't respond to this ticket because I don't see any way to register and be notified if something happens?

  19. Former user Account Deleted

    (tml)

    It sounds like your cx_Oracle was built as WITH_UNICODE turned off, is that correct?

    I may be wrong here, but my experience is that if cx_Oracle is compiled as WITH_UNICODE turned on, everything you pass it must be a unicode object or it will choke - whereas, when it's compiled as WITH_UNICODE turned off, everything you pass it must be a string object, or it will choke equally as hard.

    If you have cx_Oracle.UNICODE or cx_Oracle.FIXED_UNICODE, your cx_Oracle was built as WITH_UNICODE turned off and requires that you only pass strings.

  20. Mike Bayer repo owner

    Replying to guest:

    (pwaller)

    Sorry for not responding sooner. I didn't hit this for a while as I didn't realise there seem to be different environments where I'm running both cxo4 and cxo5. I didn't hit this issue in cxo4, for some reason, but now it is back again.

    confused - cx_oracle 4.0 doesn't support the WITH_UNICODE setting this ticket refers to.

    ipdb> cparams {'dsn': u'XXXX', 'password': u'XXXX', 'user': u'XXXX', 'twophase': True, 'threaded': True} ipdb>

    My connection string:

    u'oracle://XXX:XXX@XXX/'

    are you getting an error ? This ticket never referred to the need for unicode elements of a URL to be coerced back to strings. You'd of course use a non-unicode string for your URL (and you can do that for WITH_UNICODE mode as well since we coerce into unicode). So far this sounds like a different, strongly leaning "worksforme", issue.

    P.S. I didn't respond to this ticket because I don't see any way to register and be notified if something happens?

    you'd put your email address in the "cc" list. if its not showing up then we can add it for you.

  22. Former user Account Deleted

    (pwaller) Please add me to the CC, I can't see how to do it myself. peter.waller@gmail.com

    @tml indeed, it does have UNICODE and FIXED_UNICODE.

    Replying to zzzeek:

    confused - cx_oracle 4.0 doesn't support the WITH_UNICODE setting this ticket refers to.

    Which would explain why I didn't hit the problem for a while then :-) (I have just recently found myself using cxo5 in some cases)

    are you getting an error ? This ticket never referred to the need for unicode elements of a URL to be coerced back to strings. You'd of course use a non-unicode string for your URL (and you can do that for WITH_UNICODE mode as well since we coerce into unicode). So far this sounds like a different, strongly leaning "worksforme", issue.

    Ah. I didn't realise I was passing a unicode string in, I thought it was a plain string. (It was when I hard-coded the connection string, but then I changed my code to read parts of the connection string in from some XML files, which came in as unicode objects).

    Summary: cxo4 connection string: string: works works cxo5 connection string: string: works does not work

    Fair enough I can coerce the connection string myself, but I did not realise the problem was in my code, and I also didn't understand why it was intermittent (worked in some circumstances (cxo4) but not others). It would be nice is SQLA would make it 'just work' in either case.

  23. Mike Bayer repo owner

    e1cd2563ed8601fffab1d1404471969d198d235e contains what I consider to be a workaround for what is essentially a cx_oracle bug. We now cannot pass any connect tokens to cx_oracle that have non-ascii characters in them, since we do not know what encoding to use. The unicode tokens are coerced directly to string assuming ASCII or an error is raised.

  26. Log in to comment
Assignee
Type
bug
Priority
major
Status
resolved
Component
oracle
Milestone
–
Votes
0
Watchers
0
Jira: the preferred issue tracker for Bitbucket. Join the team!