On Jul 13, 2010, at 5:01 PM, Rodney wrote:
> I just upgraded to 0.6.2 and the new handling of Decimal for Oracle is
> giving me trouble. More specifically, I have a query that's pulling
> data from a pipeline function (e.g., "SELECT * FROM TABLE (func
> (:a,:b,:c))"). In previous SA versions, a couple of the columns were
> "correctly" returned as integers. Now, they're being returned as
> Decimal and that's causing trouble downstream.
The handling was changed in 0.6, but we specifically fixed a bunch of things
related to Decimal in 0.6.2. Oracle doesn't send entirely meaningful
information regarding the specifics of numeric types in a few cases (typically
they involve subqueries, it appears you've found another variant of it), in
those we return Decimal and let SQLA types handle further conversion.
Are you sure you were getting back integers before, and not floats, for this
particular statement ? I've observed that cx_oracle seems to choose float() in
the face of ambiguity.
>
> I understand that the new behavior is probably more accurate
yeah, what we're doing by using a cx_oracle type handler is getting access to
the numeric value before its coerced to a float(). Without doing that, a
float() happens inside cx_oracle and we lose decimal accuracy.
The type handler tries really hard to figure out that the column would be a
decimal and not an integer, though this is before the actual data has been sent
along. The metadata from Oracle is not always meaningful.
> and I
> know that I can convert the data to integers when I reference the
> columns; however, I'd prefer to deal with the conversion closer to the
> source. Unfortunately, I can't figure out how to do that though I
> suspect it's pretty simple.
if you use the Integer type, that will coerce values to integer.
>
> I did find this in the documentation: "Type objects are supplied to
> Table definitions and can be supplied as type hints to functions for
> occasions where the database driver returns an incorrect type." The
> "type hints" sound promising; however, I can't find any other
> reference to that.
>
> When I originally wrote this section of code I was thinking it would
> be nice to use a mapper to make this query look like a table to the
> rest of the application. Perhaps I could define the column types that
> way. However, the bind variables would seem to cause problems with
> that.
>
> So, what's a good way to tell SA about the types of the columns
> returned by the query?
it depends on what kind of query you are using. func() for example can be
made to return Integer by passing in type_, func.foo(type_=Integer). text()
handles it using result_map. Statements derived from Table just do the right
thing automatically. Its only if you use func()/text() with no return type,
or execute("string"), that the typing isn't taking place, so there is always a
way to get Integer in there.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
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.
