[Zope-dev] Re: DCOracle2 and SP problem

[Coi Giovanni]

In data 25 Jun 2001, alle ore 14:38, Matthew T. Kromer ha scritto:

Date sent:      	Mon, 25 Jun 2001 14:38:21 -0400
Subject:        	Re: DCOracle2 and SP problem
From:           	"Matthew T. Kromer" <matt@digicool.com>
To:             	<coi@prometeo.it>

> on 6/25/01 1:27 PM, Coi Giovanni at coi.giovanni@prometeo.it wrote:
> 
> > The situation:
> > 
> > - Oracle 8.1.7
> > installed on a machine (excalibur) running SuSE Linux 7.0 (i386) - Kernel
> > 2.2.16 (2).
.....
> > 
> > and the test page return this error message:
> > 
> > Error Type: DatabaseError
> > Error Value: (6550, "ORA-06550: line 1, column 28:\012PLS-00225:
> > subprogram or cursor 'GET_ID_UTENTE' reference is out of scope\012ORA-
> > 06550: line 1, column 7:\012PL/SQL: Statement ignored")
> > 
> > Any ideas?
> > 
> 
> I'm  not quite sure what it doesn't like.  I'll have to research it in the
> message reference tomorrow.
> 

FOUND why and other bugs...

I found that this message was because the store procedure was not in a 
package. After I created a package and store the SP in it the error 
disappeared but...

- FIRST PROBLEM (bug)

when I test it from ZMI test pannel I do not get the correct result.

I report the SP body:

FUNCTION        GET_ID_UTENTE (UserAccount in Varchar2) return 
Integer is

  idUtente Integer;
  
BEGIN
  select ID_UTENTE into idUtente from UTENTI 
      where ACCOUNT = UserAccount;
  
 if idUtente is null then
    return (-1);
  else
    return (idUtente);
  end if; 
END;

it query a table with a UserAccount (string) and return an ID_UTENTE 
(Integer)

The test return to me 2.40109947219e-269 (instead of 1003).

I have try it with OTHER SP but always the Interger result was 
2.40109947219e-269 (string result instead was correct).

Some problem with conversion?
Note: ID_UTENTE was defined as NUMBER with size=30, Scale=0.


- SECOND PROBLEM (bug?)
Looking into the code I found that in DCOracle2.py the fetchmany() and 
fetchall() methods have the code

if len(a) == 0: return None
return a

at the end. 
This cause an error when the (for example) SELECT do not return any 
rows. The result was None (instead of []) and the caller must test explicity 
this (see the ZMI test pannel of a database connection object, it return an 
error in this case).
I suggest of remove the line

if len(a) == 0: return None

so them return 'a' that if there are not rows is [].




- THIRD PROBLEM (but may be a <dtml-tree ...> ... </dtml-tree> problem)

I want use a SELECT statement from ORACLE to feed a dtml-tree tag.
I use it many times on old DCOracle and on ODBC, the code was

<dtml-let inode="0"">
   <dtml-tree id=inode branches_expr="TREE_LIST(idParent=inode)"
              assume_children=1
              skip_unauthorized=1>

....... body .....
 
   </dtml-tree>
</dtml-let>

where TREE_LIST return the list of rows (idparent, inode, description) for 
idParent.
And 'inode' was the 'id' that identify rows.

The columns of this table was define as

inode: DataType=NUMBER, Size=38, Scale=0
idParent: DataType=NUMBER, Size=38, Scale=0
description: DataType=VARCHAR2, Size=255

and when the dtml-tree try to use 'inode' as 'id' it return an error as it want 
to use it like a "method" to call to obtain the "real" id.

I look the code and found that in TreeTag.py the tpRender method test if 
the 'id' argument was a 'simple_type' ( string, integer, or float) using:

simple_type={ type(''):0, type(1):0, type(1.0):0}.has_key

but the type of the value return from 'inode' columns whas LONG INTEGER 
(as for example 1003L) and so it try to call it.

What do you think about this? A TreeTag bug?

By.
_________________________________________________________
Prometeo srl - The Software Experience
Coi Giovanni			
Voice : +39 (041)5701366        Via Giudecca 15
Fax   : +39 (041)5701005        30035 MIRANO (VE) - ITALY
e-mail: coi@prometeo.it         http://www.prometeo.it