[Zope-dev] Re: DCOracle2 and SP problem
Coi Giovanni
coi@prometeo.it
Sat, 14 Jul 2001 11:59:08 +0200
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