cx-oracle-users

[cx-oracle-users] How to transparently re-connect using cx_Oracle?

[Laurelin of M. E. <la...@fn...>]

Hello,

Is it possible for a cx_Oracle.Connection to be told to "reconnect"?

Here is the situation: We have written client code that uses cx_Oracle
to call various pl/sql package methods.  All cx_Oracle calls to the
"real" database (callproc, callfunc, execute, fetchXXX, etc.) are
wrapped in a RetryHandler object.

The RetryHandler can recognize certain Well Known Situations and retry
the call -- example is that somebody has recompiled (or is in the
process of recompiling) the PL/SQL package and its associated
underlying implementations.  In this case, the RetryHandler recognizes
the "existing state of packages has been discarded" exception, waits
a bit, and retries.  This works nicely.

But the situation I'm facing now is: client establishes their
cx_Oracle.Connection instance (and the assocated cx_Oracle.Cursor).
Some time later, the database is "bounced", and that connection is no
longer good.  The RetryHandler recognizes the "not connected to
Oracle" exception, but what to do with it?

The RetryHandler has been given a cursor object (with its connection),
and cannot "replace" that cursor/connection with a different instance.
But it would be nice to tell that cursor/connection to "try to connect
yourself again from scratch, as if you were new, but within the existing
instance at the existing address".

Is this possible?  Or am I reaching for the moon?

Thanks in advance, lauri
-- lauri loebel carpenter
     computing grunt
     fermilab
     la...@fn...

Re: [cx-oracle-users] How to transparently re-connect using cx_Oracle?

[Amaury F. d'A. <ama...@gm...>]

2006/9/28, Laurelin of Middle Earth wrote:
> Hello,
>
> Is it possible for a cx_Oracle.Connection to be told to "reconnect"?
>
....
>
> But the situation I'm facing now is: client establishes their
> cx_Oracle.Connection instance (and the assocated cx_Oracle.Cursor).
> Some time later, the database is "bounced", and that connection is no
> longer good.  The RetryHandler recognizes the "not connected to
> Oracle" exception, but what to do with it?
>
> The RetryHandler has been given a cursor object (with its connection),
> and cannot "replace" that cursor/connection with a different instance.
> But it would be nice to tell that cursor/connection to "try to connect
> yourself again from scratch, as if you were new, but within the existing
> instance at the existing address".
>
> Is this possible?  Or am I reaching for the moon?

A solution could be to wrap both classes (connection and cursor)
inside "RetryingConnection", "RetryingCursor" classes. They delegate
all calls to their base objects, but the RetryingConnection.cursor()
function returns a wrapped RetryingCursor.

RetryingCursor contains a reference to its parent RetryingConnection,
in addition to its base cursor object.

When a method of RetryingCursor detects an error, it asks the
RetryingConnection to connect again, then recreates its cursor member.
Note that some functions may be difficult to restart: if fetchone()
fails in the middle of a long resultset, you have no choice but trying
to fetch again the previous lines.
And it's better if Variables are not involved...

At least, this method should work for the plain cursor.execute() function.

I hope I was clear. I tried to write some sample code, but I could not
figure how your RetryHandler works. It is really an object, or a
simple function?

--
Amaury Forgeot d'Arc

Re: [cx-oracle-users] How to transparently re-connect using cx_Oracle?

[Amaury F. d'A. <ama...@gm...>]

(the mailing list seems to reject all my messages.
That's why I send this directly to you)

2006/9/28, Laurelin of Middle Earth <la...@fn...> wrote:
> Hello,
>
> Is it possible for a cx_Oracle.Connection to be told to "reconnect"?
>
....
>
> But the situation I'm facing now is: client establishes their
> cx_Oracle.Connection instance (and the assocated cx_Oracle.Cursor).
> Some time later, the database is "bounced", and that connection is no
> longer good.  The RetryHandler recognizes the "not connected to
> Oracle" exception, but what to do with it?
>
> The RetryHandler has been given a cursor object (with its connection),
> and cannot "replace" that cursor/connection with a different instance.
> But it would be nice to tell that cursor/connection to "try to connect
> yourself again from scratch, as if you were new, but within the existing
> instance at the existing address".
>
> Is this possible?  Or am I reaching for the moon?

A solution could be to wrap both classes (connection and cursor)
inside "RetryingConnection", "RetryingCursor" classes. They delegate
all calls to their base objects, but the RetryingConnection.cursor()
function returns a wrapped RetryingCursor.

RetryingCursor contains a reference to its parent RetryingConnection,
in addition to its base cursor object.

When a method of RetryingCursor detects an error, it asks the
RetryingConnection to connect again, then recreates its cursor member.
Note that some functions may be difficult to restart: if fetchone()
fails in the middle of a long resultset, you have no choice but trying
to fetch again the previous lines.
And it's better if Variables are not involved...

At least, this method should work for the plain cursor.execute() function.

I hope I was clear. I tried to write some sample code, but I could not
figure how your RetryHandler works. It is really an object, or a
simple function?

-- 
Amaury Forgeot d'Arc

Re: [cx-oracle-users] How to transparently re-connect using cx_Oracle?

[Laurelin of M. E. <la...@fn...>]

Unfortunately, this won't work for the case I'm trying to solve
because the connection that is given to me is (and must remain)
a cx_Oracle.Connection.  No matter how I wrap it and mungle it
at my end, the connection that the caller has passed me *is*
the connection.  If I change it or reconnect, then essentially
I am using a *new*, separate connection, and the caller still
has its dead connection.

Unless the cx_Oracle.Connection instance knows how to reconnect
itself....

Thanks, lauri

Thus spaketh Amaury Forgeot d'Arc on 10/04/2006 03:22 AM:

 > [snippage]
 >
 > A solution could be to wrap both classes (connection and cursor)
 > inside "RetryingConnection", "RetryingCursor" classes. They delegate
 > all calls to their base objects, but the RetryingConnection.cursor()
 > function returns a wrapped RetryingCursor.
...

Re: [cx-oracle-users] How to transparently re-connect using cx_Oracle?

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

On 10/4/06, Laurelin of Middle Earth <la...@fn...> wrote:
> Unfortunately, this won't work for the case I'm trying to solve
> because the connection that is given to me is (and must remain)
> a cx_Oracle.Connection.  No matter how I wrap it and mungle it
> at my end, the connection that the caller has passed me *is*
> the connection.  If I change it or reconnect, then essentially
> I am using a *new*, separate connection, and the caller still
> has its dead connection.
>
> Unless the cx_Oracle.Connection instance knows how to reconnect
> itself....

It doesn't currently -- but its entirely possible that it could. Of
course any current cursors would be invalidated by such a move. That
might end up causing more trouble than its worth.... As has already
been noted attempting to restart something in the middle may not be
very effective as well. Are you certain that this is the solution you
need and would a reconnect() method help you? Would you need this
"right away" as well?

> Thanks, lauri
>
> Thus spaketh Amaury Forgeot d'Arc on 10/04/2006 03:22 AM:
>
>  > [snippage]
>  >
>  > A solution could be to wrap both classes (connection and cursor)
>  > inside "RetryingConnection", "RetryingCursor" classes. They delegate
>  > all calls to their base objects, but the RetryingConnection.cursor()
>  > function returns a wrapped RetryingCursor.
> ...
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys -- and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] How to transparently re-connect using cx_Oracle?

[Laurelin of M. E. <la...@fn...>]

Thus spaketh Anthony Tuininga on 10/05/2006 11:23 AM:

 > On 10/4/06, Laurelin of Middle Earth <la...@fn...> wrote:
 >
 >> [snippage]
 >>Unless the cx_Oracle.Connection instance knows how to reconnect
 >>itself....
 >
 >
 > It doesn't currently -- but its entirely possible that it could. Of
 > course any current cursors would be invalidated by such a move. That
 > might end up causing more trouble than its worth.... As has already
 > been noted attempting to restart something in the middle may not be
 > very effective as well. Are you certain that this is the solution you
 > need and would a reconnect() method help you? Would you need this
 > "right away" as well?
 >

My RetryHandler knows about certain "transactions" -- that is, things
that require both "execute" (or "callfunc") and a series of one or
more "fetchXXX" calls.  If any of the elements fails, it will go back
to the beginning of the transaction.  So I think I've handled the
case of "restarting something in the middle", at least for the
situations of interest to me.

As for invalidating current cursors... hmmm.... I guess I don't
understand (but that's not unusual ;-).  My hope was that the cursor
would become "usable" again by re-connecting its existing connection
rather than creating a new connection.  Of course you cannot continue
with anything that you are currently executing in that cursor -- but
if the connection is dead, then the cursor can't be doing much of
anything anyway, right?

In my case, what I was hoping could happen would be something along
the lines of the pseudo-code:

     done = False
     while not done:
        try:
	  cursor.callfunc(...)
	  cursor.do_whatever_fetch_XXX(...)
	  done = True

        except ConnectionIsDeadException:
           cursor.connection.reconnect()

        except RetriableException:
            pass


"Right away"?  Yes, of course -- yesterday would be optimal.  ;-)  No,
we have a workaround that is getting us by for the time being, and I'm
not presumptuous enough to think that you have free time to spend on
solving my problems.  "At your convenience (but the sooner the better)"
is just fine.

Thanks, lauri