[Zope-DB] Problem with DCOracle2 stored procedure calls

[Christian Long]

Hi Matt, list

Thanks for your reply.  I followed your suggestions, unfortunately I'm still
having the same problem.  Looks like the problem only comes for IN OUT
parameters where I'm passing something in.  It also looks like DCOracle2
does correctly identify the parameters as VARCHAR2. Old and new versions of
DCOracle2 produced identical results for
print cursor.procedures.prociapi.adjust_inventory.__doc__
and
print cursor.procedures.prociapi.icbm_move.__doc__
(see results below).

Any other diagnostic info I can get for you?

Any thoughts on a workaround for now?  Can I write the call with a
BindingArray instead of using callproc?  I'm not at all sure how to do that.


Thanks

Christian



---

> I just answered this to the list -- but I just noticed you ARE using an
> old version of DCOracle2 -- I advise using a fresh CVS version as a
> prophylactic measure.

OK:

Newly installed version:
(cvs release) -- DCOracle2 1.101 (dco2:
1.133 -DORACLE8i -DUSEOBJECT -DNATIVENUM
ERIC=1 -D_REENTRANT )

Previous version of DCOracle2 was like this:
DCOracle2                     1.2 -- DCOracle2 1.95 (dco2:
1.120 -DORACLE8i -DUSEOBJECT -D_REENTRANT )

---

> One thing you can do that would help is to get the DCOracle2 procedure
> object and print it's doc string, e.g.
>
> print cursor.procedures.prociapi.adjust_inventory.__doc__
> print cursor.procedures.prociapi.icbm_move.__doc__

>>> print c.procedures.prociapi.adjust_inventory.__doc__
procedure PROCIAPI.ADJUST_INVENTORY has arguments:
        T_EMPNO IN VARCHAR2
        T_ITEM_NO IN VARCHAR2
        T_TRANS_DT IN DATE
        T_TRANS_QTY IN NUMBER
        T_TRANS_UM IN VARCHAR2
        T_STOCK_AREA IN VARCHAR2
        T_BIN_LOC IN VARCHAR2
        T_CONTROL_NO IN VARCHAR2
        T_REASON_CD IN VARCHAR2
        T_REFERENCE IN VARCHAR2
        T_TRANS_TYPE IN VARCHAR2
        T_REF_ORDER IN VARCHAR2
        T_RTV_NO IN NUMBER
        T_INSERT_LOG IN VARCHAR2
        T_ROWID IN OUT VARCHAR2
        T_FAILED IN OUT VARCHAR2
        T_MESSAGE IN OUT VARCHAR2

>>> print c.procedures.prociapi.icbm_move.__doc__
procedure PROCIAPI.ICBM_MOVE has arguments:
        T_EMPNO IN VARCHAR2
        T_ITEM_NO IN VARCHAR2
        T_TRANS_DT IN OUT DATE
        T_TRANS_QTY IN NUMBER
        T_TRANS_UM IN OUT VARCHAR2
        T_FROM_AREA IN OUT VARCHAR2
        T_FROM_BIN IN OUT VARCHAR2
        T_TO_AREA IN OUT VARCHAR2
        T_TO_BIN IN OUT VARCHAR2
        T_CONTROL_NO IN OUT VARCHAR2
        T_REFERENCE IN VARCHAR2
        T_BATCH_NO IN NUMBER
        T_REASON_CODE IN VARCHAR2
        T_ROWID IN OUT VARCHAR2
        T_FAILED IN OUT VARCHAR2
        T_MESSAGE IN OUT VARCHAR2

---





Matthew T. Kromer wrote:

>
> Hi Christian,
>
> I can see from the trace data that the conflict is coming from the fact
> that the actual binding is happening as type SQLT_CHR and it was set up
> as SQLT_STR, essentially CHAR vs VARCHAR2.
>
> The dynamic bind happens because it is an IN/OUT parameter.  The code
> sniffed the procedure definition, saw that it wanted VARCHAR2, and set
> up the binding array as type 5 (SQLT_STR).  This datatype uses a
> terminal NULL to signify end of string.
>
> However, something else has caused the actual BIND to get set up as
> SQLT_CHR.   I dont know that I have enough context to know that your
> trouble spot is really the culprit spot.  It is showing me that in the
> first case, it is bound as SQLT_CHR and in the second case is bound as
> SQLT_STR.
>
> Because you are passing in Python string objects, and your procedure
> definitions all reference VARCHAR2, I am not sure why a binding is EVER
> happening as SQLT_CHR.
>
> One thing you can do that would help is to get the DCOracle2 procedure
> object and print it's doc string, e.g.
>
> print cursor.procedures.prociapi.adjust_inventory.__doc__
> print cursor.procedures.prociapi.icbm_move.__doc__
>
> This will print out what DCOracle2 thinks the procedures are, rather
> than what you declared them to be (in case it is making a mistake).
>
> I'd be willing to bet Oracle is answering the describe call with
> SQLT_CHR which is what is causing the goof.
>
> Having said that, the particular code that notices that the data types
> are in conflict doesnt really know what to do in that case.  It might be
> as simple as adjusting the defined type in the binding array to match
> the result type provided by Oracle, in which case the result objects
> would be converted to python strings properly (without the trailing
> NULL).  I'm not positive that will work though, because I'm not 100%
> clear exactly what code is making the error.
>
>
> --
> Matt Kromer
> Zope Corporation  http://www.zope.com/
>
>
>
>
> Christian Long wrote:
>
> >Hi
> >
> >I'm having trouble with some stored procedure calls on
> DCOracle2.  I think
> >the trouble is with dynamic binding.
> >
> >See version & environment info at end of message
> >
> >
> >I have 2 stored procedures - call them M4 and M5. They have
> similar (but not
> >the same) parameters, and are called from  pytyhon in a similar
> manner.  M4
> >succeds and M5 fails.  M5 fails because a null string
> termination character
> >\x00 has been appended to certain argument, so the argument
> value does not
> >match the value in the database table.
> >
> >Both stored procedures succeed when called from Oracle SQL*Plus
> >
> >My Python code and the stored procedure signatures are all at
> the end of the
> >message
> >
> >