Problem with dbi,odbc module and Oracle 9.2 - suffixed "L" with number data type

[dananrg]

I was messing around with the native ODBC module (I am using Python in
a Win32 environment), e.g:

import dbi, odbc

....and it seems to meet my needs. I'd rather use a module that comes
natively with Python if it works (don't care about performance in this
particular use case; just that it works).

The only issue I've had so far is retrieving data from Oracle when an
integer has been defined like:

number(p) [same thing as number(p,0) evidently]

This is from a database I didn't design and can't change. Evidently
there are new ways to declare integer data types in Oracle.

The problem is that the ODBC module suffixes an "L" to any integer
returned that
was defined as data type number(p). For example, an integer stored as:
56 will be returned as 56L. Actually, it now seems to be doing the
same thing, at least in some cases, for number data types declared as
number(p,s). What gives? Anyone know why this would happen?

Can't use mxODBC because it's a commercial product and can't use
cx_oracle at the moment because I am stuck with Python 2.1 (for ESRI
geoprocessing), and there is no cx_oracle for Python 2.1 (starts with
Python 2.2 and refuses to install for 2.1). I could install a later
version of Python independently, but I need to be able to do the
geoprocessing that 2.1 allows as well as ODBC calls to Oracle all in
the same script. This means dbi,odbc seems to be my only choice.

Thanks for the help so far y'all. As a former Perl devotee, I now
worship at the altar of Python.

 

[Diez B. Roggisch]

I was messing around with the native ODBC module (I am using Python in
a Win32 environment), e.g:

import dbi, odbc

...and it seems to meet my needs. I'd rather use a module that comes
natively with Python if it works (don't care about performance in this
particular use case; just that it works).

The only issue I've had so far is retrieving data from Oracle when an
integer has been defined like:

number(p) [same thing as number(p,0) evidently]

This is from a database I didn't design and can't change. Evidently
there are new ways to declare integer data types in Oracle.

The problem is that the ODBC module suffixes an "L" to any integer
returned that
was defined as data type number(p). For example, an integer stored as:
56 will be returned as 56L. Actually, it now seems to be doing the
same thing, at least in some cases, for number data types declared as
number(p,s). What gives? Anyone know why this would happen?

Well, it is a legal python umber literal. Fire up your python interpreter
and do
1L

It simply says that it is a long, not an int (which means 64 rather than 32
bits of precision I think)

So - no need to worry.

Diez

 

[dananrg]

Great, thanks Diez! Should I just use str(n) to convert it to a format
I can write out to a flat file? I'm also struggling with the strange
date object format that the ODBC module returns when I fetch rows. I
need to convert that to a text string in a format that a mainframe
flatfile database requires. Any advice there?

I was messing around with the native ODBC module (I am using Python in
a Win32 environment), e.g:

import dbi, odbc

...and it seems to meet my needs. I'd rather use a module that comes
natively with Python if it works (don't care about performance in this
particular use case; just that it works).

The only issue I've had so far is retrieving data from Oracle when an
integer has been defined like:

number(p) [same thing as number(p,0) evidently]

This is from a database I didn't design and can't change. Evidently
there are new ways to declare integer data types in Oracle.

The problem is that the ODBC module suffixes an "L" to any integer
returned that
was defined as data type number(p). For example, an integer stored as:
56 will be returned as 56L. Actually, it now seems to be doing the
same thing, at least in some cases, for number data types declared as
number(p,s). What gives? Anyone know why this would happen?

Well, it is a legal python umber literal. Fire up your python interpreter
and do
1L

It simply says that it is a long, not an int (which means 64 rather than 32
bits of precision I think)

So - no need to worry.

Diez

 

[Diez B. Roggisch]

Great, thanks Diez! Should I just use str(n) to convert it to a format
I can write out to a flat file? I'm also struggling with the strange

I guess so, yes.

date object format that the ODBC module returns when I fetch rows. I
need to convert that to a text string in a format that a mainframe
flatfile database requires. Any advice there?

No, I do use cx_Oracle - no strange dateformats there.

Diez

 

[Andrew MacIntyre]

[posted & mailed]

The only issue I've had so far is retrieving data from Oracle when an
integer has been defined like:

number(p) [same thing as number(p,0) evidently]

This is from a database I didn't design and can't change. Evidently
there are new ways to declare integer data types in Oracle.

The problem is that the ODBC module suffixes an "L" to any integer
returned that
was defined as data type number(p). For example, an integer stored as:
56 will be returned as 56L. Actually, it now seems to be doing the
same thing, at least in some cases, for number data types declared as
number(p,s). What gives? Anyone know why this would happen?

The 'L' suffix indicates a Python long, which is an arbitrary precision
integer. If you're confident that the number in question is in the
normal integer range, you can coerce it to a normal int with int(p) so
the formatting on display doesn't include the suffix - if the number
is too large to coerce an OverflowError will be raised (for 2.2 and
earlier at least; 2.4 and later unify ints and longs).

Can't use mxODBC because it's a commercial product and can't use
cx_oracle at the moment because I am stuck with Python 2.1 (for ESRI
geoprocessing), and there is no cx_oracle for Python 2.1 (starts with
Python 2.2 and refuses to install for 2.1). I could install a later
version of Python independently, but I need to be able to do the
geoprocessing that 2.1 allows as well as ODBC calls to Oracle all in
the same script. This means dbi,odbc seems to be my only choice.

If you have access to a compiler, you may be able to build cx_Oracle
for Python 2.1, but you would have to check that the code doesn't
require Python 2.2 or later features. The MingW gcc package should
work with
Python 2.1 (which was built with VC6 as I recall).

I've been bugging ESRI about upgrading, and I'm sure others have too.

 

[dananrg]

Thanks Andrew.

I've been bugging ESRI about upgrading, and I'm sure others have too.

The beta of ArcGIS Desktop 9.2 ships with Python 2.4, so I imagine the
release of ArcGIS Desktop 9.2 this summer will also ship with Python
2.4. I've read threads recently on the geoprocessing / scripting
support.esri.com of people using Python 2.4 successfully for
geoprocessing but I've not tried to install it yet.

Check out:

http://forums.esri.com/Thread.asp?c=93&f=1729&t=157014

And also this support article:

http://support.esri.com/index.cfm?fa=knowledgebase.techarticles.articleShow&d=26872

Let me know if you have any luck with this.

Dana