Re: [cx-oracle-users] How to deal with lost connection to Oracle ?

[Weber, G. <Geo...@PA...>]

Sounds great to me!  I'd be in favor of exposing this as it's caused me headaches in the past, especially in Perl with DBD::Oracle.


>> If you (i) can't use ping (ii) find its internal implementation
>> doesn't catch everything you classify as a connection error (iii) or
>> don't want to use it because it causes a roundtrip to the DB and hence
>> reduces scalability, then you could try explicitly testing error
>> codes.  A fairly good set of codes to identify if an error is a
>> connection error is given in the PHP_OCI_HANDLE_ERROR macro used by
>> PHP error checking.  See
>> http://svn.php.net/viewvc/php/php-src/branches/PHP_5_3/ext/oci8/php_oci8_int.h?view=markup
>> There's an OCI attribute check (also used in PHP_OCI_HANDLE_ERROR)
>> that does this test but cx_Oracle doesn't expose it.

>That can be fixed. :-)

>Just to be clear: this attribute is only set __after__ an attempt is
>made to access the server. So at that point the typical solution would
>be:

>try:
>    cursor.execute(some_sql)
>except cx_Oracle.DatabaseError:
>    if cursor.connection.connected:
>        raise
>    print "*** Server not connected: do something about it!"

>The other option is to check this attribute when the error is being
>created and store it on the error object instead -- but I suspect
>exposing the object on the connection will be more flexible. Comments
>anyone?

I think you're right here, Anthony - having it be part of the connection object would probably make it more flexible, but since it's only set after the attempt to access the server, maybe using the error object still makes more sense?  I guess I can see it set in either location, but it might make more sense to understand the actual functionality better if it was part of the error object.  Or maybe you can expose it in both places?  I guess I'm not helping much, but those were my quick thoughts on the matter.  In the case of using the error object though, then would something like the following work?  Maybe it's too cumbersome...

try:
    cursor.execute(some_sql)
except (cx_Oracle.ConnectionError, cx_Oracle.DatabaseError) as e:
    if ( isinstance(e, cx_Oracle.ConnectionError) ):
        print '*** Server not connected: Do something about it! ***'
    else:
        # other error handling


Thanks!
   Geoff