[sqlalchemy] Re: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
On Sep 25, 2007, at 7:30 PM, m h wrote: Have updated the ticket with my fix change the dbapi_type of OracleText from CLOB to NUMBER I'm not sure if this breaks others code the bug is, the type of the bind parameters should be coming out as VARCHAR, not CLOB. NUMBER is definitely wrong. perchance, is the type of the column cal_dim.c.adwkenddt a CLOB ? (i.e. String with no length?) you didnt put the table on the ticket so i actually cannot test it (its an incomplete test case). just add a length and the whole thing should work. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
On 9/26/07, Michael Bayer [EMAIL PROTECTED] wrote: On Sep 25, 2007, at 7:30 PM, m h wrote: Have updated the ticket with my fix change the dbapi_type of OracleText from CLOB to NUMBER I'm not sure if this breaks others code the bug is, the type of the bind parameters should be coming out as VARCHAR, not CLOB. NUMBER is definitely wrong. perchance, is the type of the column cal_dim.c.adwkenddt a CLOB ? (i.e. String with no length?) you didnt put the table on the ticket so i actually cannot test it (its an incomplete test case). just add a length and the whole thing should work. Nope, not a CLOB, it's a DATE field. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
On Sep 24, 2007, at 11:36 PM, m h wrote: NP, I know Oracle is a drag but that's what the client has. Perhaps if you could clarify why I'm confused I might be able to help debug. (I've stepped through expression.py all day long). I feel like I just need a little hint or push in the right direction. Eventually you get to a cx cursor and call execute with the statement on it. How does the same statement/parameter combo fail with SQL generate, yet succeed using `text` or plain cx? If I can get over that hump, I think I might be able to give you a patch. my suspicion is that we are calling set_input_sizes on the cursor in the case of oracle. but im not sure (need to play with it). if you want to try unsetting that flag in the oracle dialect...(its called auto_setinputsizes or similar). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
Have updated the ticket with my fix change the dbapi_type of OracleText from CLOB to NUMBER I'm not sure if this breaks others code http://www.sqlalchemy.org/trac/ticket/793 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
Here's another simple testcase that fails for Oracle beta5 but works
with 0.3.7. It's about the simplest query I can come up with. Have a
table with a date column in it and query against it using the
to_date function.
def test_to_date():
start_date = '10/05/04'
where = cal_dim.c.adwkenddt == func.to_date(start_date,'MM/DD/RR')
query = select([cal_dim.c.adwkenddt],
whereclause=where
)
result = query.execute()
for r in result:
print r
This fails for beta5, works for .3.7. The error is::
Traceback (most recent call last):
File s4.py, line 88, in ?
test_to_date()
File s4.py, line 59, in test_to_date
result = query.execute()
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/sql/expression.py,
line 973, in execute
return compiled.execute(*multiparams, **params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 488, in execute
return e._execute_compiled(self, multiparams, params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 1121, in _execute_compiled
return connection._execute_compiled(compiled, multiparams, params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 832, in _execute_compiled
self.__execute_raw(context)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 850, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
parameters, context=context)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 867, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.DatabaseError: (DatabaseError) ORA-00932:
inconsistent datatypes: expected NUMBER got CLOB
'SELECT AD_WEEK_CALENDAR_DIM.adwkenddt \nFROM
AD_WEEK_CALENDAR_DIM \nWHERE AD_WEEK_CALENDAR_DIM.adwkenddt =
to_date(:to_date, :to_date_1)' {'to_date_1': 'MM/DD/RR', 'to_date':
'10/05/04'}
Am still confused as to the problem here I run the same query
through text and it works::
def test_to_date_text():
s = text(SELECT AD_WEEK_CALENDAR_DIM.adwkenddt
FROM AD_WEEK_CALENDAR_DIM
WHERE AD_WEEK_CALENDAR_DIM.adwkenddt = to_date(:to_date, :to_date_1))
result = connection.execute(s,
to_date_1= 'MM/DD/RR',
to_date= '10/05/04')
for r in result:
print r
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
sorry, i havent forgotten you. just have to get the time to power
up my oracle box. thanks for putting in the ticket.
On Sep 24, 2007, at 7:22 PM, m h wrote:
Here's another simple testcase that fails for Oracle beta5 but works
with 0.3.7. It's about the simplest query I can come up with. Have a
table with a date column in it and query against it using the
to_date function.
def test_to_date():
start_date = '10/05/04'
where = cal_dim.c.adwkenddt == func.to_date(start_date,'MM/DD/RR')
query = select([cal_dim.c.adwkenddt],
whereclause=where
)
result = query.execute()
for r in result:
print r
This fails for beta5, works for .3.7. The error is::
Traceback (most recent call last):
File s4.py, line 88, in ?
test_to_date()
File s4.py, line 59, in test_to_date
result = query.execute()
File /home/matt/work/vpython/lib/python2.4/site-packages/
SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/sql/expression.py,
line 973, in execute
return compiled.execute(*multiparams, **params)
File /home/matt/work/vpython/lib/python2.4/site-packages/
SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 488, in execute
return e._execute_compiled(self, multiparams, params)
File /home/matt/work/vpython/lib/python2.4/site-packages/
SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 1121, in _execute_compiled
return connection._execute_compiled(compiled, multiparams, params)
File /home/matt/work/vpython/lib/python2.4/site-packages/
SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 832, in _execute_compiled
self.__execute_raw(context)
File /home/matt/work/vpython/lib/python2.4/site-packages/
SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 850, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
parameters, context=context)
File /home/matt/work/vpython/lib/python2.4/site-packages/
SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 867, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.DatabaseError: (DatabaseError) ORA-00932:
inconsistent datatypes: expected NUMBER got CLOB
'SELECT AD_WEEK_CALENDAR_DIM.adwkenddt \nFROM
AD_WEEK_CALENDAR_DIM \nWHERE AD_WEEK_CALENDAR_DIM.adwkenddt =
to_date(:to_date, :to_date_1)' {'to_date_1': 'MM/DD/RR', 'to_date':
'10/05/04'}
Am still confused as to the problem here I run the same query
through text and it works::
def test_to_date_text():
s = text(SELECT AD_WEEK_CALENDAR_DIM.adwkenddt
FROM AD_WEEK_CALENDAR_DIM
WHERE AD_WEEK_CALENDAR_DIM.adwkenddt = to_date
(:to_date, :to_date_1))
result = connection.execute(s,
to_date_1= 'MM/DD/RR',
to_date= '10/05/04')
for r in result:
print r
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
NP, I know Oracle is a drag but that's what the client has.
Perhaps if you could clarify why I'm confused I might be able to help
debug. (I've stepped through expression.py all day long). I feel
like I just need a little hint or push in the right direction.
Eventually you get to a cx cursor and call execute with the statement
on it. How does the same statement/parameter combo fail with SQL
generate, yet succeed using `text` or plain cx? If I can get over
that hump, I think I might be able to give you a patch.
On 9/24/07, Michael Bayer [EMAIL PROTECTED] wrote:
sorry, i havent forgotten you. just have to get the time to power
up my oracle box. thanks for putting in the ticket.
On Sep 24, 2007, at 7:22 PM, m h wrote:
Here's another simple testcase that fails for Oracle beta5 but works
with 0.3.7. It's about the simplest query I can come up with. Have a
table with a date column in it and query against it using the
to_date function.
def test_to_date():
start_date = '10/05/04'
where = cal_dim.c.adwkenddt == func.to_date(start_date,'MM/DD/RR')
query = select([cal_dim.c.adwkenddt],
whereclause=where
)
result = query.execute()
for r in result:
print r
This fails for beta5, works for .3.7. The error is::
Traceback (most recent call last):
File s4.py, line 88, in ?
test_to_date()
File s4.py, line 59, in test_to_date
result = query.execute()
File /home/matt/work/vpython/lib/python2.4/site-packages/
SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/sql/expression.py,
line 973, in execute
return compiled.execute(*multiparams, **params)
File /home/matt/work/vpython/lib/python2.4/site-packages/
SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 488, in execute
return e._execute_compiled(self, multiparams, params)
File /home/matt/work/vpython/lib/python2.4/site-packages/
SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 1121, in _execute_compiled
return connection._execute_compiled(compiled, multiparams, params)
File /home/matt/work/vpython/lib/python2.4/site-packages/
SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 832, in _execute_compiled
self.__execute_raw(context)
File /home/matt/work/vpython/lib/python2.4/site-packages/
SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 850, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
parameters, context=context)
File /home/matt/work/vpython/lib/python2.4/site-packages/
SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 867, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.DatabaseError: (DatabaseError) ORA-00932:
inconsistent datatypes: expected NUMBER got CLOB
'SELECT AD_WEEK_CALENDAR_DIM.adwkenddt \nFROM
AD_WEEK_CALENDAR_DIM \nWHERE AD_WEEK_CALENDAR_DIM.adwkenddt =
to_date(:to_date, :to_date_1)' {'to_date_1': 'MM/DD/RR', 'to_date':
'10/05/04'}
Am still confused as to the problem here I run the same query
through text and it works::
def test_to_date_text():
s = text(SELECT AD_WEEK_CALENDAR_DIM.adwkenddt
FROM AD_WEEK_CALENDAR_DIM
WHERE AD_WEEK_CALENDAR_DIM.adwkenddt = to_date
(:to_date, :to_date_1))
result = connection.execute(s,
to_date_1= 'MM/DD/RR',
to_date= '10/05/04')
for r in result:
print r
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
On Sep 21, 2007, at 1:43 PM, m h wrote: Just for kicks I tried using .3.10 and it failed giving the same error that was reported in the Aug 18 thread autoloading Oracle tables with column defaults:: File /home/matt/work/vpython/lib/python2.4/site-packages/ SQLAlchemy-0.3.10-py2.4.egg/sqlalchemy/databases/oracle.py, line 117, in convert_result_value return value.read() AttributeError: 'str' object has no attribute 'read' I can stay with .3.7, but I'm just wondering what the deal is with beta5. lets see that query youre running (and the bind params youre sending...oh and the table schema too). its guaranteed something small on our end or maybe on yours. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
well the issue is not even the bind params its the result coming back
from cx_oracle. its not well documented/consistent which oracle
types come back as a LOB and also come back with a cx_oracle LOB
object, and which ones do not. its possible that just upgrading
your cx_oracle will fix the issue here...see if you can try that
first (and possibly identify which column in that SQL statement might
be LOB-like). Also keep in mind if you are using any String column
types without a length, they come back as LOBs as well.
the last resort will be that we just need to add an isinstance()
check into convert_result_value to ensure that all LOBs get the read
() call and all strings do not.
On Sep 22, 2007, at 11:50 AM, m h wrote:
On 9/22/07, Michael Bayer [EMAIL PROTECTED] wrote:
On Sep 21, 2007, at 1:43 PM, m h wrote:
Just for kicks I tried using .3.10 and it failed giving the same
error
that was reported in the Aug 18 thread autoloading Oracle tables
with
column defaults::
File /home/matt/work/vpython/lib/python2.4/site-packages/
SQLAlchemy-0.3.10-py2.4.egg/sqlalchemy/databases/oracle.py,
line 117, in convert_result_value
return value.read()
AttributeError: 'str' object has no attribute 'read'
I can stay with .3.7, but I'm just wondering what the deal is with
beta5.
lets see that query youre running (and the bind params youre
sending...oh and the table schema too). its guaranteed something
small on our end or maybe on yours.
Here's the query that's failing::
SELECT PRODUCT_DIM.deptnm AS DEPARTMENT, PRODUCT_DIM.groupnumnm
AS CATEGORY, PRODUCT_DIM.categorynumnm AS SUBCATEGORY,
PRODUCT_DIM.upcdesc AS DESCRIPTION, PRODUCT_DIM.freeformsz AS
SIZE, STORE_DIM.orglevel1 AS STORE CHAIN, STORE_DIM.store AS
STORE NAME, STORE_DIM.state AS STATE, STORE_DIM.county AS
COUNTY, PRODUCT_DIM.upc AS UPC, PRODUCT_DIM.groupnumnm AS
group num, sum(WEEK_SALES_FACT.units *
(coalesce(WEEK_SALES_FACT.dealamt, :coalesce) +
coalesce(WEEK_SALES_FACT.purbillback, :coalesce_1) +
coalesce(WEEK_SALES_FACT.scanbillback, :coalesce_2))) AS current
allowances, sum(WEEK_SALES_FACT.sales -
coalesce(WEEK_SALES_FACT.cost, :coalesce_3)) AS margin,
sum(WEEK_SALES_FACT.sales) AS sales, sum(WEEK_SALES_FACT.units) AS
units
FROM PRODUCT_DIM, STORE_DIM, WEEK_SALES_FACT,
AD_WEEK_CALENDAR_DIM
WHERE AD_WEEK_CALENDAR_DIM.adweekid = WEEK_SALES_FACT.adweekid AND
PRODUCT_DIM.upc = WEEK_SALES_FACT.upc AND STORE_DIM.stornum =
WEEK_SALES_FACT.stornum AND AD_WEEK_CALENDAR_DIM.adwkenddt BETWEEN
to_date(:to_date, :to_date_1) AND to_date(:to_date_2, :to_date_3) AND
PRODUCT_DIM.groupnumnm = :PRODUCT_DIM_groupnumnm AND
PRODUCT_DIM.upc = :PRODUCT_DIM_upc GROUP BY PRODUCT_DIM.deptnm,
PRODUCT_DIM.groupnumnm, PRODUCT_DIM.categorynumnm,
PRODUCT_DIM.upcdesc, PRODUCT_DIM.freeformsz,
STORE_DIM.orglevel1, STORE_DIM.store, STORE_DIM.state,
STORE_DIM.county, PRODUCT_DIM.upc ORDER BY
PRODUCT_DIM.groupnumnm ASC
Here's the parameters::
{'to_date_2': '12/30/03', 'to_date_3': 'MM/DD/RR', 'PRODUCT_DIM_upc':
398483.0, 'to_date_1': 'MM/DD/RR', 'coalesce_2': 0, 'coalesce_3':
0, 'coalesce_1': 0, 'PRODUCT_DIM_groupnumnm': '', 'coalesce': 0,
'to_date': '12/26/03'}
I'm reflecting the tables, so I don't have code for them.
I can't really publish the schema here, but all the columns are either
dates, varchars or numbers. The one value that is getting converted
to a float (in the beta version not .3.7) is PRODUCT_DIM_upc (which
should be a number).
-matt
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
Thanks for the response! On 9/22/07, Michael Bayer [EMAIL PROTECTED] wrote: well the issue is not even the bind params its the result coming back from cx_oracle. Again, I'm confused how this query works from straight cx and .3.7 but fails when .4beta calls it. Could you enlighten me on what I'm missing? its not well documented/consistent which oracle types come back as a LOB and also come back with a cx_oracle LOB object, and which ones do not. its possible that just upgrading your cx_oracle will fix the issue here...see if you can try that first (and possibly identify which column in that SQL statement might be LOB-like). Upgraded to cx_Oracle 4.3.1 from 4.2 and got the same error. All the varchar2 columns have sizes (between 2 and 50) Also keep in mind if you are using any String column types without a length, they come back as LOBs as well. As I said above it appears that all the strings come with lengths. But doesn't the error indicate that a string (CLOB/VARCHAR) appeared where a NUMBER was expected? So to one who doesn't have much experience with the SA code, I would think that somehow one of the numbers or aggregates are getting confused and getting turned into CLOBS somehow... (DatabaseError) ORA-00932: inconsistent datatypes: expected NUMBER got CLOB the last resort will be that we just need to add an isinstance() check into convert_result_value to ensure that all LOBs get the read () call and all strings do not. Hmmm, this appears to be a regression since .3.7 worked I'm assumming new CLOB code somewhere is overeager in converting to CLOBs. You mentioned previously that this is an error on the coming back into SA side. I'm not sure how that is possible if the error is thrown on the cursor.execute. How will convert_result_value do anything if the error is coming from cxOracle before SA even has a result to look at? Thanks again for the response! -matt --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
I guess to help validate my confusion, in my debugging I replaced the line 867 in base.py:: raise exceptions.DBAPIError.instance(statement, parameters, e) with:: raise Then it doesn't give me the useful statement and parameters, but it tells me exactly where it failed:: ... File /home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py, line 850, in __execute_raw self._cursor_execute(context.cursor, context.statement, parameters, context=context) File /home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py, line 858, in _cursor_execute self.dialect.do_execute(cursor, statement, parameters, context=context) File /home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/default.py, line 117, in do_execute cursor.execute(statement, parameters) cx_Oracle.DatabaseError: ORA-00932: inconsistent datatypes: expected NUMBER got CLOB So the failure is when CursorFairy executes the execute method on the cx cursor. Where does the SA converting returned types come into play on this? thanks -matt --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
On Sep 22, 2007, at 3:45 PM, m h wrote: So the failure is when CursorFairy executes the execute method on the cx cursor. Where does the SA converting returned types come into play on this? because you sent me this stack trace: File /home/matt/work/vpython/lib/python2.4/site-packages/ SQLAlchemy-0.3.10-py2.4.egg/sqlalchemy/databases/oracle.py, line 117, in convert_result_value return value.read() AttributeError: 'str' object has no attribute 'read' that line of code is only called within result-set processing, well after any statement execution occurs. now youre talking about getting an exception upon execute, which means the above stack trace should be totally impossible to get. Also, youre saying that removing the DBAPI adaptation somehow made the oracle error visible where it was not earlier, which is also strange; so I'd at least advise using the latest trunk and not beta5 since i think we might have made some corrections to the exception handling. Hmmm, this appears to be a regression since .3.7 worked I'm assumming new CLOB code somewhere is overeager in converting to CLOBs. we arent converting CLOBs or anything like that. the only thing that is different from 0.3.7 to now is that we do a little bit of conversion of Numeric types to Decimal objects. so the new error youre getting now puts it back in the execution camp. you need to narrow down the query to the specific column expression and/or parameter thats making it break. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
Sorry, my mess of emails appears to have confused you (youre looking at the error with .3.10 not .4beta) On 9/22/07, Michael Bayer [EMAIL PROTECTED] wrote: On Sep 22, 2007, at 3:45 PM, m h wrote: So the failure is when CursorFairy executes the execute method on the cx cursor. Where does the SA converting returned types come into play on this? because you sent me this stack trace: File /home/matt/work/vpython/lib/python2.4/site-packages/ SQLAlchemy-0.3.10-py2.4.egg/sqlalchemy/databases/oracle.py, line 117, in convert_result_value return value.read() AttributeError: 'str' object has no attribute 'read' that line of code is only called within result-set processing, well after any statement execution occurs. Sorry, that was the error when I tried (just for fun with .3.10) now youre talking about getting an exception upon execute, which means the above stack trace should be totally impossible to get. Also, youre saying that removing the DBAPI adaptation somehow made the oracle error visible where it was not earlier, which is also strange; so I'd at least advise using the latest trunk and not beta5 since i think we might have made some corrections to the exception handling. Hmmm, this appears to be a regression since .3.7 worked I'm assumming new CLOB code somewhere is overeager in converting to CLOBs. we arent converting CLOBs or anything like that. the only thing that is different from 0.3.7 to now is that we do a little bit of conversion of Numeric types to Decimal objects. so the new error youre getting now puts it back in the execution camp. you need to narrow down the query to the specific column expression and/or parameter thats making it break. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
On 9/22/07, Michael Bayer [EMAIL PROTECTED] wrote:
so the new error youre getting now puts it back in the execution
camp. you need to narrow down the query to the specific column
expression and/or parameter thats making it break.
Here's a simple query that fails. The deal breaker is the between
function for dates. When I add the between the query fails (note that
it's a stupid query but it gives the error)...
AD_WEEK_CALENDAR_DIM table
ADWKENDDT type: DATE
PRODUCT_DIM table
---
DEPTNM type: VARCHAR2(30)
::
#python code
#..set username, password, etc
engine = create_engine(oracle://%s:[EMAIL PROTECTED]:1521/%s%(u, p, host, db))
connection = engine.connect()
meta = MetaData(engine)
cal_dim = Table('AD_WEEK_CALENDAR_DIM', meta, autoload=True)
prod_dim = Table('PRODUCT_DIM', meta, autoload=True)
def fail_query():
start_date = '12/26/03'
end_date = '12/30/03'
where =
and_(cal_dim.c.adwkenddt.between(func.to_date(start_date,'MM/DD/RR'),
func.to_date(end_date, 'MM/DD/RR'))
)
query = select([prod_dim.c.deptnm.label(department)],
whereclause=where
)
compiled = query.compile()
result = query.execute()
for r in result:
print r
fail_query()
Here's the error:
Traceback (most recent call last):
Traceback (most recent call last):
File s4.py, line 43, in ?
fail_query()
File s4.py, line 39, in fail_query
result = query.execute()
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/sql/expression.py,
line 971, in execute
return self.compile(bind=self.bind, parameters=compile_params,
inline=(len(multiparams) 1)).execute(*multiparams, **params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 488, in execute
return e._execute_compiled(self, multiparams, params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 1121, in _execute_compiled
return connection._execute_compiled(compiled, multiparams, params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 832, in _execute_compiled
self.__execute_raw(context)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 850, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
parameters, context=context)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 867, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.DatabaseError: (DatabaseError) ORA-00932:
inconsistent datatypes: expected NUMBER got CLOB
'SELECT PRODUCT_DIM.deptnm AS department \nFROM PRODUCT_DIM,
AD_WEEK_CALENDAR_DIM \nWHERE AD_WEEK_CALENDAR_DIM.adwkenddt
BETWEEN to_date(:to_date, :to_date_1) AND to_date(:to_date_2,
:to_date_3)' {'to_date_2': '12/30/03', 'to_date_3': 'MM/DD/RR',
'to_date_1': 'MM/DD/RR', 'to_date': '12/26/03'}
Here's the log statement/params::
SELECT PRODUCT_DIM.deptnm AS department
FROM PRODUCT_DIM, AD_WEEK_CALENDAR_DIM
WHERE AD_WEEK_CALENDAR_DIM.adwkenddt BETWEEN to_date(:to_date,
:to_date_1) AND to_date(:to_date_2, :to_date_3)
{'to_date_2': '12/30/03', 'to_date_3': 'MM/DD/RR', 'to_date_1':
'MM/DD/RR', 'to_date': '12/26/03'}
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
Ok, it's caused by func.to_date. Here's an even simpler testcase.
Try to convert a date to a string::
def test2():
start_date = '12/26/03'
query = select([func.to_date(cal_dim.c.adwkenddt,'MM/DD/RR')])
compiled = query.compile()
result = query.execute()
for r in result:
print r
On 9/22/07, m h [EMAIL PROTECTED] wrote:
On 9/22/07, Michael Bayer [EMAIL PROTECTED] wrote:
so the new error youre getting now puts it back in the execution
camp. you need to narrow down the query to the specific column
expression and/or parameter thats making it break.
Here's a simple query that fails. The deal breaker is the between
function for dates. When I add the between the query fails (note that
it's a stupid query but it gives the error)...
AD_WEEK_CALENDAR_DIM table
ADWKENDDT type: DATE
PRODUCT_DIM table
---
DEPTNM type: VARCHAR2(30)
::
#python code
#..set username, password, etc
engine = create_engine(oracle://%s:[EMAIL PROTECTED]:1521/%s%(u, p, host,
db))
connection = engine.connect()
meta = MetaData(engine)
cal_dim = Table('AD_WEEK_CALENDAR_DIM', meta, autoload=True)
prod_dim = Table('PRODUCT_DIM', meta, autoload=True)
def fail_query():
start_date = '12/26/03'
end_date = '12/30/03'
where =
and_(cal_dim.c.adwkenddt.between(func.to_date(start_date,'MM/DD/RR'),
func.to_date(end_date,
'MM/DD/RR'))
)
query = select([prod_dim.c.deptnm.label(department)],
whereclause=where
)
compiled = query.compile()
result = query.execute()
for r in result:
print r
fail_query()
Here's the error:
Traceback (most recent call last):
Traceback (most recent call last):
File s4.py, line 43, in ?
fail_query()
File s4.py, line 39, in fail_query
result = query.execute()
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/sql/expression.py,
line 971, in execute
return self.compile(bind=self.bind, parameters=compile_params,
inline=(len(multiparams) 1)).execute(*multiparams, **params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 488, in execute
return e._execute_compiled(self, multiparams, params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 1121, in _execute_compiled
return connection._execute_compiled(compiled, multiparams, params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 832, in _execute_compiled
self.__execute_raw(context)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 850, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
parameters, context=context)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 867, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.DatabaseError: (DatabaseError) ORA-00932:
inconsistent datatypes: expected NUMBER got CLOB
'SELECT PRODUCT_DIM.deptnm AS department \nFROM PRODUCT_DIM,
AD_WEEK_CALENDAR_DIM \nWHERE AD_WEEK_CALENDAR_DIM.adwkenddt
BETWEEN to_date(:to_date, :to_date_1) AND to_date(:to_date_2,
:to_date_3)' {'to_date_2': '12/30/03', 'to_date_3': 'MM/DD/RR',
'to_date_1': 'MM/DD/RR', 'to_date': '12/26/03'}
Here's the log statement/params::
SELECT PRODUCT_DIM.deptnm AS department
FROM PRODUCT_DIM, AD_WEEK_CALENDAR_DIM
WHERE AD_WEEK_CALENDAR_DIM.adwkenddt BETWEEN to_date(:to_date,
:to_date_1) AND to_date(:to_date_2, :to_date_3)
{'to_date_2': '12/30/03', 'to_date_3': 'MM/DD/RR', 'to_date_1':
'MM/DD/RR', 'to_date': '12/26/03'}
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
Disregard this testcase it's late at night no need to convert
a date to a date but the previous testcase fails for .4beta and
works for .3.7!!!
On 9/22/07, m h [EMAIL PROTECTED] wrote:
Ok, it's caused by func.to_date. Here's an even simpler testcase.
Try to convert a date to a string::
def test2():
start_date = '12/26/03'
query = select([func.to_date(cal_dim.c.adwkenddt,'MM/DD/RR')])
compiled = query.compile()
result = query.execute()
for r in result:
print r
On 9/22/07, m h [EMAIL PROTECTED] wrote:
On 9/22/07, Michael Bayer [EMAIL PROTECTED] wrote:
so the new error youre getting now puts it back in the execution
camp. you need to narrow down the query to the specific column
expression and/or parameter thats making it break.
Here's a simple query that fails. The deal breaker is the between
function for dates. When I add the between the query fails (note that
it's a stupid query but it gives the error)...
AD_WEEK_CALENDAR_DIM table
ADWKENDDT type: DATE
PRODUCT_DIM table
---
DEPTNM type: VARCHAR2(30)
::
#python code
#..set username, password, etc
engine = create_engine(oracle://%s:[EMAIL PROTECTED]:1521/%s%(u, p, host,
db))
connection = engine.connect()
meta = MetaData(engine)
cal_dim = Table('AD_WEEK_CALENDAR_DIM', meta, autoload=True)
prod_dim = Table('PRODUCT_DIM', meta, autoload=True)
def fail_query():
start_date = '12/26/03'
end_date = '12/30/03'
where =
and_(cal_dim.c.adwkenddt.between(func.to_date(start_date,'MM/DD/RR'),
func.to_date(end_date,
'MM/DD/RR'))
)
query = select([prod_dim.c.deptnm.label(department)],
whereclause=where
)
compiled = query.compile()
result = query.execute()
for r in result:
print r
fail_query()
Here's the error:
Traceback (most recent call last):
Traceback (most recent call last):
File s4.py, line 43, in ?
fail_query()
File s4.py, line 39, in fail_query
result = query.execute()
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/sql/expression.py,
line 971, in execute
return self.compile(bind=self.bind, parameters=compile_params,
inline=(len(multiparams) 1)).execute(*multiparams, **params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 488, in execute
return e._execute_compiled(self, multiparams, params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 1121, in _execute_compiled
return connection._execute_compiled(compiled, multiparams, params)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 832, in _execute_compiled
self.__execute_raw(context)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 850, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
parameters, context=context)
File
/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py,
line 867, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.DatabaseError: (DatabaseError) ORA-00932:
inconsistent datatypes: expected NUMBER got CLOB
'SELECT PRODUCT_DIM.deptnm AS department \nFROM PRODUCT_DIM,
AD_WEEK_CALENDAR_DIM \nWHERE AD_WEEK_CALENDAR_DIM.adwkenddt
BETWEEN to_date(:to_date, :to_date_1) AND to_date(:to_date_2,
:to_date_3)' {'to_date_2': '12/30/03', 'to_date_3': 'MM/DD/RR',
'to_date_1': 'MM/DD/RR', 'to_date': '12/26/03'}
Here's the log statement/params::
SELECT PRODUCT_DIM.deptnm AS department
FROM PRODUCT_DIM, AD_WEEK_CALENDAR_DIM
WHERE AD_WEEK_CALENDAR_DIM.adwkenddt BETWEEN to_date(:to_date,
:to_date_1) AND to_date(:to_date_2, :to_date_3)
{'to_date_2': '12/30/03', 'to_date_3': 'MM/DD/RR', 'to_date_1':
'MM/DD/RR', 'to_date': '12/26/03'}
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.4.0beta5 breaks [Oracle] query, works on 0.3.7 and with cxOracle
Just for kicks I tried using .3.10 and it failed giving the same error that was reported in the Aug 18 thread autoloading Oracle tables with column defaults:: File /home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.3.10-py2.4.egg/sqlalchemy/databases/oracle.py, line 117, in convert_result_value return value.read() AttributeError: 'str' object has no attribute 'read' I can stay with .3.7, but I'm just wondering what the deal is with beta5. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
