SQLError: ... invalid identifier

[shday]

I am getting a new error when using r2607. When I switch back to 3.6
there is no error.

Here is the stack trace:

Page handler: <bound method StudyRequestController.default of
<srt.controllers.StudyRequestController instance at 0x015D6620>>
Traceback (most recent call last):
File "C:\Python24\lib\site-packages\cherrypy-2.2.1-py2.4.egg\cherrypy
\_cphttptools.py", line 105, in _run
self.main()
File "C:\Python24\lib\site-packages\cherrypy-2.2.1-py2.4.egg\cherrypy
\_cphttptools.py", line 254, in main
body = page_handler(*virtual_path, **self.params)
File "<string>", line 3, in default
File "C:\Documents and Settings\Stephen\Desktop\tg_checkout
\turbogears\controllers.py", line 334, in expose
File "<string>", line 5, in run_with_transaction
File "c:\documents and settings\daystev\desktop\tg_checkout
\turbogears\database.py", line 354, in sa_rwt
retval = dispatch_exception(e,args,kw)
File "c:\documents and settings\daystev\desktop\tg_checkout
\turbogears\database.py", line 343, in sa_rwt
retval = func(*args, **kw)
File "<string>", line 5, in _expose
File "C:\Documents and Settings\Stephen\Desktop\tg_checkout
\turbogears\controllers.py", line 351, in <lambda>
File "C:\Documents and Settings\Stephen\Desktop\tg_checkout
\turbogears\controllers.py", line 378, in _execute_func
File "C:\Documents and Settings\Stephen\Desktop\tg_checkout
\turbogears\errorhandling.py", line 73, in try_call
File "C:\Documents and Settings\Daystev\Desktop\srt_trunk\srt-project
\srt\controllers.py", line 232, in default
return action(item, **params)
File "<string>", line 3, in edit
File "C:\Documents and Settings\Stephen\Desktop\tg_checkout
\turbogears\controllers.py", line 330, in expose
File "<string>", line 5, in _expose
File "C:\Documents and Settings\Stephen\Desktop\tg_checkout
\turbogears\controllers.py", line 351, in <lambda>
File "C:\Documents and Settings\Stephen\Desktop\tg_checkout
\turbogears\controllers.py", line 378, in _execute_func
File "C:\Documents and Settings\Stephen\Desktop\tg_checkout
\turbogears\errorhandling.py", line 73, in try_call
File "C:\Documents and Settings\Daystev\Desktop\srt_trunk\srt-project
\srt\controllers.py", line 251, in edit
request = dbmodel.request(request_id)
File "C:\Documents and Settings\Daystev\Desktop\srt_trunk\srt-project
\srt\model.py", line 328, in request
model_table.c.model_id == study_request_table.c.model_id))
File "c:\documents and settings\daystev\desktop\sqlalchemy_co\lib
\sqlalchemy\orm\query.py", line 270, in selectfirst
ret = self.select_whereclause(whereclause=arg, **kwargs)
File "c:\documents and settings\daystev\desktop\sqlalchemy_co\lib
\sqlalchemy\orm\query.py", line 326, in select_whereclause
return self._select_statement(statement, params=params)
File "c:\documents and settings\daystev\desktop\sqlalchemy_co\lib
\sqlalchemy\orm\query.py", line 939, in _select_statement
return self.execute(statement, params=params, **kwargs)
File "c:\documents and settings\daystev\desktop\sqlalchemy_co\lib
\sqlalchemy\orm\query.py", line 843, in execute
result = self.session.execute(self.mapper, clauseelement,
params=params)
File "c:\documents and settings\daystev\desktop\sqlalchemy_co\lib
\sqlalchemy\orm\session.py", line 183, in execute
return self.connection(mapper,
close_with_result=True).execute(clause, params, **kwargs)
File "c:\documents and settings\daystev\desktop\sqlalchemy_co\lib
\sqlalchemy\engine\base.py", line 496, in execute
return Connection.executors[c](self, object, *multiparams,
**params)
File "c:\documents and settings\daystev\desktop\sqlalchemy_co\lib
\sqlalchemy\engine\base.py", line 536, in execute_clauseelement
return self.execute_compiled(elem.compile(dialect=self.dialect,
parameters=param), *multiparams, **params)
File "c:\documents and settings\daystev\desktop\sqlalchemy_co\lib
\sqlalchemy\engine\base.py", line 547, in execute_compiled
self._execute_raw(context)
File "c:\documents and settings\daystev\desktop\sqlalchemy_co\lib
\sqlalchemy\engine\base.py", line 560, in _execute_raw
self._execute(context)
File "c:\documents and settings\daystev\desktop\sqlalchemy_co\lib
\sqlalchemy\engine\base.py", line 578, in _execute
raise exceptions.SQLError(context.statement, context.parameters,
e)
SQLError: (DatabaseError) ORA-00904:
"TBL_ROW_COUNT"."STUDY_REQUEST_STUDY_REQUEST_ID": invalid identifier
'SELECT study_request.model_id AS study_request_model_id,
study_request.action_plan_wiki_id AS study_request_action_pla_2,
anon_dd00.task AS anon_dd00_task, anon_dd00.study_request_id AS
anon_dd00_study_request_id, anon_dd00.fte_days AS anon_dd00_fte_days,
study_request.animals_requested AS study_request_animals_re_3,
study_request.note AS study_request_note, study_request.media_type AS
study_request_media_type, study_request.study_date AS
study_request_study_date, study_request.study_end_date AS
study_request_study_end_date, study_request.acc_protocol_id AS
study_request_acc_protocol_id, study_request.justification AS
study_request_justification, study_request.year_submitted AS
study_request_year_submitted, study_request.fte_weeks AS
study_request_fte_weeks, study_request.date_submitted AS
study_request_date_submitted, study_request.action_plan_md5 AS
study_request_action_plan_md5, study_request.fte_weekends AS
study_request_fte_weekends, study_request.requester_isid AS
study_request_requester_isid, anon_3ada.study_request_id AS
anon_3ada_study_request_id, anon_3ada.technician_isid AS
anon_3ada_technician_isid, anon_3ada.task AS anon_3ada_task,
study_request.mcode AS study_request_mcode, study_request.action_plan
AS study_request_action_plan, study_request.cancelled AS
study_request_cancelled, anon_59b7.model_acronym AS
anon_59b7_model_acronym, anon_59b7.therapeutic_area AS
anon_59b7_therapeutic_area, anon_59b7.model_id AS anon_59b7_model_id,
anon_59b7.investigator_isid AS anon_59b7_investigator_isid,
anon_59b7.active AS anon_59b7_active, anon_59b7.model_name AS
anon_59b7_model_name, study_request.study_request_id AS
study_request_study_requ_1, study_request.animals_used AS
study_request_animals_used, study_request.model_request_counter AS
study_request_model_requ_4 \nFROM (SELECT study_request_study_requ_1
\nFROM (SELECT study_request.study_request_id AS
study_request_study_requ_1, study_request.rowid AS study_request_oid,
ROW_NUMBER() OVER (ORDER BY study_request.rowid) AS ora_rn \nFROM
study_request, model \nWHERE model.model_acronym
= :model_model_acronym AND study_request.model_request_counter
= :study_request_model_requ_1 AND model.model_id =
study_request.model_id) \nWHERE ora_rn<=1) tbl_row_count,
study_request LEFT OUTER JOIN comp_med_resource anon_dd00 ON
study_request.study_request_id = anon_dd00.study_request_id LEFT OUTER
JOIN technician_study_request anon_3ada ON
study_request.study_request_id = anon_3ada.study_request_id LEFT OUTER
JOIN model anon_59b7 ON anon_59b7.model_id = study_request.model_id
\nWHERE study_request.study_request_id =
tbl_row_count.study_request_study_requ_1 ORDER BY
tbl_row_count.study_request_study_request_id,
anon_dd00.study_request_id, anon_3ada.task,
anon_59b7.model_id' {'model_model_acronym': 'RMS2007',
'study_request_model_requ_1': '30'}

[Michael Bayer]

well its generally that the identifier name is too long. i dont
exactly understand how its coming out without its proper truncation
being set, and it might be related to how the oracle module redefines
"ORDER BY"...so for now youd have to stick with 0.3.6 or shorten the
column name on your table. if you want to send me the "Table" for
that i can try to create a test out of it.

[shday]

The table is reflected here:

study_request_table = Table('study_request',metadata,

Column('study_request_id',Numeric(precision=6,length=0),

Sequence('study_request_seq'),primary_key=True,nullable=False),
autoload=True)

Here in what metadata.table['study_request'] gives:

Table('study_request',DynamicMetaData(),Column('acc_protocol_id',OracleString(le
ngth=8),ForeignKey('model_acc_protocol.acc_protocol_id'),nullable=False),Column(
'mcode',OracleString(length=8)),Column('date_submitted',OracleDateTime(timezone=
False)),Column('action_plan_wiki_id',OracleNumeric(precision=10,length=0)),Colum
n('fte_weeks',OracleNumeric(precision=12,length=4)),Column('fte_weekends',Oracle
Numeric(precision=12,length=4)),Column('year_submitted',OracleNumeric(precision=
4,length=0)),Column('study_request_id',Numeric(precision=6,length=0),primary_key
=True,nullable=False,default=Sequence('study_request_seq',start=None,increment=N
one,optional=False)),Column('model_id',OracleNumeric(precision=6,length=0),Forei
gnKey('model_acc_protocol.model_id'),nullable=False),Column('requester_isid',Ora
cleString(length=8),nullable=False),Column('justification',OracleString(length=4
000)),Column('study_date',OracleDateTime(timezone=False),nullable=False),Column(
'animals_requested',OracleNumeric(precision=4,length=0)),Column('animals_used',O
racleNumeric(precision=4,length=0)),Column('cancelled',OracleNumeric(precision=1
,length=0),nullable=False,default=PassiveDefault(<sqlalchemy.sql._TextClause
obj
ect at
0x016FB630>)),Column('model_request_counter',OracleNumeric(precision=6,le
ngth=0),nullable=False),Column('study_end_date',OracleDateTime(timezone=False),n
ullable=False),Column('note',OracleString(length=4000)),Column('action_plan',Ora
cleBinary(length=None)),Column('media_type',OracleString(length=64)),Column('act
ion_plan_md5',OracleString(length=32)),schema=None)

> > 'study_request_model_requ_1': '30'}- Hide quoted text -
>
> - Show quoted text -

[Michael Bayer]

yeah also whats your mapper on that ? is there a string-based
order_by in there somewhere (or in the query.selectfirst() call) ? i
dont understand why that name wouldnt be truncated.

[Michael Bayer]

also is there a count() stuck in there somewhere ? the
"tbl_row_count" identifier only comes into play when theres count().

On May 7, 2007, at 3:09 PM, shday wrote:

>
> The table is reflected here:
>
> study_request_table = Table('study_request',metadata,
>
> Column('study_request_id',Numeric(precision=6,length=0),
>
> Sequence('study_request_seq'),primary_key=True,nullable=False),
> autoload=True)
>
> Here in what metadata.table['study_request'] gives:
>

> (<sqlalchemy.sql._TextClause
> obj
> ect at

> 0x016FB630>)),Column('model_request_counter',OracleNumeric
> (precision=6,le
> ngth=0),nullable=False),Column('study_end_date',OracleDateTime
> (timezone=False),n
> ullable=False),Column('note',OracleString(length=4000)),Column
> ('action_plan',Ora
> cleBinary(length=None)),Column('media_type',OracleString

[shday]

The mapper:

mapper(StudyRequest,study_request_table,properties={'model':relation(Model,

primaryjoin=model_table.c.model_id==study_request_table.c.model_id,

foreign_keys=[study_request_table.c.model_id],
lazy=False), #backref doesn't work
in 3.6

'technicians':relation(TechnicianStudyRequest,backref='study_request',lazy=False),

'action_plans':relation(ActionPlan,backref='study_request',lazy=True),

'comp_med_resources':relation(CompMedResource,backref='study_request',lazy=False)})

The selectfirst:

request = session.query(StudyRequest).selectfirst(
and_(model_table.c.model_acronym == acronym,
study_request_table.c.model_request_counter == counter,
model_table.c.model_id == study_request_table.c.model_id))

Could the problem have something to do with the column being Numeric
instead of OracleNumeric? Seems to be that way because reflection was
overridden.

> ...
>
> read more »- Hide quoted text -

[shday]

No count() anywhere.

> ...
>
> read more »- Hide quoted text -

[Michael Bayer]

k i got it, rev 2609.

On May 7, 2007, at 3:44 PM, shday wrote:

>
> The mapper:
>
> mapper(StudyRequest,study_request_table,properties={'model':relation

[shday]

That did the trick.