[Zope-DB] ZOracleDA and Stored Procedure -- bug, no such feature or my fault?

[Matthew T. Kromer]

I'm going to prepend my answer instead of appending it ;)

What this looks like is a bug in SP.py of DCOracle2 (gasp!) where some 
other error is causing the transaction to be aborted.  The _abort call is:

   def _abort(self, *ignored):
        self._v_db.db.rollback()

and self._v_db is the database connection object.  Something in this 
case means this object is getting registered with the transaction 
manager (which happens down in the __call__ code) but then blowing up 
later, but in such a way that the object is not connected.

The connection is done in __call__ by checking if self._v_proc exists. 
 If it doesn't, connect gets called which sets up _v_proc and _v_db.

The actual call to the procedure in __call__ is guarded with an exept 
that will clear _v_proc if an error happens (which would cause the 
connection to be re-established the next time the object is called).

I can't see why self._v_db would not be defined and yet the object be 
registered with the transaction manager, but it seems to have happened. 
 You can fix that by changing the _abort method to either test for the 
existance of _v_db before using it, or by catching and ignoring 
AttributeErrors in _abort.

Then, and only then, will whatever the "real" error is show up.


Bo M. Maryniuck wrote:

>Hi.
>
>I have a Stored Procedure and Zope shows me it like this:
>
>function PM.PM_02.CLOB_PRINT returns OUT BOOLEAN, has arguments:
>         P_TABLE IN VARCHAR2
>         P_ID IN NUMBER
>         P_BUFFER IN OUT VARCHAR2
>         P_AMOUNT IN OUT NUMBER
>         P_POS IN NUMBER
>
>According to the PL/SQL it should work in this way: return 
>EOF into LL_NOTENT and a body of the CLOB into the buffer:
>
> ln_amount:=4000;
> ln_pos:=1;
> result:='';
> LOOP
>   ll_notend:=PM.PM_02.CLOB_PRINT('PM_REQUEST_ATTACHEMENTS',p_att_id,buffer,
>              ln_amount,
>              ln_pos);
>   result=result||buffer
>   ln_pos:=ln_pos+ln_amount;             
>  IF NOT ll_notend THEN EXIT; END IF;
> END LOOP;
>
>Well. Now I try to store the result of _procedure_ into some buffer
>with the Zope/PythonScript, like:
>
>buffer = ''
># clobPrint -- is ID of StoredProcedure in ZODB
>(answer,buffer,amount) = container.clobPrint(P_TABLE ='PM_REQUEST_ATTACHEMENTS',
>                                             P_ID = 294,
>                                             P_AMOUNT = 200,
>                                             P_POS = 1,
>                                             P_BUFFER = buffer)
>return buffer
>
>This causes:
>File /opt/zope/lib/python/ZODB/Transaction.py, line 107, in abort
>  File /opt/zope/lib/python/Shared/DC/ZRDB/TM.py, line 60, in abort
>    (Object: clobPrint)
>  File /opt/zope/lib/python/Products/DCOracle2/SP.py, line 154, in _abort
>    (Object: clobPrint)
>AttributeError: _v_db
>
>O.K., then DCOracle2 documentation sais, that I should not define OUT parameter.
>Then Oracle causes "Not enough arguments". Now what shell I do? 
>Is DCOracle2 Stored Procedure can not return more than one result? Or I do something
>wrong?
>
>  
>



-- 
Matt Kromer
Zope Corporation  http://www.zope.com/