Oracle 8 server detection assumes engine.connect() is called

[Former user]

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

[Mike Bayer]

what happens if you change that "connect()" to contextual_connect() ? that's the only difference.

[Former user]

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

[Mike Bayer]

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

[Former user]

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 (or server_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 referencing use_ansi, even within my _compiler_dispatch() function?

[Former user]

BTW, I should have mentioned, if I don't issue the print statements then it works fine...

[Mike Bayer]

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 a ClauseElement, the connection is acquired before the statement is compiled, so normally there's no issue here.

[Former user]

I assume the same goes for server_version_info or _is_oracle_8 as use_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() or engine.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?

[Mike Bayer]

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.

[Mike Bayer]

6457901f968569bb4c09622cb4f9ba264eee41ec

[Mike Bayer]

Removing milestone: 0.6.2 (automated comment)