Oracle 8 server detection assumes engine.connect() is called
As long as engine.connect() is called, the changes recently added to 0.6.1 for Oracle 8 server configuration work.
However, if connect() isn't called, it fails.
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('oracle://scott:tiger@localhost/test',echo=True)
#engine.connect()
metadata = MetaData()
Session = sessionmaker(bind=engine)
DBSession = Session()
table = Table("tabl", metadata,
Column("col", Unicode(255), primary_key=True)
)
class Record(object):
pass
mapper(Record, table)
r=Record()
r.col = u'Unicode String'
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 "convert_unicode:", engine.dialect.convert_unicode
print "*****************************"
metadata.create_all(engine)
DBSession.add(r)
DBSession.flush()
Output:
*****************************
server_version_info: None
_supports_char_length: True
supports_unicode_binds: True
use_ansi: True
convert_unicode: False
*****************************
2010-06-01 11:48:44,145 INFO sqlalchemy.engine.base.Engine.0x...dbd0 SELECT USER FROM DUAL
2010-06-01 11:48:44,148 INFO sqlalchemy.engine.base.Engine.0x...dbd0 {}
Traceback (most recent call last):
File "ora8.py", line 33, in <module>
metadata.create_all(engine)
File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/schema.py", line 1975, in create_all
bind.create(self, checkfirst=checkfirst, tables=tables)
File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1647, in create
connection=connection, **kwargs)
File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1677, in _run_visitor
conn = self.contextual_connect(close_with_result=False)
File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1742, in contextual_connect
self.pool.connect(),
File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/pool.py", line 157, in connect
return _ConnectionFairy(self).checkout()
File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/pool.py", line 321, in __init__
rec = self._connection_record = pool.get()
File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/pool.py", line 176, in get
return self.do_get()
File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/pool.py", line 670, in do_get
con = self.create_connection()
File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/pool.py", line 137, in create_connection
return _ConnectionRecord(self)
File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/pool.py", line 217, in __init__
l.first_connect(self.connection, self)
File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/engine/strategies.py", line 145, in first_connect
dialect.initialize(c)
File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/dialects/oracle/cx_oracle.py", line 452, in initialize
super(OracleDialect_cx_oracle, self).initialize(connection)
File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/dialects/oracle/base.py", line 623, in initialize
super(OracleDialect, self).initialize(connection)
File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/engine/default.py", line 138, in initialize
self.returns_unicode_strings = self._check_unicode_returns(connection)
File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/engine/default.py", line 183, in _check_unicode_returns
unicode_for_varchar = check_unicode(sqltypes.VARCHAR(60))
File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/engine/default.py", line 173, in check_unicode
]).compile(dialect=self)
cx_Oracle.DatabaseError: ORA-00907: missing right parenthesis
Comments (10)
-
repo owner -
Account Deleted Replying to zzzeek:
what happens if you change that "connect()" to
contextual_connect()
? that's the only difference.If I uncomment {{{#engine.connect()}}} and make it {{{engine.contextual_connect()}}}, it works just fine:
2010-06-01 12:10:25,041 INFO sqlalchemy.engine.base.Engine.0x...cbd0 SELECT USER FROM DUAL 2010-06-01 12:10:25,044 INFO sqlalchemy.engine.base.Engine.0x...cbd0 {} ***************************** server_version_info: (8, 1, 7, 4, 0) _supports_char_length: False supports_unicode_binds: False use_ansi: False convert_unicode: False ***************************** 2010-06-01 12:10:25,817 INFO sqlalchemy.engine.base.Engine.0x...cbd0 SELECT table_name FROM all_tables WHERE table_name = :name AND owner = :schema_name 2010-06-01 12:10:25,817 INFO sqlalchemy.engine.base.Engine.0x...cbd0 {'name': 'TABL', 'schema_name': 'ARC'} 2010-06-01 12:10:26,132 INFO sqlalchemy.engine.base.Engine.0x...cbd0 BEGIN 2010-06-01 12:10:26,132 INFO sqlalchemy.engine.base.Engine.0x...cbd0 INSERT INTO tabl (col) VALUES (:col) 2010-06-01 12:10:26,133 INFO sqlalchemy.engine.base.Engine.0x...cbd0 {'col': 'Unicode String'}
-
repo owner oh, duh, what happens if you don't do your print statements ahead of time ? you are causing those values to be cached with the wrong information. though its possible the table object is being compiled, causing the same issue. in that case your patch is thus:
diff -r 1bd54fd9c854044d8153dba0f6dd261bf6bb6bff lib/sqlalchemy/dialects/oracle/base.py --- a/lib/sqlalchemy/dialects/oracle/base.py Mon May 31 14:47:07 2010 -0400 +++ b/lib/sqlalchemy/dialects/oracle/base.py Tue Jun 01 15:37:26 2010 -0400 @@ -634,11 +634,11 @@ return self.server_version_info and \ self.server_version_info < (9, ) - @util.memoized_property + @property def _supports_char_length(self): return not self._is_oracle_8 - @util.memoized_property + @property def _supports_nchar(self): return not self._is_oracle_8
-
Account Deleted Oh, right. In my code I'm detecting Ora8 with this:
if self.eng.dialect.use_ansi: # oracle 9i or better else: # oracle 8
How can I be sure
use_ansi
(orserver_version_info
or_is_oracle_8
- would be nice to make that public) has already been set properly before referencing it, even within my_compiler_dispatch
function?I also have several functions like this:
class current_date(ColumnElement): type = Date() @compiles(current_date) def _compiler_dispatch(element, compiler, **kw): if compiler.dialect.name == 'oracle': if compiler.dialect.use_ansi: return "trunc(current_date)" else: return "trunc(sysdate)" else: # assume postgresql return "current_date"
Do I need to call
contextual_connect()
before referencinguse_ansi
, even within my_compiler_dispatch()
function? -
Account Deleted BTW, I should have mentioned, if I don't issue the print statements then it works fine...
-
repo owner - changed milestone to 0.6.2
OK, the change here should be made regardless, the "memoized" approach doesn't really save us much. Regarding use_ansi, it is something you can set externally, but if you want server detection to do it then yes you'd have to ensure an initial connection has been made before you rely upon its value. With normal usage of
execute()
given aClauseElement
, the connection is acquired before the statement is compiled, so normally there's no issue here. -
Account Deleted I assume the same goes for
server_version_info
or_is_oracle_8
asuse_ansi
, i.e. I need to make sure the connection is already established before relying on any of these, correct?Are you opposed for any reason to making
_is_oracle_8 -> is_oracle_8
(public)?Finally, can I just call
engine.contextual_connect()
orengine.connect()
before inspecting these values? What's the difference between the two and are both smart enough to not re-establish another connection if one already exists? -
repo owner server_version_info comes from the first connection, yes. I'm not eager to make is_oracle_8 public, I don't know that it will always work the same way and be available at the same point in future releases (then again that applies to server_version_info too..). contextual_connect() and connect() are the same in the case of a default Engine without a threadlocal Pool in use (also the default). don't know what "if one already exists" means. the engine uses a connection pool which handles "reuse" of checked-in connections.
-
repo owner - changed status to resolved
-
repo owner - removed milestone
Removing milestone: 0.6.2 (automated comment)
- Log in to comment
what happens if you change that "connect()" to
contextual_connect()
? that's the only difference.