On Aug 10, 2010, at 9:28 AM, Sven A. Schmidt wrote:
> Getting this out of the way first, because I always forget ;) :
> SQLAlchemy-0.5.5, Python 2.6
I'll take a look at this later but you should probably be tracking down the
issue in 0.6.3, assuming its still present - that's where we'd fix any issues.
>
> I'm getting the above error when trying to connect to an Oracle schema
> 'TEST' and read from another schema 'SAS' where tables are exposed via
> synonyms (permissions have been granted) and I'm hoping that someone
> on this list may be able to cast some light on what's going on. I've
> googled for the error but only found the source where this exception
> is being raised.
>
> I've tried connecting with echo=True and running the SQL by hand from
> the TEST account and the strange thing is I get no error but a list of
> fields as I would expect (see below). First, here's the echo output of
> my script:
>
> 2010-08-10 14:56:00,221 INFO sqlalchemy.engine.base.Engine.0x...4b90
> select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION,
> DATA_SCALE, NULLABLE, DATA_DEFAULT from ALL_TAB_COLUMNS where
> TABLE_NAME = :table_name and OWNER = :owner
> INFO select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION,
> DATA_SCALE, NULLABLE, DATA_DEFAULT from ALL_TAB_COLUMNS where
> TABLE_NAME = :table_name and OWNER = :owner
> 2010-08-10 14:56:00,221 INFO sqlalchemy.engine.base.Engine.0x...4b90
> {'owner': 'SAS', 'table_name': 'FORECASTS'}
> INFO {'owner': 'SAS', 'table_name': 'FORECASTS'}
> 2010-08-10 14:56:00,230 INFO sqlalchemy.engine.base.Engine.0x...4b90
> SELECT
> ac.constraint_name,
> ac.constraint_type,
> loc.column_name AS local_column,
> rem.table_name AS remote_table,
> rem.column_name AS remote_column,
> rem.owner AS remote_owner
> FROM all_constraints ac,
> all_cons_columns loc,
> all_cons_columns rem
> WHERE ac.table_name = :table_name
> AND ac.constraint_type IN ('R','P')
> AND ac.owner = :owner
> AND ac.owner = loc.owner
> AND ac.constraint_name = loc.constraint_name
> AND ac.r_owner = rem.owner(+)
> AND ac.r_constraint_name = rem.constraint_name(+)
> -- order multiple primary keys correctly
> ORDER BY ac.constraint_name, loc.position, rem.position
> INFO SELECT
> ac.constraint_name,
> ac.constraint_type,
> loc.column_name AS local_column,
> rem.table_name AS remote_table,
> rem.column_name AS remote_column,
> rem.owner AS remote_owner
> FROM all_constraints ac,
> all_cons_columns loc,
> all_cons_columns rem
> WHERE ac.table_name = :table_name
> AND ac.constraint_type IN ('R','P')
> AND ac.owner = :owner
> AND ac.owner = loc.owner
> AND ac.constraint_name = loc.constraint_name
> AND ac.r_owner = rem.owner(+)
> AND ac.r_constraint_name = rem.constraint_name(+)
> -- order multiple primary keys correctly
> ORDER BY ac.constraint_name, loc.position, rem.position
> 2010-08-10 14:56:00,230 INFO sqlalchemy.engine.base.Engine.0x...4b90
> {'owner': 'SAS', 'table_name': 'FORECASTS'}
> INFO {'owner': 'SAS', 'table_name': 'FORECASTS'}
> /Library/Python/2.6/site-packages/SQLAlchemy-0.5.5-py2.6.egg/
> sqlalchemy/engine/base.py:1265: SAWarning: Got 'None' querying
> 'table_name' from all_cons_columns - does the user have proper rights
> to the table?
> self.dialect.reflecttable(conn, table, include_columns)
> 2010-08-10 14:56:00,251 INFO sqlalchemy.engine.base.Engine.0x...4b90
> select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION,
> DATA_SCALE, NULLABLE, DATA_DEFAULT from ALL_TAB_COLUMNS where
> TABLE_NAME = :table_name and OWNER = :owner
> INFO select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION,
> DATA_SCALE, NULLABLE, DATA_DEFAULT from ALL_TAB_COLUMNS where
> TABLE_NAME = :table_name and OWNER = :owner
> 2010-08-10 14:56:00,251 INFO sqlalchemy.engine.base.Engine.0x...4b90
> {'owner': 'SAS', 'table_name': 'GROUPS'}
>
> I guess the error message refers to the query with the parmeters
> {'owner': 'SAS', 'table_name': 'FORECASTS'} (but I've also tried the
> one after, in case the error precedes the query -- it looks the same).
> So I ran the query:
>
> SELECT
> ac.constraint_name,
> ac.constraint_type,
> loc.column_name AS local_column,
> rem.table_name AS remote_table,
> rem.column_name AS remote_column,
> rem.owner AS remote_owner
> FROM all_constraints ac,
> all_cons_columns loc,
> all_cons_columns rem
> WHERE ac.table_name = 'FORECASTS'
> AND ac.constraint_type IN ('R','P')
> AND ac.owner = 'SAS'
> AND ac.owner = loc.owner
> AND ac.constraint_name = loc.constraint_name
> AND ac.r_owner = rem.owner(+)
> AND ac.r_constraint_name = rem.constraint_name(+)
> -- order multiple primary keys correctly
> ORDER BY ac.constraint_name, loc.position, rem.position;
>
> and got
>
> "CONSTRAINT_NAME" "CONSTRAINT_TYPE" "LOCAL_COLUMN" "REMOTE_TABLE"
> "REMOTE_COLUMN" "REMOTE_OWNER"
> "FK5E6775D8575C2425" "R" "FORECAST_STATUS_ID" "" "" ""
> "FK5E6775D871226E5" "R" "SUB_GROUP_ID" "GROUPS" "ID" "SAS"
> "FK5E6775D87C3474A6" "R" "PROGRAMME_ID" "PROGRAMME_CFG" "ID" "SAS"
> "FK5E6775D88E98545F" "R" "APPROVAL_CYCLE_ID" "APPROVAL_CYCLES"
> "ID" "SAS"
> "FK5E6775D896C4452F" "R" "BUSINESS_IMPACT_ID" "" "" ""
> "FK5E6775D89A1530AE" "R" "DIVISION_ID" "DIVISIONS_CFG" "ID" "SAS"
> "FK5E6775D89F6A3DA5" "R" "BUSINESS_JUSTIFICATION_ID" "" ""
> ""
> "FK5E6775D8B85D0B4E" "R" "INITIATIVE_ID" "INITIATIVE_CFG" "ID"
> "SAS"
> "FK5E6775D8FC3CABC6" "R" "GROUP_ID" "GROUPS" "ID" "SAS"
> "SYS_C0057558" "P" "ID" "" "" ""
>
> (I hope this is readable, here's the same in Google docs:
> https://spreadsheets.google.com/ccc?key=0AgdbG5HyoweVdGZiN2RnM1JMY3hDSGpuZ09CVVdsbkE&hl=en&authkey=CKqIrcsL)
>
> I'm connecting to the database with the schema='SAS' parameter on all
> Table objects. I've done the same thing in the past (connecting to
> another schema I read from) in SQLAlchemy (same version, 0.5.5) but
> unfortunately the different scripts are hard to compare as such. They
> do look very much alike from how they connect and obtain schema
> information (but you're often blind to differences in your own code).
> I'll probably have to strip them down further in the end to track this
> down but this error looks like something more fundamental may be
> wrong.
>
> Does anyone on this list have an idea what I could try to investigate
> further? I hope I've included all the relevant infos in this (quite
> long, sorry!) mail. Let me know if I can provide anything else!
>
> Cheers,
> Sven
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
