cx-oracle-users

[cx-oracle-users] Nulls returned as None and not as as "empty or zero lenght string"

[Chris D. <cdu...@ya...>]

All,

Is there anyway that Null values returned from a query can be an "empty or zero length string"
instead of None?

This may seem a strange request as it's easy to use type to test if a null has been returned as
None. However I've a performance critical app where I need to output "nothing" (zero length /
empty string) if the value is null. Hence at present I have to perform this "type test" on ALL
columns that don't have a "Not Null" constraint in the database which is quite an overhead when in
some cases I need to do it millions and sometimes billions of times.

Thanks,

Chris Dunscombe


		
__________________________________ 
Do you Yahoo!? 
Read only the mail you want - Yahoo! Mail SpamGuard. 
http://promotions.yahoo.com/new_mail 

Re: [cx-oracle-users] Nulls returned as None and not as as "empty or zero lenght string"

[Mihai I. <mi...@re...>]

On Wed, May 11, 2005 at 07:15:23AM -0700, Chris Dunscombe wrote:
> All,
> 
> Is there anyway that Null values returned from a query can be an "empty or zero length string"
> instead of None?
> 
> This may seem a strange request as it's easy to use type to test if a null has been returned as
> None. However I've a performance critical app where I need to output "nothing" (zero length /
> empty string) if the value is null. Hence at present I have to perform this "type test" on ALL
> columns that don't have a "Not Null" constraint in the database which is quite an overhead when in
> some cases I need to do it millions and sometimes billions of times.

You don't have to test with type.

if val is None:
    val = ''

Or, if val is always a string or None:
val or ''

will always return a string (empty string if object is None).

Null objects are very well represented as None. Empty strings being
represented as Null in Oracle is the weird part here, and I don't think it's
something cx_Oracle (or any database libarries for that matter) should try to
fix.

Hope this helps.
Misa

Re: [cx-oracle-users] Nulls returned as None and not as as "empty or zero lenght string"

[Chris D. <cdu...@ya...>]

Mihai,

Thanks for that, but I still have to do A test, even if it's:

if val is None:
    val = ''

for each col returned, (many million / billion times)

Cheers,

Chris


--- Mihai Ibanescu <mi...@re...> wrote:

> On Wed, May 11, 2005 at 07:15:23AM -0700, Chris Dunscombe wrote:
> > All,
> > 
> > Is there anyway that Null values returned from a query can be an "empty or zero length string"
> > instead of None?
> > 
> > This may seem a strange request as it's easy to use type to test if a null has been returned
> as
> > None. However I've a performance critical app where I need to output "nothing" (zero length /
> > empty string) if the value is null. Hence at present I have to perform this "type test" on ALL
> > columns that don't have a "Not Null" constraint in the database which is quite an overhead
> when in
> > some cases I need to do it millions and sometimes billions of times.
> 
> You don't have to test with type.
> 
> if val is None:
>     val = ''
> 
> Or, if val is always a string or None:
> val or ''
> 
> will always return a string (empty string if object is None).
> 
> Null objects are very well represented as None. Empty strings being
> represented as Null in Oracle is the weird part here, and I don't think it's
> something cx_Oracle (or any database libarries for that matter) should try to
> fix.
> 
> Hope this helps.
> Misa
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [cx-oracle-users] Nulls returned as None and not as as "empty or zero lenght string"

[Marcos P. <ma...@bu...>]

you may try to do it at oracle level

select isnull(col,'')...

it should be faster

El mi=C3=A9, 11-05-2005 a las 07:15 -0700, Chris Dunscombe escribi=C3=B3:
> All,
>=20
> Is there anyway that Null values returned from a query can be an "empty=
 or zero length string"
> instead of None?
>=20
> This may seem a strange request as it's easy to use type to test if a n=
ull has been returned as
> None. However I've a performance critical app where I need to output "n=
othing" (zero length /
> empty string) if the value is null. Hence at present I have to perform =
this "type test" on ALL
> columns that don't have a "Not Null" constraint in the database which i=
s quite an overhead when in
> some cases I need to do it millions and sometimes billions of times.
>=20
> Thanks,
>=20
> Chris Dunscombe
>=20
>=20
> 	=09
> __________________________________=20
> Do you Yahoo!?=20
> Read only the mail you want - Yahoo! Mail SpamGuard.=20
> http://promotions.yahoo.com/new_mail=20
>=20
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] Nulls returned as None and not as as "empty or zero lenght string"

[Chris D. <cdu...@ya...>]

Marcos,

Great idea (nvl as opposed to isnull). However cx_Oracle still returns None e.g.

cur1.execute("select nvl(subobject_name,'') from objects_big where rownum < 5")
data = cur1.fetchall()
print data
[(None,), (None,), (None,), (None,), (None,)]

I thought you'd "fixed it". Thanks for the idea anyway.

Chris

--- Marcos Sánchez Provencio <ma...@bu...> wrote:

> you may try to do it at oracle level
> 
> select isnull(col,'')...
> 
> it should be faster
> 
> El mié, 11-05-2005 a las 07:15 -0700, Chris Dunscombe escribió:
> > All,
> > 
> > Is there anyway that Null values returned from a query can be an "empty or zero length string"
> > instead of None?
> > 
> > This may seem a strange request as it's easy to use type to test if a null has been returned
> as
> > None. However I've a performance critical app where I need to output "nothing" (zero length /
> > empty string) if the value is null. Hence at present I have to perform this "type test" on ALL
> > columns that don't have a "Not Null" constraint in the database which is quite an overhead
> when in
> > some cases I need to do it millions and sometimes billions of times.
> > 
> > Thanks,
> > 
> > Chris Dunscombe
> > 
> > 
> > 		
> > __________________________________ 
> > Do you Yahoo!? 
> > Read only the mail you want - Yahoo! Mail SpamGuard. 
> > http://promotions.yahoo.com/new_mail 
> > 
> > 
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_ids93&alloc_id281&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 



		
Discover Yahoo! 
Stay in touch with email, IM, photo sharing and more. Check it out! 
http://discover.yahoo.com/stayintouch.html

Re: [cx-oracle-users] Nulls returned as None and not as as "empty or zero lenght string"

[Anthony T. <ant...@gm...>]

Oracle is interesting in this respect. An empty string is implicitly
changed to NULL in Oracle -- thus nvl(column, "") is pointless since
you'll get null back anyway. :-)

On 5/11/05, Chris Dunscombe <cdu...@ya...> wrote:
> Marcos,
>=20
> Great idea (nvl as opposed to isnull). However cx_Oracle still returns No=
ne e.g.
>=20
> cur1.execute("select nvl(subobject_name,'') from objects_big where rownum=
 < 5")
> data =3D cur1.fetchall()
> print data
> [(None,), (None,), (None,), (None,), (None,)]
>=20
> I thought you'd "fixed it". Thanks for the idea anyway.
>=20
> Chris
>=20
> --- Marcos S=E1nchez Provencio <ma...@bu...> wrote:
>=20
> > you may try to do it at oracle level
> >
> > select isnull(col,'')...
> >
> > it should be faster
> >
> > El mi=E9, 11-05-2005 a las 07:15 -0700, Chris Dunscombe escribi=F3:
> > > All,
> > >
> > > Is there anyway that Null values returned from a query can be an "emp=
ty or zero length string"
> > > instead of None?
> > >
> > > This may seem a strange request as it's easy to use type to test if a=
 null has been returned
> > as
> > > None. However I've a performance critical app where I need to output =
"nothing" (zero length /
> > > empty string) if the value is null. Hence at present I have to perfor=
m this "type test" on ALL
> > > columns that don't have a "Not Null" constraint in the database which=
 is quite an overhead
> > when in
> > > some cases I need to do it millions and sometimes billions of times.
> > >
> > > Thanks,
> > >
> > > Chris Dunscombe
> > >
> > >
> > >
> > > __________________________________
> > > Do you Yahoo!?
> > > Read only the mail you want - Yahoo! Mail SpamGuard.
> > > http://promotions.yahoo.com/new_mail
> > >
> > >
> > > -------------------------------------------------------
> > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > Want to be the first software developer in space?
> > > Enter now for the Oracle Space Sweepstakes!
> > > http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-...@li...
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_ids93&alloc_id=16281&op=3Dclick
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
>=20
> Discover Yahoo!
> Stay in touch with email, IM, photo sharing and more. Check it out!
> http://discover.yahoo.com/stayintouch.html
>=20
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] Nulls returned as None and not as as "empty or zero lenght string"

[Paul M. <p.f...@gm...>]

On 5/11/05, Marcos S=E1nchez Provencio <ma...@bu...> wrote:
> you may try to do it at oracle level
>=20
> select isnull(col,'')...

That won't work, precisely because Oracle doesn't distinguish between
NULL and ''. This is a problem with Oracle rather than anything else.

You might be able to get away with NVL(col, ' ') and return a single
space, but that is different from an empty string, and so may not
satisfy your application's requirements.

Paul.