oracle dsn fix in trunk

[zzzeek]

Originally reported by: Anonymous

---

with this diff applied, the dsn handling for oracle is fixed

one can now use such a dsn for remote hosts: oracle://user:pass@host/sid

and the one that should have been implemented and didn't work now works

oracle://user:pass@tnsname

i'll attach the file

---

• Bitbucket: https://bitbucket.org/zzzeek/sqlalchemy/issue/192

[zzzeek]

Original comment by Michael Bayer (Bitbucket: zzzeek, GitHub: zzzeek):

---

Removing milestone: 0.4.0 (automated comment)

[zzzeek]

Original comment by Michael Bayer (Bitbucket: zzzeek, GitHub: zzzeek):

---

Milestone next micropoint release deleted

[zzzeek]

Original comment by Michael Bayer (Bitbucket: zzzeek, GitHub: zzzeek):

---

thanks, tested this in various combinations against my XE db and it works, changeset:1575

[zzzeek]

Original comment by Anonymous:

---

this is the dsn cx_Oracle expects, only this way you can work with a host name without using a local tnsname

btw: i have tested this with my oracle 9.2 server an all cases work

from http://www.python.net/crew/atuining/cx_Oracle/html/module.html

makedsn( host, port, sid) Return a string suitable for use as the dsn for the connect() method. This string is identical to the strings that are defined by the Oracle names server or defined in the tnsnames.ora file.

[zzzeek]

Original comment by Michael Bayer (Bitbucket: zzzeek, GitHub: zzzeek):

---

the "makedsn" function you put in your patch does this:

cx_Oracle.makedsn('127.0.0.1', 1537, 'somename') '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1537)))(CONNECT_DATA=(SID=somename)))'

sorry, had to revert this; please test patches beforehand !

[zzzeek]

Original comment by Michael Bayer (Bitbucket: zzzeek, GitHub: zzzeek):

---

thanks for the test case. the Query.load() and get() methods were failing to properly apply type conversion to the argument. this is fixed in changeset:1564.

ive committed your other patch (though havent fired up the oracle box to test it) in changeset:1565, you should insure that it works.

[zzzeek]

Original comment by Anonymous:

---

the problem appears when you do a query.load from a table

for example if you have this column in a table:

sqlalchemy.Column('userName', sqlalchemy.Unicode,
                  sqlalchemy.ForeignKey('vw_lust_employees.userName'),
                  primary_key=True),

and you do a query.load with a unicode parameter like this

    return q.load([self.context.userName,int(year)](self.context.userName,int(year)))

here a traceback from my application

2006-06-01T10:58:24 ERROR SiteError http://localhost:8080/++skin++Lust/lust/employees/bd/plan2class.png
Traceback (most recent call last):
  File "/Users/bd/zsandbox/trunk/src/zope/publisher/publish.py", line 133, in publish
    result = publication.callObject(request, obj)
  File "/Users/bd/zsandbox/trunk/src/zope/app/publication/zopepublication.py", line 161, in callObject
    return mapply(ob, request.getPositionalArguments(), request)
  File "/Users/bd/zsandbox/trunk/src/zope/publisher/publish.py", line 108, in mapply
    return debug_call(obj, args)
  File "/Users/bd/zsandbox/trunk/src/zope/publisher/publish.py", line 114, in debug_call
    return obj(*args)
  File "/Users/bd/zopes/lust/lib/python/fhv/lust/browser/employee.py", line 18, in chartPlan2Class
    d = self.report.getStats(self.session['year']('year'))
  File "/Users/bd/zopes/lust/lib/python/fhv/lust/report.py", line 31, in getStats
    plan = self.getYearPlan(year)
  File "/Users/bd/zopes/lust/lib/python/fhv/lust/report.py", line 24, in getYearPlan
    return q.load([self.context.userName,int(year)](self.context.userName,int(year)))
  File "/Users/bd/zopes/lust/lib/python/sqlalchemy/orm/query.py", line 51, in load
    instance = self._get(key, ident, reload=True, **kwargs)
  File "/Users/bd/zopes/lust/lib/python/sqlalchemy/orm/query.py", line 285, in _get
    return self._select_statement(statement, params=params, populate_existing=reload)[0](0)
  File "/Users/bd/zopes/lust/lib/python/sqlalchemy/orm/query.py", line 293, in _select_statement
    return self.instances(statement, params=params, **kwargs)
  File "/Users/bd/zopes/lust/lib/python/sqlalchemy/orm/query.py", line 255, in instances
    result = self.session.execute(self.mapper, clauseelement, params=params)
  File "/Users/bd/zopes/lust/lib/python/sqlalchemy/orm/session.py", line 114, in execute
    return self.connection(mapper, close_with_result=True).execute(clause, params, **kwargs)
  File "/Users/bd/zopes/lust/lib/python/sqlalchemy/engine/base.py", line 240, in execute
    return Connection.executors[type(object).__mro__[-2](type(object).__mro__[-2)](self, object, *multiparams, **params)
  File "/Users/bd/zopes/lust/lib/python/sqlalchemy/engine/base.py", line 264, in execute_clauseelement
    return self.execute_compiled(elem.compile(engine=self.__engine, parameters=param), *multiparams, **params)
  File "/Users/bd/zopes/lust/lib/python/sqlalchemy/engine/base.py", line 280, in execute_compiled
    proxy(str(compiled), parameters)
  File "/Users/bd/zopes/lust/lib/python/sqlalchemy/engine/base.py", line 276, in proxy
    self._execute_raw(statement, parameters, cursor=cursor, context=context)
  File "/Users/bd/zopes/lust/lib/python/sqlalchemy/engine/base.py", line 315, in _execute_raw
    self._execute(cursor, statement, parameters, context=context)
  File "/Users/bd/zopes/lust/lib/python/sqlalchemy/engine/base.py", line 334, in _execute
    raise exceptions.SQLError(statement, parameters, e)
SQLError: (NotSupportedError) Variable_TypeByValue(): unhandled data type unicode 'SELECT vw_lust_yearplans.userName AS vw_lust_yearplans_userName, vw_lust_yearplans.plan_admin AS vw_lust_yearplans_plan_admin, vw_lust_yearplans.plan_edu AS vw_lust_yearplans_plan_edu, vw_lust_yearplans.plan_prj AS vw_lust_yearplans_plan_prj, vw_lust_yearplans.plan_ot AS vw_lust_yearplans_plan_ot, vw_lust_yearplans.plan_year AS vw_lust_yearplans_plan_year, vw_lust_yearplans.plan_oap AS vw_lust_yearplans_plan_oap, vw_lust_yearplans.worktime_org AS vw_lust_yearplans_workti_1651 \nFROM vw_lust_yearplans \nWHERE vw_lust_yearplans.userName = :pk_vw_lust_yearplans_userName AND vw_lust_yearplans.plan_year = :pk_vw_lust_yearplans_plan_year' {'pk_vw_lust_yearplans_userName': u'bd', 'pk_vw_lust_yearplans_plan_year': 2006}

[zzzeek]

Original comment by Michael Bayer (Bitbucket: zzzeek, GitHub: zzzeek):

---

im a little confused about the unicode bind parameters fix. when you use an Dialect with convert_unicode=True, all String types automatically convert unicode values to encoded strings. There is also a unittest in test/testtypes.py called UnicodeTest which passes in Oracle. Conversion of bind parameter values is performed within TypeEngine objects, not the Compiler.

can you show me a test case that currently fails for Oracle ?

[zzzeek]

Original comment by Anonymous:

---

another fix is added: cx_Oracle don't work with unicode bind parameters, so with the new diff, these parameters are converted to encoded strings